1 SQL SERVER 实现字符串分割成table的方法
时间：2021-07-01 10:21:17
帮助过：15人阅读
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
RETURN
END
1 SQL SERVER 实现字符串分割成table的方法
标签：substr begin where 实现 position pos create entity ret
