SQL Function for Spliting a string by Using Comma.



introduction : 

In this tutorial i will explain how to Split the String in SQL by using Comma( , )  . Firstly We should execute the below code and the call function where it is necessary. can Also be used in Store Procedures. Not only by comma's but also you can split by your wish, Review the function and replace Comma( , ) with your Characters. Have Blast.

SQL Function  : 

create Function SplitStringFunction
    (@Value Varchar(8000)
    ,@Part Int
    ,@Sep Char(1)=','
)Returns Varchar(8000)
As Begin
Declare @Start Int
Declare @Finish Int
Set @Start=1
Set @Finish=CharIndex(@Sep,@Value,@Start)
While (@Part>1 And @Finish>0)Begin
    Set @Start=@Finish+1
    Set @Finish=CharIndex(@Sep,@Value,@Start)
    Set @Part=@Part-1
End
If @Part>1 Set @Start=Len(@Value)+1
If @Finish=0 Set @Finish=Len(@Value)+1
Return SubString(@Value,@Start,@Finish-@Start)
End

Call the Function : 


select dbo.[SplitStringFunction]('India,Goa,Chile,Cylon,Paris',3,Default)

(FYI:  In the above Query 3 denotes the 3rd value, so you can replace it with as your requirement.)

Output :  

Chile