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.
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?
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]
If you have any questions, please feel free to ask.
If you have any feedback on our support, please click
Microsoft is conducting an online survey to understand your opinion of the Msdn Web site. If you choose to participate, the online survey will be presented to you when you leave the Msdn Web site.
Would you like to participate?