September 19, 2016 at 4:43 am
Hi, I have a table where the data has been loaded in this format (I know it should have been parsed and normalised at source but its not possible to change now since there are lot of interdependencies)
I want to write a function (or sp) which can parse this. - without looping if possible
DECLARE @T TABLE
(
CONTINENTID INT,
COUNTRIES VARCHAR(255),
CITIES VARCHAR(255)
)
INSERT INTO @T
( CONTINENTID, COUNTRIES, CITIES )
VALUES ( 1, -- ID - int
'INDIA,CHINA,BANGLADESH', -- COUNTRIES - varchar(255)
'MUMBAI,DELHI|BEIJING,SHANGHAI|DHAKA' -- CITIES - varchar(255)
)
INSERT INTO @T
( CONTINENTID, COUNTRIES, CITIES )
VALUES ( 2, -- ID - int
'SPAIN,UK', -- COUNTRIES - varchar(255)
'BARCELONA|LONDON,MANCHESTER' -- CITIES - varchar(255)
)
SELECT * FROM @T
Expecting output like this
CONTINENTIDCOUNTRY CITY
1INDIAMUMBAI
1INDIADELHI
1CHINABEIJING
1CHINASHANGHAI
1BANGLADESHDHAKA
2SPAINBARCELONA
2UKLONDON
2UKMANCHESTER
September 19, 2016 at 5:32 am
Using the splitter here http://www.sqlservercentral.com/articles/Tally+Table/72993/
SELECT t.CONTINENTID,
s1.Item AS COUNTRY,
s3.Item AS CITY
FROM @T t
CROSS APPLY dbo.DelimitedSplit8K(t.COUNTRIES,',') s1
CROSS APPLY dbo.DelimitedSplit8K(t.CITIES,'|') s2
CROSS APPLY dbo.DelimitedSplit8K(s2.Item,',') s3
WHERE s1.ItemNumber = s2.ItemNumber
ORDER BY t.CONTINENTID,s1.ItemNumber,s3.ItemNumber;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537Viewing 2 posts - 1 through 2 (of 2 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