sql.Split_Int

PHOTO EMBED

Sun Feb 06 2022 14:23:49 GMT+0000 (UTC)

Saved by @rick_m #sql

IF EXISTS (
    SELECT * FROM sysobjects WHERE id = object_id(N'Split_Int') 
    AND xtype IN (N'FN', N'IF', N'TF')
)
    DROP FUNCTION [dbo].[Split_Int]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[Split_Int]
       (
         @InputList VARCHAR(8000) = '',
         @Delimiter NVARCHAR(1) = ','
       )
RETURNS @List TABLE
       (
         [Index] INT NOT NULL
                        IDENTITY(0, 1)
                        PRIMARY KEY,
         [Value] INT NULL
       )
AS 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_Int('1,2,3,4',',')


select * from dbo.Split_Int('	','	')
content_copyCOPY