November 3, 2013 at 8:54 am
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?
November 3, 2013 at 12:38 pm
See the String Functions documentation here.
For splitting text into columns, you can use the following if the character width of the first two values is always the same:
select left(denormalizedColumn, 3) as denormalizedColumn,
substring(denormalizedColumn, 5, 5) as yearsValid,
...
If the customer number might have varying numbers of characters, you will need to determine the values of the constants used above, for example
select left(denormalizedColumn, patindex('% %', denormalizedColumn)-1) as customerNumber...
For concatenating the values together , simply use the + operator, instead of separating out the values into separate columns, e.g.
select substring(denormalizedColumn, 5, 5) + ' ' + substring(denormalizedColumn, 11, len(denormalizedColumn)) + ' ' + left(denormalizedColumn, 3) as displayValue
from (select '099 11-12 Midwest Plumbers.' as denormalizedColumn) as poorlyDesignedTable
Dan Guzman - Not the MVP (7/22/2010)
All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'
November 3, 2013 at 5:51 pm
Perhaps something like this:
WITH SampleData (cust_string) AS
(
SELECT '11-12 Midwest Plumbers 099'
)
SELECT cust_string, EffectiveYears, CustNo
,CustName=SUBSTRING(
cust_string
,LEN(EffectiveYears)+2
,LEN(cust_string)-(2+LEN(EffectiveYears)+LEN(CustNo)))
FROM SampleData a
CROSS APPLY
(
SELECT EffectiveYears=LEFT(cust_string, CHARINDEX(' ', cust_string)-1)
,CustNo=RIGHT(cust_string, CHARINDEX(' ', REVERSE(cust_string))-1)
) b;
I would have preferred to use PatternSplitCM (see the 4th article in my signature links), however there was some uncertainty in the formatting rules of the 3 parts so I hesitated.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply