I realize this is a really old question, but starting with SQL Server 2016 there are functions for parsing JSON data that can be used to specifically address the OP's question--and without splitting strings or resorting to a user-defined function. To access an item at a particular index of a delimited string, use the JSON_VALUE function. Properly formatted JSON data is required, however: strings must be enclosed in double quotes " and the delimiter must be a comma ,, with the entire string enclosed in square brackets [].

DECLARE @SampleString NVARCHAR(MAX) = '"Hello John Smith"';
--Format as JSON data.
SET @SampleString = '[' + REPLACE(@SampleString, ' ', '","') + ']';
SELECT 
    JSON_VALUE(@SampleString, '$[0]') AS Element1Value,
    JSON_VALUE(@SampleString, '$[1]') AS Element2Value,
    JSON_VALUE(@SampleString, '$[2]') AS Element3Value;

输出

Element1Value         Element2Value       Element3Value
--------------------- ------------------- ------------------------------
Hello                 John                Smith

(1 row affected)

使用字符串和values()语句怎么样?

DECLARE @str varchar(max)
SET @str = 'Hello John Smith'

DECLARE @separator varchar(max)
SET @separator = ' '

DECLARE @Splited TABLE(id int IDENTITY(1,1), item varchar(max))

SET @str = REPLACE(@str, @separator, '''),(''')
SET @str = 'SELECT * FROM (VALUES(''' + @str + ''')) AS V(A)' 

INSERT INTO @Splited
EXEC(@str)

SELECT * FROM @Splited

结果集。

id  item
1   Hello
2   John
3   Smith

你可以在SQL用户定义函数解析带分隔符的字符串中找到有用的解决方案(来自代码项目)。

你可以使用这个简单的逻辑:

Declare @products varchar(200) = '1|20|3|343|44|6|8765'
Declare @individual varchar(20) = null

WHILE LEN(@products) > 0
BEGIN
    IF PATINDEX('%|%', @products) > 0
    BEGIN
        SET @individual = SUBSTRING(@products,
                                    0,
                                    PATINDEX('%|%', @products))
        SELECT @individual

        SET @products = SUBSTRING(@products,
                                  LEN(@individual + '|') + 1,
                                  LEN(@products))
    END
    ELSE
    BEGIN
        SET @individual = @products
        SET @products = NULL
        SELECT @individual
    END
END

解析姓和名的简单解决方案

DECLARE @Name varchar(10) = 'John Smith'

-- Get First Name
SELECT SUBSTRING(@Name, 0, (SELECT CHARINDEX(' ', @Name)))

-- Get Last Name
SELECT SUBSTRING(@Name, (SELECT CHARINDEX(' ', @Name)) + 1, LEN(@Name))

在我的例子中(在许多其他人中似乎也是如此……)，我有一个由一个空格隔开的姓和名列表。可以直接在选择语句中使用它来解析姓和名。

-- i.e. Get First and Last Name from a table of Full Names
SELECT SUBSTRING(FullName, 0, (SELECT CHARINDEX(' ', FullName))) as FirstName,
SUBSTRING(FullName, (SELECT CHARINDEX(' ', FullName)) + 1, LEN(FullName)) as LastName,
From FullNameTable

一个简单的优化算法:

ALTER FUNCTION [dbo].[Split]( @Text NVARCHAR(200),@Splitor CHAR(1) )
RETURNS @Result TABLE ( value NVARCHAR(50)) 
AS
BEGIN
    DECLARE @PathInd INT
    Set @Text+=@Splitor
    WHILE LEN(@Text) > 0
    BEGIN
        SET @PathInd=PATINDEX('%'+@Splitor+'%',@Text)
        INSERT INTO  @Result VALUES(SUBSTRING(@Text, 0, @PathInd))
        SET @Text= SUBSTRING(@Text, @PathInd+1, LEN(@Text))
    END
        RETURN 
END

Aaron Bertrand的回答很好，但也有缺陷。它不能准确地将空格作为分隔符处理(就像最初问题中的示例一样)，因为长度函数将空格带在后面。

下面是他的代码，稍微调整了一下，允许使用空格分隔符:

CREATE FUNCTION [dbo].[SplitString]
(
    @List NVARCHAR(MAX),
    @Delim VARCHAR(255)
)
RETURNS TABLE
AS
    RETURN ( SELECT [Value] FROM 
      ( 
        SELECT 
          [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
          CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
        FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
          FROM sys.all_objects) AS x
          WHERE Number <= LEN(@List)
          AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim+'x')-1) = @Delim
      ) AS y
    );