这里有一个UDF可以做到这一点。它将返回一个带分隔符的值的表，我还没有尝试所有的场景，但您的示例工作良好。

CREATE FUNCTION SplitString 
(
    -- Add the parameters for the function here
    @myString varchar(500),
    @deliminator varchar(10)
)
RETURNS 
@ReturnTable TABLE 
(
    -- Add the column definitions for the TABLE variable here
    [id] [int] IDENTITY(1,1) NOT NULL,
    [part] [varchar](50) NULL
)
AS
BEGIN
        Declare @iSpaces int
        Declare @part varchar(50)

        --initialize spaces
        Select @iSpaces = charindex(@deliminator,@myString,0)
        While @iSpaces > 0

        Begin
            Select @part = substring(@myString,0,charindex(@deliminator,@myString,0))

            Insert Into @ReturnTable(part)
            Select @part

    Select @myString = substring(@mystring,charindex(@deliminator,@myString,0)+ len(@deliminator),len(@myString) - charindex(' ',@myString,0))


            Select @iSpaces = charindex(@deliminator,@myString,0)
        end

        If len(@myString) > 0
            Insert Into @ReturnTable
            Select @myString

    RETURN 
END
GO

你可以这样称呼它:

Select * From SplitString('Hello John Smith',' ')

编辑:使用len>1处理分隔符的更新解决方案如下:

select * From SplitString('Hello**John**Smith','**')

这个问题不是关于字符串分割方法，而是关于如何获取第n个元素。

这里所有的答案都是使用递归，CTEs，多个CHARINDEX, REVERSE和PATINDEX，发明函数，调用CLR方法，数字表，CROSS APPLYs…大多数答案都包含了很多行代码。

但是-如果你真的只想要一个方法来获取第n个元素-这可以作为真正的一行程序来完成，没有UDF，甚至没有子选择…作为一个额外的好处:类型安全

用空格分隔第2部分:

DECLARE @input NVARCHAR(100)=N'part1 part2 part3';
SELECT CAST(N'<x>' + REPLACE(@input,N' ',N'</x><x>') + N'</x>' AS XML).value('/x[2]','nvarchar(max)')

当然，你可以使用变量作为分隔符和位置(使用sql:column直接从查询值中检索位置):

