Wednesday, 21 August 2024

DelimitedSplit8K function to split the delimited text

 This function is used to split the text into the rows and columns.

CREATE FUNCTION DelimitedSplit8K

        (@pString VARCHAR(8000), @pDelimiter CHAR(1))

RETURNS TABLE WITH SCHEMABINDING AS

 RETURN

  WITH E1(N) AS (

                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

                ),                        

       E2(N) AS (SELECT 1 FROM E1 a, E1 b),

       E4(N) AS (SELECT 1 FROM E2 a, E2 b),

 cteTally(N) AS (

                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4

                ),

cteStart(N1) AS (

                 SELECT 1 UNION ALL

                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter

                ),

cteLen(N1,L1) AS(

                 SELECT s.N1,

                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)

                   FROM cteStart s

                )

 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),

        Item       = SUBSTRING(@pString, l.N1, l.L1)

   FROM cteLen l;

 

Function is created


Let’s use this function.

Select * from DelimitedSplit8K('Bagesh,kumar,singh',',');

 


Delimiter may cam use any character. See as below.



No comments:

Post a Comment

If you have any doubt, please let me know.

Popular Posts