how to split a string by space in SQL

Question

I have a string like this: 'My name is Jason Fernandez.'

Strings separated by speace have different lengths. I want to get each of the strings separated by space, which means I want to have 5 different items as a result.

1 my
2 name
3 is
4 Jason
5 Fernandez

I am using SQL server 2008.

Thank you.

Google: "sql server split function" – Gordon Linoff Oct 27 '16 at 01:33 — Gordon Linoff, Oct 27 '16 at 01:33

score 4 · Answer 1 · answered Oct 27 '16 at 01:39

4

DECLARE @siteIds VARCHAR(255)
DECLARE @strngLen int
DECLARE @split TABLE(siteId VARCHAR(100))

SET @siteIds = 'My name is Jason Fernandez'

SET @strngLen = CHARINDEX(' ', @siteIds)

WHILE CHARINDEX(' ', @siteIds) > 0
BEGIN
    SET @strngLen = CHARINDEX(' ', @siteIds);

    INSERT INTO @split
    SELECT SUBSTRING(@siteIds,1,@strngLen - 1);

    SET @siteIds = SUBSTRING(@siteIds, @strngLen + 1, LEN(@siteIds));
END

INSERT INTO @split
SELECT @siteIds

SELECT * FROM @split

answered Oct 27 '16 at 01:39

ace_mccloud

497
1
8
25

2

Probably the worst way to split a string – Pரதீப் Oct 27 '16 at 01:43
6

Do you have a better way to get the results? No wonder you are weak af mate. – ace_mccloud Oct 27 '16 at 01:53
1

Please check the duplicate link.. Its just a suggestion don't take it in bad way.. stay cool – Pரதீப் Oct 27 '16 at 01:55
1

The right and polite way to say the same thing would be "There is a better way to split the string" Basic English. – ace_mccloud Oct 27 '16 at 01:58
1

Ok my bad...... – Pரதீப் Oct 27 '16 at 02:00

how to split a string by space in SQL

1 Answers1

Linked