Share via

SQL Server split string using delimited

  • Article

Introduction

This article gives you brief about how to split string using delimiter

Function(TBV)

CREATE FUNCTION  [dbo].[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  0 
        UNION ALL
        SELECT TOP  (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY  (SELECT  NULL)) FROM E4
    ),
    cteStart(N1) 
    AS
    (   SELECT  t.N+1
        FROM cteTally t
        WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0) 
    )
    SELECT
        ROW_NUMBER() OVER(ORDER BY  s.N1) ItemNumber
        ,SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)) Item
    FROM cteStart s
 
 
 
--After Creating above function let's test the funciton
 
 Declare @split varchar(200) = 'BU/TRU/G/KW/BREED/Col1/Col2/Col3/col4'
 select * from dbo.DelimitedSplit8K(@split, '/') split