使用 T-SQL，从字符串中返回第 n 个分隔元素 [英] Using T-SQL, return nth delimited element from a string

问题描述

我需要创建一个函数，该函数将返回分隔字符串的第 n 个元素.

I have a need to create a function the will return nth element of a delimited string.

对于数据迁移项目，我使用 SQL 脚本将存储在 SQL Server 数据库中的 JSON 审计记录转换为结构化报告.目标是在没有任何代码的情况下交付一个sql脚本和脚本使用的sql函数.

For a data migration project, I am converting JSON audit records stored in a SQL Server database into a structured report using SQL script. Goal is to deliver a sql script and a sql function used by the script without any code.

(这是一个短期修复，将在 ASP.NET/MVC 应用程序中添加新的审核功能时使用)

(This is a short-term fix will be used while a new auditing feature is added the ASP.NET/MVC application)

不乏可用的分隔字符串到表格示例.我选择了一个通用表表达式示例 http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings

There is no shortage of delimited string to table examples available. I've chosen a Common Table Expression example http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings

示例:我想从 '1,222,2,67,888,1111' 返回 67

Example: I want to return 67 from '1,222,2,67,888,1111'

推荐答案

这是我最初的解决方案...它基于 Aaron Bertrand http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings

Here is my initial solution... It is based on work by Aaron Bertrand http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings

我只是更改了返回类型，使其成为标量函数.

I simply changed the return type to make it a scalar function.

示例:SELECT dbo.GetSplitString_CTE('1,222,2,67,888,1111',',',4)

Example: SELECT dbo.GetSplitString_CTE('1,222,2,67,888,1111',',',4)

CREATE FUNCTION dbo.GetSplitString_CTE
(
   @List       VARCHAR(MAX),
   @Delimiter  VARCHAR(255),
   @ElementNumber int
)
RETURNS VARCHAR(4000)
AS
BEGIN

   DECLARE @result varchar(4000)    
   DECLARE @Items TABLE ( position int IDENTITY PRIMARY KEY,
                          Item VARCHAR(4000)
                         )  

   DECLARE @ll INT = LEN(@List) + 1, @ld INT = LEN(@Delimiter);  

   WITH a AS
   (
       SELECT
           [start] = 1,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, @ld), 0), @ll),
           [value] = SUBSTRING(@List, 1, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, @ld), 0), @ll) - 1)
       UNION ALL
       SELECT
           [start] = CONVERT(INT, [end]) + @ld,
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, [end] + @ld), 0), @ll),
           [value] = SUBSTRING(@List, [end] + @ld, 
                     COALESCE(NULLIF(CHARINDEX(@Delimiter, 
                       @List, [end] + @ld), 0), @ll)-[end]-@ld)
       FROM a
       WHERE [end] < @ll
   )
   INSERT @Items SELECT [value]
   FROM a
   WHERE LEN([value]) > 0
   OPTION (MAXRECURSION 0);

   SELECT @result=Item
   FROM @Items
   WHERE position=@ElementNumber

   RETURN @result;
END
GO

这篇关于使用 T-SQL，从字符串中返回第 n 个分隔元素的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！