Split a String Using Delimiter

Split a String Using Delimiter

Create Function [dbo].[f_StringSplit]

(  

    @DelimitedList nvarchar(max)

    , @Delimiter nvarchar(2) = ','

)

RETURNS TABLE

AS

RETURN

    (

    With CorrectedList As

        (

        Select Case When Left(@DelimitedList, Len(@Delimiter)) <> @Delimiter Then @Delimiter Else '' End

            + @DelimitedList

            + Case When Right(@DelimitedList, Len(@Delimiter)) <> @Delimiter Then @Delimiter Else '' End

            As List

            , Len(@Delimiter) As DelimiterLen

        )

        , Numbers As

        (

        Select TOP( Coalesce(DataLength(@DelimitedList)/2,0) ) Row_Number() Over ( Order By c1.object_id ) As Value

        From sys.columns As c1

            Cross Join sys.columns As c2

        )

    Select CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen As Strat_Position

        , Substring (

                    CL.List

                    , CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen    

                    , CharIndex(@Delimiter, CL.list, N.Value + 1)                          

                        - ( CharIndex(@Delimiter, CL.list, N.Value) + CL.DelimiterLen )

                    ) As Value

    From CorrectedList As CL

        Cross Join Numbers As N

    Where N.Value <= DataLength(CL.List) / 2

        And Substring(CL.List, N.Value, CL.DelimiterLen) = @Delimiter

    )

OUTPUT

select * from [dbo].[f_StringSplit] ('Career Opportunities,Cross – Functional & Business Exposure,Designation,Infrastructure: Transportation/ Cafeteria etc.,',',')

Strat_Position	Value
2	Career Opportunities
23	Cross – Functional & Business Exposure
62	Designation
74	Infrastructure: Transportation/ Cafeteria etc.