sql.Split_String
Sun Feb 06 2022 14:24:37 GMT+0000 (Coordinated Universal Time)
Saved by
@rick_m
#javascript
IF EXISTS (
SELECT * FROM sysobjects WHERE id = object_id(N'Split_String')
AND xtype IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION [dbo].[Split_String]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[Split_String]
(
@InputList VARCHAR(8000) ='',-- List of delimited items
@Delimiter NVARCHAR(1) = ',' -- delimiter that separates items
)
RETURNS @List TABLE
(
[Index] INT NOT NULL
IDENTITY(0, 1)
PRIMARY KEY,
[Value] Varchar(8000) NULL
)
BEGIN
DECLARE @Item VARCHAR(8000)
WHILE CHARINDEX(@Delimiter, @InputList, 0) <> 0
BEGIN
SELECT @Item = RTRIM(LTRIM(SUBSTRING(@InputList, 1,
CHARINDEX(@Delimiter, @InputList, 0)
- 1))),
@InputList = RTRIM(LTRIM(SUBSTRING(@InputList,
CHARINDEX(@Delimiter, @InputList, 0)
+ LEN(@Delimiter),
LEN(@InputList))))
IF LEN(@Item) > 0
INSERT INTO @List
SELECT @Item
END
IF LEN(@InputList) > 0
INSERT INTO @List
SELECT @InputList -- Put the last item in
RETURN
END
GO
select * from dbo.Split_String('a,b,c,d',',')
select * from dbo.Split_String(' ', ' ')
content_copyCOPY
Comments