如何在MSSQL中启用STRING_SPLIT函数的序数

Question

我正在尝试使用 Microsoft SQL Server 2019 中的 STRING_SPLIT 函数。该函数可以正常工作，如果我只放入两个参数，但由于我想从字符串中提取特定元素，我想启用序数。

当我向 STRING_SPLIT 函数添加第三个参数时，它会返回

Msg 8144, Level 16, State 3, Line 5 Procedure or function STRING_SPLIT has too many arguments specified.

我不明白我做错了什么，因为将光标悬停在 STRING_SPLIT 函数上清楚地说明该函数可以作为 int 接受第三个参数。

我的 SQL 代码如下：

SELECT *
FROM STRING_SPLIT('[Control Structure].Root.NP_02.ABC01_02_03.Applications.Prototype.Control Modules.ABC060V.ABC060VXFR2','.',1)
WHERE ORDINAL = 4

- RasmusM

---

第三个参数目前仅适用于Azure SQL数据库，而非SQL Server 2019。 - Martin Smith

此功能也计划用于 SQL Server 2022。Azure SQL 数据库领先于框架版本。 - Dan Guzman

Answer 1

由于它在SQL Server 2019中不可用（并且几乎肯定不会被后移），因此您无法启用它。

问题在于SSMS的IntelliSense /工具提示编码没有基于版本的条件逻辑，并且代码超前于引擎。目前，该功能仅在Azure SQL数据库，托管实例和Synapse中可用。

从文档：

参数enable_ordinal 和 ordinal 输出列目前仅在Azure SQL数据库，Azure SQL 托管实例和Azure Synapse Analytics（仅限无服务器SQL池）中受支持。

更多背景信息：

• 在Azure SQL数据库中信任STRING_SPLIT()顺序

您可以创建自己的内联表值UDF来提供相同类型的序数输出（并使其返回与 STRING_SPLIT 相同的输出，以便稍后轻松更改）。有许多变体；这是其中之一：

CREATE FUNCTION dbo.SplitStrings_Ordered
(
    @List       nvarchar(max),
    @Delimiter  nvarchar(255)
)
RETURNS TABLE
AS
    RETURN (SELECT value = Item ,
      ordinal = ROW_NUMBER() OVER (ORDER BY Number), 
    FROM (SELECT Number, Item = SUBSTRING(@List, Number, 
      CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)
     FROM (SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id])
      FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2) AS n(Number)
      WHERE Number <= CONVERT(INT, LEN(@List))
      AND SUBSTRING(@Delimiter + @List, Number, LEN(@Delimiter)) = @Delimiter
    ) AS y);
GO

另一种更简单的方法是使用JSON，我最近在这个提示中甚至忘记了自己写过它：

CREATE FUNCTION dbo.SplitStrings_Ordered
(
  @List      nvarchar(max),
  @Delimiter nchar(1)
)
RETURNS table WITH SCHEMABINDING
AS
  RETURN
  (
    SELECT value, ordinal = [key]
    FROM OPENJSON(N'["' + REPLACE(@List, @Delimiter, N'","') + N'"]') AS x
  );
GO

此外，如果您只想获取1到4段名称中的最后一个序数，并且每个部分都小于等于128个字符，则可以使用PARSENAME()函数：

DECLARE @str nvarchar(512) = N'here is one.here is two.and three.and four';

SELECT p1 = PARSENAME(@str, 4),
       p2 = PARSENAME(@str, 3),
       p3 = PARSENAME(@str, 2),
       p4 = PARSENAME(@str, 1);

输出:

p1	p2	p3	p4
这里是一个	这里是两个	还有三个	最后是四个

• 示例 db<>fiddle

- Aaron Bertrand

---

喜欢 OpenJson 解决方案。不知道它的存在。我可以经常使用它。 - Brobic Vripiat

