SQL Server 中的自定义拆分函数

Question

我正在尝试在SQL Server中创建一个"split"函数，该函数拆分一个字符串并给我第n个子字符串，但我没有足够的SQL经验来实现它。

理想情况下，我想写

select splitselect('level_a:level_b:level_c:level_d',':',3)

它会返回"level_c"。

部分答案在这里

https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15

但我无法将其转换为我想要的功能。

Answer 1

使用 StringSplit 函数，但必须是 sql Server 2016 或更高版本 STRING_SPLIT(字符串、分隔符( 从STRING_SPLIT中选择 *("约翰，杰里米，杰克"，"(

Answer 2

我认为这就是你想要的 速度与 SQL 内置的/System 函数STRING_SPLIT()函数相同。

IF OBJECT_ID('[dbo].[fnVRS_STRING_SPLIT]','IF') IS NULL BEGIN
EXEC ('CREATE FUNCTION [dbo].[fnVRS_STRING_SPLIT] () RETURNS TABLE AS RETURN SELECT 1 X') 
END
GO
ALTER FUNCTION [dbo].[fnVRS_STRING_SPLIT]
(
@string    nvarchar(MAX), 
@separator nvarchar(MAX)
)
RETURNS TABLE WITH SCHEMABINDING 
AS RETURN
WITH X(N) AS (SELECT 'Table1' FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) T(C)),
Y(N) AS (SELECT 'Table2' FROM X A1, X A2, X A3, X A4, X A5, X A6, X A7, X A8) , -- Up to 16^8 = 4 billion
T(N) AS (SELECT TOP(ISNULL(LEN(@string),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 N FROM Y),
Delim(Pos) AS (SELECT t.N FROM T WHERE (SUBSTRING(@string, t.N, LEN(@separator+'x')-1) LIKE @separator OR t.N = 0)),
Separated(value) AS (SELECT SUBSTRING(@string, d.Pos + LEN(@separator+'x')-1, LEAD(d.Pos,1,2147483647) OVER (ORDER BY (SELECT NULL)) - d.Pos - LEN(@separator)) 
FROM Delim d
WHERE @string IS NOT NULL)
SELECT s.value
FROM Separated s
WHERE s.value <> @separator
GO

Answer 3

我认为这就是你想要的

create FUNCTION dbo.SplitArrayIndex
(
@InputString NVARCHAR(MAX) ,
@Delimiter VARCHAR(50) ,
@index INT
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @ret NVARCHAR(MAX)
DECLARE @Items TABLE
(
id INT IDENTITY(1, 1) ,
Item NVARCHAR(MAX)
)
IF @Delimiter = ' '
BEGIN
SET @Delimiter = ','
SET @InputString = REPLACE(@InputString, ' ', @Delimiter)
END
IF ( @Delimiter IS NULL
OR @Delimiter = ''
)
SET @Delimiter = ','

DECLARE @Item NVARCHAR(MAX)
DECLARE @ItemList NVARCHAR(MAX)
DECLARE @DelimIndex INT
SET @ItemList = @InputString
SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
WHILE ( @DelimIndex != 0 )
BEGIN
SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
INSERT  INTO @Items
VALUES  ( @Item )
-- Set @ItemList = @ItemList minus one less item
SET @ItemList = SUBSTRING(@ItemList, @DelimIndex + 1,
LEN(@ItemList) - @DelimIndex)
SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
END -- End WHILE
IF @Item IS NOT NULL -- At least one delimiter was encountered in 
@InputString
BEGIN
SET @Item = @ItemList
INSERT  INTO @Items
VALUES  ( @Item )
END
-- No delimiters were encountered in @InputString, so just return 
@InputString
ELSE
INSERT  INTO @Items
VALUES  ( @InputString )

SELECT TOP 1
@ret = Item
FROM    @Items
WHERE   id = @index
RETURN @ret
END -- End Function