时间戳

首页 > 解决方案 > 解释这个 SQL 拆分函数的作用

sql - 解释这个 SQL 拆分函数的作用

问题描述

我对 SQL 还很陌生。我在网上找到了这个脚本。它在使用分隔符值拆分字符串值方面做得很好。

我可以看到他们声明了两个变量。之后的一切对我来说都没有多大意义。有人可以告诉我 fn_split_string_to_column 是如何工作的吗？

我怎么强调都不过分。我对此很陌生。

CREATE FUNCTION [dbo].[fn_split_string_to_column] (
    @string NVARCHAR(MAX),
    @delimiter CHAR(1)
    )
RETURNS @out_put TABLE (
    [column_id] INT IDENTITY(1, 1) NOT NULL,
    [value] NVARCHAR(MAX)
    )
AS
BEGIN
    DECLARE @value NVARCHAR(MAX),
        @pos INT = 0,
        @len INT = 0

    SET @string = CASE 
            WHEN RIGHT(@string, 1) != @delimiter
                THEN @string + @delimiter
            ELSE @string
            END
    WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0
    BEGIN
        SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos
        SET @value = SUBSTRING(@string, @pos, @len)

        INSERT INTO @out_put ([value])
        SELECT LTRIM(RTRIM(@value)) AS [column]

        SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1
    END 
    RETURN
END
GO

标签： sql

解决方案

它所做的是获取一个字符串，例如“让，做，这个”，并返回一个表，其结果是字符串中被给定字符分割的术语的索引。

像这样使用它：

select    column_id, value
from      fn_split_string_to_column('lets, do, this', ',');

虽然更多时候它是更大查询的一部分：

select        st.col1, 
              st.col2,   
              termOrder = sp.column_id, 
              term = sp.value
from          someTable st
cross apply   fn_split_string_to_column(st.colToSplit, ',') sp;

请注意，sql server 2016 具有内置的拆分功能，但它不会为您提供术语排序（并且无法保证顺序），并且不会修剪条目：

select * from string_split('lets, do, this', ',');

至于这实际上是如何工作的，这里是该函数的注释版本：

CREATE FUNCTION [dbo].[fn_split_string_to_column] (
    @string NVARCHAR(MAX), -- the string to be splitted
    @delimiter CHAR(1) -- the character that tells you where to make the cuts
)
-- The result set will be a table, not a scalar value
RETURNS @out_put TABLE (
    [column_id] INT IDENTITY(1, 1) NOT NULL, -- where the term occured in the string
    [value] NVARCHAR(MAX) -- the splitted term
)
AS
BEGIN

    DECLARE 
        @value NVARCHAR(MAX), -- holds the value of each segment (split in the string, result of substring) 
        @pos INT = 0, -- determines the starting position of a segment (for use in substring)
        @len INT = 0 -- determines the length of the segment (for use in substring)

    -- Ensure the passed-in string always ends with the delimiter character
    -- This is so that the loop doesn't terminate prematurely
    SET @string = 
        CASE 
        WHEN RIGHT(@string, 1) != @delimiter THEN @string + @delimiter
        ELSE @string
        END


    -- while the length of the next segment would be positive ...
    WHILE CHARINDEX(@delimiter, @string, @pos + 1) > 0 
    BEGIN

        -- ... establish the length of the next segment to analyze
        SET @len = CHARINDEX(@delimiter, @string, @pos + 1) - @pos 

        -- ... get the next segment to analyze
        SET @value = SUBSTRING(@string, @pos, @len) 

        -- ... insert the segment as an entry into the results, ensuring it has no spaces surrounding it
        INSERT INTO @out_put ([value])
        SELECT LTRIM(RTRIM(@value)) AS [column]

        -- ... in advance of the next loop, establish the starting position of the next segment
        SET @pos = CHARINDEX(@delimiter, @string, @pos + @len) + 1

    END 
    RETURN

END

推荐阅读