sql.Split_String

PHOTO EMBED

Sun Feb 06 2022 14:24:37 GMT+0000 (UTC)

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