+1 对于 OPENJSON() 解决方案，一如既往地感谢！我注意到一件事，就是它对某些字符，比如双引号 ", 会产生错误。在 OPENJSON() 函数中是否有适当的转义方式，或者我最好的选择是用占位符字符替换它们，然后再替换回来？ - undefined

@J.D. 在许多情况下，STRING_ESCAPE()函数是可行的。https://dbfiddle.uk/YGPEpskI - undefined

@AaronBertrand 啊，今天又学到了一个新的函数。干杯！:) - undefined

Answer 2

我们可以通过使用当前顺序来绕过序数，以达到排序的目的。请记住，STRING_SPLIT 的默认顺序是不确定的： STRING_SPLIT() reference 引用如下：

输出行可能以任何顺序出现。不能保证顺序与输入字符串中子字符串的顺序匹配。您可以在 SELECT 语句上使用 ORDER BY 子句来覆盖最终排序顺序，例如 ORDER BY value 或 ORDER BY ordinal。

DECLARE @object as nvarchar(500) = 'test_string_split_order_string'
select
    value,
    ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowNum
from STRING_SPLIT(@object, '_')

- ferdinand wegerif

---

指出使用 order by (SELECT NULL) 不能保证结果按顺序排序。https://dev59.com/5rjna4cB1Zd3GeqP_oGU#59644259 - George Joseph

Answer 3

另一种方式（使用 ';' 作为分隔符）

create function dbo.split_string_ord
(
    @sentence nvarchar(max)
)
returns table
as
return(
    with first_word(ordinal,word,sentence) as (
    Select
        1 as ordinal,
        substring(@sentence+';',1,charindex(';',@sentence+';',1)-1) as word,
        substring(@sentence+';',charindex(';',@sentence+';',1)+1,LEN(@sentence+';')-charindex(';',@sentence+';',1)+1) as sentence
    union all
    Select
        ordinal + 1 as ordinal,
        substring(sentence,1,charindex(';',sentence,1)-1) as word,
        substring(sentence,charindex(';',sentence,1)+1,LEN(sentence)-charindex(';',sentence,1)+1) as sentence
    from
        first_word
    where
        sentence != ''
    )
    Select
        ordinal,
        word
    from
        first_word
)
;

- JFK

Answer 4

SQL Server的XML/XQuery允许非常容易地对字符串进行标记化。

XML/XQuery数据模型基于“有序序列”。

它允许根据其在一串标记中的位置检索任何标记。

SQL

DECLARE @tokens VARCHAR(256) = '[Control Structure].Root.NP_02.ABC01_02_03.Applications.Prototype.Control Modules.ABC060V.ABC060VXFR2'
    , @separator CHAR(1) = '.'
    , @pos INT = 4;

SELECT c.value('(/root/r[sql:variable("@pos")]/text())[1]', 'VARCHAR(100)') AS token
FROM (SELECT TRY_CAST('<root><r><![CDATA[' + 
        REPLACE(@tokens, @separator, ']]></r><r><![CDATA[') + 
        ']]></r></root>' AS XML)) AS t(c);

输出

+-------------+
|    token    |
+-------------+
| ABC01_02_03 |
+-------------+

- miriamka

Answer 5

创建一个如下所示的表格：

CREATE TABLE tmpTable (Value varchar(max), Ordinal int identity(1,1))

现在只需将这些值插入到该表中即可。

INSERT INTO tmpTable (Value)
SELECT * FROM string_split('this.is.a.test', '.')

你最终会得到一个填充了序数值的表格。

SELECT * FROM tmpTable

- dkolln

Answer 6

我通过返回被拆分的原始值，找到charindex()并应用适当的分区和分组，成功创建了一个序数。

declare @string varchar(100) = 'car,dog,guitar,food'

select *, charindex(value, @string) Orderer
, row_number() over (order by charindex(value, @string)) Ordinal
from (
select * 
from string_split(@string, ',')
) x
order by charindex(value, @string)

- mrkstrgn