SQL Query to split values
-
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 -
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 -
Hi,
What are all the possible delimiters?
Delimiters are dot, coma, underscore and spaceCould 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 CodeThe last before row don't have Cost Code which is 3 digit
The Last row don't have Location Code which is 4 digitCould a row have more than 2 delimiters?
YesI tried using case statement with sub-string but struggling to achieve.
Thanks
- dannyfirst80 - Monday, October 8, 2018 1:55 PMHi,
I have below column with values and I want to split values into multiple column valuesExpected Output:
Can you please tell me how to achieve this?
Regards,
DannyBased on your sample data
CREATE 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 results
WITH 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; -
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 alias2SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
- dannyfirst80 - Monday, October 8, 2018 2:15 PM
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 2814a1Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply