1 SQL SERVER 实现字符串分割成table的方法-白红宇

1 SQL SERVER 实现字符串分割成table的方法

阅读量：5088 次

发布时间：2019-06-13

本文共 1812 字，大约阅读时间需要 6 分钟。

CREATE FUNCTION [dbo].[fn_SplitStringToTable](    @p_Input     VARCHAR(MAX),    @p_Delimeter CHAR(1) = ',')RETURNS @l_Table TABLE(    Id       INT IDENTITY(1, 1),    Value    VARCHAR(511),    Position INT,    Length   INT)BEGIN   DECLARE @l_Position INT,           @l_Value    VARCHAR(256)      SELECT @l_Position = 1,          @l_Value = ''      IF RIGHT(@p_Input, LEN(@p_Delimeter)) <> @p_Delimeter      SET @p_Input = @p_Input + @p_Delimeter      WHILE SUBSTRING(@p_Input, @l_Position, 1) = CHAR(10) OR        SUBSTRING(@p_Input, @l_Position, 1) = CHAR(13)   BEGIN      SELECT @l_Position = @l_Position + 1   END      WHILE @l_Position <= CHARINDEX(@p_Delimeter, @p_Input, @l_Position)   BEGIN      SET @l_Value = RTRIM ( LTRIM ( SUBSTRING ( @p_Input,                                                 @l_Position,                                                 CHARINDEX ( @p_Delimeter,                                                             @p_Input,                                                             @l_Position) - @l_Position ) ) )            IF NOT EXISTS ( SELECT 1                        FROM @l_Table                       WHERE Value = @l_Value )      BEGIN         INSERT INTO @l_Table ( Value, Position, Length )              VALUES ( @l_Value, @l_Position, CHARINDEX ( @p_Delimeter,                                                          @p_Input,                                                          @l_Position ) - @l_Position )      END            SELECT @l_Position = CHARINDEX(@p_Delimeter, @p_Input, @l_Position) + 1            WHILE SUBSTRING(@p_Input, @l_Position, 1) = CHAR(10) OR         SUBSTRING(@p_Input, @l_Position, 1) = CHAR(13)      BEGIN         SELECT @l_Position = @l_Position + 1      END   END   RETURNEND

转载于:https://www.cnblogs.com/mibing/p/7447234.html

你可能感兴趣的文章