November 14, 2014 at 1:57 am
Hello,
I have a scenario wherein one of the column values in a row contains a string value which is non-delimited (as shown below). I need to split them by 2 characters and generate as many rows as count of set of 2 digits in that string.
Current record
RecordID Name CountyList
1 ABC 00
2 CDE 01020304
3 XYZ NULL
Expected output:
RecordID Name CountyList
1 ABC 00
2 CDE 01
2 CDE 02
2 CDE 03
2 CDE 04
3 XYZ NULL
I already have a solution in place to run it thru cursor and then do a while loop on the CountyList column by taking 2 digit value using Substring function (keeping start position dyanamic and jumping 2 positions).
But for this small thing, I feel that this effort is more. Can somehome give me a simpler solution to handle it?
November 14, 2014 at 2:13 am
Using a tally table it becomes simple
DECLARE @String VARCHAR(100) = '010203040506'
;WITH Cte_Tally
AS
(
SELECT ((Row_NUMBER() OVER (ORDER BY (SELECT NULL)) *2)-1) Pos FROM sys.columns
)
SELECT
SubString(@String,Pos,2)
from CTE_Tally
Where Pos<Len(@String)
For more details search this site for articles on Tally Tables, as there are a number of options.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
November 14, 2014 at 3:57 am
I've just done an example with a table as its source rather than the single string.
DECLARE @t TABLE (Id smallint,Name varchar(10),CountryList varchar(100))
INSERT INTO @t
VALUES
(1, 'ABC', '00')
,(2, 'CDE', '01020304')
,(3, 'XYZ', NULL)
;WITH Cte_Tally
AS
(
SELECT ((Row_NUMBER() OVER (ORDER BY (SELECT NULL)) *2)-1) Pos FROM sys.columns
)
SELECT
Id, Name, x.splitData
from
@t
CROSS APPLY
(
SELECT
SubString(CountryList,Pos,2) splitData
from CTE_Tally
Where Pos<LEN(CountryList) or Pos=1
) x
This is a modified version using the raw data, there was an issue with NULL or Empty Strings, which is solved by the "OR Pos=1" clause in the Cross Apply.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply