如何在SQL中按空格分割字符串[复制]

This question already has an answer here:

这个问题在这里已有答案:

• T-SQL split string 24 answers

T-SQL拆分字符串24个答案

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

我有一个这样的字符串:'我叫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.

由speace分隔的字符串具有不同的长度。我希望每个字符串都用空格分隔,这意味着我想要有5个不同的项目。

1 my
2 name
3 is
4 Jason
5 Fernandez

I am using SQL server 2008.

我正在使用SQL Server 2008。

Thank you.

1 个解决方案

#1

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