October 8, 2018 at 1:55 pm
Hi,
I have below column with values and I want to split values into multiple column values
Expected Output:
Can you please tell me how to achieve this?
Regards,
Danny
October 8, 2018 at 1:57 pm
What are all the possible delimiters? Could you explain the logic behind the final 2 rows? Could a row have more than 2 delimiters?
Edit: oh, and what have you tried so far?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 8, 2018 at 2:15 pm
Hi,
What are all the possible delimiters?
Delimiters are dot, coma, underscore and space
Could you explain the logic behind the final 2 rows?
First 4 character represents Location Code
Second 3 character represents Cost Code
Last 6 character represents Account Code
The last before row don't have Cost Code which is 3 digit
The Last row don't have Location Code which is 4 digit
Could a row have more than 2 delimiters?
Yes
I tried using case statement with sub-string but struggling to achieve.
Thanks
October 8, 2018 at 2:36 pm
dannyfirst80 - Monday, October 8, 2018 1:55 PMHi,
I have below column with values and I want to split values into multiple column values
Expected Output:
Can you please tell me how to achieve this?
Regards,
Danny
Based on your sample dataCREATE TABLE #test (
ColumnA varchar(20)
);
INSERT INTO #test (ColumnA)
VALUES ('AA12.F07.123456')
, ('XX34 SA8 676868')
, ('YY13_SS3_798798')
, ('HJ88.657769')
, ('.898798');
The following query will return the expected resultsWITH cteBaseData AS (
SELECT ColumnA = REPLACE(REPLACE(REPLACE(ColumnA, ',', '.'), '_', '.'), ' ', '.')
FROM #test
)
SELECT
ColA = CASE
WHEN LEN(PARSENAME(bd.ColumnA, 1)) = 4 THEN PARSENAME(bd.ColumnA, 1)
WHEN LEN(PARSENAME(bd.ColumnA, 2)) = 4 THEN PARSENAME(bd.ColumnA, 2)
WHEN LEN(PARSENAME(bd.ColumnA, 3)) = 4 THEN PARSENAME(bd.ColumnA, 3)
END
, ColB = CASE
WHEN LEN(PARSENAME(bd.ColumnA, 1)) = 3 THEN PARSENAME(bd.ColumnA, 1)
WHEN LEN(PARSENAME(bd.ColumnA, 2)) = 3 THEN PARSENAME(bd.ColumnA, 2)
WHEN LEN(PARSENAME(bd.ColumnA, 3)) = 3 THEN PARSENAME(bd.ColumnA, 3)
END
, ColC = CASE
WHEN LEN(PARSENAME(bd.ColumnA, 1)) = 6 THEN PARSENAME(bd.ColumnA, 1)
WHEN LEN(PARSENAME(bd.ColumnA, 2)) = 6 THEN PARSENAME(bd.ColumnA, 2)
WHEN LEN(PARSENAME(bd.ColumnA, 3)) = 6 THEN PARSENAME(bd.ColumnA, 3)
END
FROM cteBaseData AS bd;
October 8, 2018 at 2:54 pm
If you want to limit the delimiters to strictly:dot, comma, underscore and space, in the code below, change:
PATINDEX('%[^A-Z0-9]%'
to
PATINDEX('%[.,_ ]%'
SELECT
CASE WHEN delim1 = 0 THEN ColumnA ELSE LEFT(ColumnA, delim1 - 1) END AS ColA,
CASE WHEN delim1 = 0 OR delim2 = 0 THEN ''
ELSE SUBSTRING(ColumnA, delim1 + 1, delim2 - 1) END AS ColB,
CASE WHEN delim1 = 0 THEN ''
ELSE SUBSTRING(ColumnA, delim1 + delim2 + 1, 100) END AS ColC
FROM #test
CROSS APPLY (
SELECT PATINDEX('%[^A-Z0-9]%', ColumnA) AS delim1
) AS alias1
CROSS APPLY (
SELECT PATINDEX('%[^A-Z0-9]%', SUBSTRING(ColumnA, delim1 + 1, 100)) AS delim2
) AS alias2
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 9, 2018 at 2:00 am
dannyfirst80 - Monday, October 8, 2018 2:15 PMCould a row have more than 2 delimiters?
Yes
You haven't given any examples of this. What would, therefore, the result for the values below therefore be?1234.5678.abcde.123asd
367a haua5 haghy 2814a1
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy