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. |
