none
SSRS 2008 concatentate

    Question

  • In an sql server 2008 r2 database, I have one column that I need to separate the values.  I then need to concatenate the values together in one row from t-sql that looks like the following:

    11-12  Midwest Plumbers  099.

    The values in the column look like the following

    099 11-12 Midwest Plumbers.

    I need to split the data to look like

    1. 099 is the Customer Number.

    2. 11-12 is the year the customer data was valid.

    3. Midwest Plumbers is the name of the company.

    Notes:

    1. The delimiter between the 3 fields is " " (one space).

    2. The company name can contain lots of spaces.

    3. There are 3 fields that need to be separated out which are:

    a. Customer Number,

    b. Effective Years,

    c. Customer Name.

    For the one row that will be displayed there needs to be 4 spaces between each value.

    Thus can  can you show me how to split up the in this column and then display the values in the order the customer wants to see?

    Sunday, November 03, 2013 3:39 PM

Answers

  • Hi Wendy,

    We can do this on the query level. I have tested it on my local environment, the code below are for your reference.

    declare @col varchar(100);
    declare @col1 varchar(100);
    declare @col2 varchar(100);
    declare @col3 varchar(100);
    set @col = '11-12 Midwest Plumbers  099';
    select @col1 = SUBSTRING(@col,1,CHARINDEX(' ',@col,1)-1);
    select @col3 = REVERSE(SUBSTRING(REVERSE(@col),1,CHARINDEX(' ',REVERSE(@col),1)-1));
    set @col = LTRIM(RTRIM(SUBSTRING(@col,CHARINDEX(' ',@col,1),len(@col))));
    select @col2 = LTRIM(RTRIM(REVERSE(SUBSTRING(REVERSE(@col),CHARINDEX(' ',REVERSE(@col),1),len(@col)))));
    select @col3 as [Number],@col1 as[Year], @col2 as [Company Name]

    Reference:
    SUBSTRING (Transact-SQL)
    CHARINDEX (Transact-SQL)
    REVERSE (Transact-SQL)

    If you have any questions, please feel free to ask.

    Regards,
    Charlie Liao

    If you have any feedback on our support, please click here.

    Monday, November 04, 2013 4:28 AM