SEPARATE WORDS TO ROWS (Convert Comma Separated)

Question

SEPARATE WORDS TO ROWS (Convert Comma Separated)

diogo.venturatomas

SSC Enthusiast

Points: 193
More actions
May 3, 2015 at 4:19 pm

#296552

I have a table Sample with data stored like below
ID|STRING |
------------------------------------------------------------------
1| 'ENGLAN SPAIN' ITALY 'FRANCE GERMANY' BRAZIL
I need the output like..
-----------------
|ENGLAND SPAIN |
|---------------|
|ITALY |
|---------------|
|FRANCE GERMANY |
|---------------|
|BRAZIL|
-----------------
How can I do the same with a select query in SQL Server?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

diogo.venturatomas (5/3/2015)
I have a table Sample with data stored like below
ID|STRING |
------------------------------------------------------------------
1| 'ENGLAN SPAIN' ITALY 'FRANCE GERMANY' BRAZIL
I need the output like..
-----------------
|ENGLAND SPAIN |
|---------------|
|ITALY |
|---------------|
|FRANCE GERMANY |
|---------------|
|BRAZIL|
-----------------
How can I do the same with a select query in SQL Server?

Quick solution using the dbo.DelimitedSplit8K[/url] function

DECLARE @SAMPLE TABLE (ID INT NOT NULL, STRING VARCHAR(200) NOT NULL)

INSERT INTO @SAMPLE (ID,STRING) VALUES (1,'''ENGLAN SPAIN'' ITALY ''FRANCE GERMANY'' BRAZIL');

SELECT

S.ID

,LTRIM(X.Item) AS OUT_STRING

FROM @SAMPLE S

CROSS APPLY dbo.DelimitedSplit8K(S.STRING,CHAR(39)) AS X

WHERE LEN(X.Item) > 0;

Results

ID OUT_STRING

----------- -----------------

1 ENGLAN SPAIN

1 ITALY

1 FRANCE GERMANY

1 BRAZIL

diogo.venturatomas SSC Enthusiast Points: 193 More actions · Answer 2

Hello Eirikur Eiriksson,

Your post is very good. But I have a question : I tried to apply the function to other by adding other words , but it has a problem, the fence does not separate words. I will show an example for you :

DECLARE @SAMPLE TABLE (ID INT NOT NULL, STRING VARCHAR(200) NOT NULL)

INSERT INTO @SAMPLE (ID,STRING)

VALUES (1,'''ENGLAN SPAIN'' ITALY ''FRANCE GERMANY'' BRAZIL ARGENTINA ''ROMENIA HOLLAND''');

--SELECT * FROM @SAMPLE

SELECT

S.ID

,LTRIM(X.Item) AS OUT_STRING

FROM @SAMPLE S

CROSS APPLY dbo.DelimitedSplit8K(S.STRING,CHAR(39)) AS X

WHERE LEN(X.Item) > 0;

RESULT:

IDOUT_STRING

1ENGLAN SPAIN

1ITALY

1FRANCE GERMANY

1BRAZIL ARGENTINA

1ROMENIA HOLLAND

BUT I WANT:

IDOUT_STRING

1ENGLAN SPAIN

1ITALY

1FRANCE GERMANY

1BRAZIL

1 ARGENTINA

1 ROMENIA HOLLAND

Thank You very mucg

Luis Cazares SSC Guru Points: 183698 More actions · Answer 3

Perhaps if you use it twice with a little tweak. 😉

SELECT

S.ID

,LTRIM(ISNULL(Y.Item, X.Item)) AS OUT_STRING

FROM @SAMPLE S

CROSS APPLY dbo.DelimitedSplit8K(S.STRING,CHAR(39)) AS X

OUTER APPLY (SELECT s.Item

FROM (SELECT LTRIM(X.Item) Item) i

CROSS APPLY dbo.DelimitedSplit8K( i.Item, ' ') s

WHERE X.Item LIKE '% '

AND LEN(s.Item) > 0) Y

WHERE LEN(X.Item) > 0;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 4

And a bit more robust version

DECLARE @SAMPLE TABLE (ID INT NOT NULL, STRING VARCHAR(200) NOT NULL)

INSERT INTO @SAMPLE (ID,STRING)

VALUES (1,'CANADA USA''ENGLAN SPAIN'' ITALY '' FRANCE GERMANY ''BRAZIL ARGENTINA''ROMENIA HOLLAND''');

SELECT

S.ID

,RTRIM(LTRIM(ISNULL(Y.Item, X.Item))) AS OUT_STRING

FROM @SAMPLE S

CROSS APPLY dbo.DelimitedSplit8K(S.STRING,CHAR(39)) AS X

OUTER APPLY (SELECT s.Item

FROM (SELECT RTRIM(LTRIM(X.Item)) Item) i

CROSS APPLY dbo.DelimitedSplit8K( i.Item, ' ') s

WHERE X.ItemNumber%2 = 1

AND LEN(s.Item) > 0) Y

WHERE LEN(X.Item) > 0;