DECLARE @dlmt NVARCHAR(10)=N' ';
DECLARE @pos INT = 2;
SELECT CAST(N'<x>' + REPLACE(@input,@dlmt,N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)')

如果您的字符串可能包含禁止字符(特别是&><中的一个字符)，您仍然可以这样做。只需首先在字符串上使用FOR XML PATH，隐式地用合适的转义序列替换所有禁止的字符。

另外，如果分隔符是分号，这是一种非常特殊的情况。在这种情况下，我首先将分隔符替换为“#DLMT#”，最后将其替换为XML标记:

SET @input=N'Some <, > and &;Other äöü@€;One more';
SET @dlmt=N';';
SELECT CAST(N'<x>' + REPLACE((SELECT REPLACE(@input,@dlmt,'#DLMT#') AS [*] FOR XML PATH('')),N'#DLMT#',N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)');

更新SQL-Server 2016+

遗憾的是，开发人员忘记使用STRING_SPLIT返回部件的索引。但是，使用SQL-Server 2016+，有JSON_VALUE和OPENJSON。

使用JSON_VALUE，我们可以传入位置作为索引数组。

对于OPENJSON，文档清楚地说明:

当OPENJSON解析JSON数组时，该函数将JSON文本中元素的索引作为键返回。

像1,2,3这样的字符串只需要括号:[1,2,3]。 像这样的一串单词需要是["this"，"is"，"an"，"example"]。 这些都是非常简单的字符串操作。试试吧:

DECLARE @str VARCHAR(100)='Hello John Smith';
DECLARE @position INT = 2;

--We can build the json-path '$[1]' using CONCAT
SELECT JSON_VALUE('["' + REPLACE(@str,' ','","') + '"]',CONCAT('$[',@position-1,']'));

-位置安全的字符串分配器(从零开始):

SELECT  JsonArray.[key] AS [Position]
       ,JsonArray.[value] AS [Part]
FROM OPENJSON('["' + REPLACE(@str,' ','","') + '"]') JsonArray

在这篇文章中，我测试了各种方法，发现OPENJSON真的很快。甚至比著名的“delimitedSplit8k()”方法快得多……

更新2 -获取类型安全的值

只需使用double[[]]，就可以在数组中使用数组。这允许使用类型化的with子句:

DECLARE  @SomeDelimitedString VARCHAR(100)='part1|1|20190920';

DECLARE @JsonArray NVARCHAR(MAX)=CONCAT('[["',REPLACE(@SomeDelimitedString,'|','","'),'"]]');

SELECT @SomeDelimitedString          AS TheOriginal
      ,@JsonArray                    AS TransformedToJSON
      ,ValuesFromTheArray.*
FROM OPENJSON(@JsonArray)
WITH(TheFirstFragment  VARCHAR(100) '$[0]'
    ,TheSecondFragment INT          '$[1]'
    ,TheThirdFragment  DATE         '$[2]') ValuesFromTheArray

CREATE TABLE test(
    id int,
    adress varchar(100)
);
INSERT INTO test VALUES(1, 'Ludovic Aubert, 42 rue de la Victoire, 75009, Paris, France'),(2, 'Jose Garcia, 1 Calle de la Victoria, 56500 Barcelona, Espana');

SELECT id, value, COUNT(*) OVER (PARTITION BY id) AS n, ROW_NUMBER() OVER (PARTITION BY id ORDER BY (SELECT NULL)) AS rn, adress
FROM test
CROSS APPLY STRING_SPLIT(adress, ',')

下面是一个函数，它将完成问题的目标，即分割字符串并访问项目X:

CREATE FUNCTION [dbo].[SplitString]
(
   @List       VARCHAR(MAX),
   @Delimiter  VARCHAR(255),
   @ElementNumber INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN

       DECLARE @inp VARCHAR(MAX)
       SET @inp = (SELECT REPLACE(@List,@Delimiter,'_DELMTR_') FOR XML PATH(''))

       DECLARE @xml XML
       SET @xml = '<split><el>' + REPLACE(@inp,'_DELMTR_','</el><el>') + '</el></split>'

       DECLARE @ret VARCHAR(MAX)
       SET @ret = (SELECT
              el = split.el.value('.','varchar(max)')
       FROM  @xml.nodes('/split/el[string-length(.)>0][position() = sql:variable("@elementnumber")]') split(el))

       RETURN @ret

END

用法:

SELECT dbo.SplitString('Hello John Smith', ' ', 2)

结果:

John

递归CTE解决方案与服务器疼痛，测试它

MS SQL Server 2008模式设置:

create table Course( Courses varchar(100) );
insert into Course values ('Hello John Smith');

查询1:

with cte as
   ( select 
        left( Courses, charindex( ' ' , Courses) ) as a_l,
        cast( substring( Courses, 
                         charindex( ' ' , Courses) + 1 , 
                         len(Courses ) ) + ' ' 
              as varchar(100) )  as a_r,
        Courses as a,
        0 as n
     from Course t
    union all
      select 
        left(a_r, charindex( ' ' , a_r) ) as a_l,
        substring( a_r, charindex( ' ' , a_r) + 1 , len(a_R ) ) as a_r,
        cte.a,
        cte.n + 1 as n
    from Course t inner join cte 
         on t.Courses = cte.a and len( a_r ) > 0

   )
select a_l, n from cte
--where N = 1

结果:

|    A_L | N |
|--------|---|
| Hello  | 0 |
|  John  | 1 |
| Smith  | 2 |

几乎所有其他答案都是替换正在分割的字符串，这浪费了CPU周期并执行不必要的内存分配。

我在这里介绍了一种更好的进行字符串拆分的方法:http://www.digitalruby.com/split-string-sql-server/

代码如下:

SET NOCOUNT ON

-- You will want to change nvarchar(MAX) to nvarchar(50), varchar(50) or whatever matches exactly with the string column you will be searching against
DECLARE @SplitStringTable TABLE (Value nvarchar(MAX) NOT NULL)
DECLARE @StringToSplit nvarchar(MAX) = 'your|string|to|split|here'
DECLARE @SplitEndPos int
DECLARE @SplitValue nvarchar(MAX)
DECLARE @SplitDelim nvarchar(1) = '|'
DECLARE @SplitStartPos int = 1

SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos)

WHILE @SplitEndPos > 0
BEGIN
    SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, (@SplitEndPos - @SplitStartPos))
    INSERT @SplitStringTable (Value) VALUES (@SplitValue)
    SET @SplitStartPos = @SplitEndPos + 1
    SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos)
END

SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, 2147483647)
INSERT @SplitStringTable (Value) VALUES(@SplitValue)

SET NOCOUNT OFF

-- You can select or join with the values in @SplitStringTable at this point.