How to split the String in SQL Server like IndexOf function

Some time you need to split the String in SQL Server. for example If you are sending Concate String to Store Procedure that time you need to split the string inside the procedure use thisfunction to split each string with perticuler dissimulator (like , ! @ # $ % ^ & * ( )).

This function will return you String which you want. and it will return null if dissimulator not present.

 

Flexibility : You can use this function as IndexOf Function as Other Language

 

Execute below Function in your SQL Query Analyser

-------------------------------------------------------------------------------

Create function dbo.Function_split( 
 @p_value  varchar(4000),  --Pass String
 @p_pos  int,   --Position
 @p_deli  varchar(1)) returns varchar(500)  --Delimiter
as 
begin 
 declare @l_value varchar(500),@l_pati int,@l_pos int,@l_avalue varchar(2000) 
 set @l_avalue=@p_value; 
 if @p_pos<=0 
  return null 
 set @l_pos=0 
 while len(@p_value)<>0 
  begin 
   select @l_pati=charindex(@p_deli,@p_value),@l_pos=@l_pos+1 
   if @l_pati<=0 or @l_pos=@p_pos 
    begin 
     if @l_pati<=0 
      set @l_value=@p_value 
     else 
      set @l_value=substring(@p_value,1,@l_pati-1)       
     break 
    end 
   set @p_value=stuff(@p_value,1,@l_pati,'') 
  end 
 if @p_pos>@l_pos 
  return '' 
 else 
  return isnull(@l_value,'') 
 return null 
end 
----------------------------------------------------------------------

Example : How to Use this function.

  

Declare @A varchar(100)
Set @A = dbo.Function_split ('Ritesh,Kumar,Kesharwani', 3 , ',')
print @A

 

Return Value : Kesharwani

 

Declare @A varchar(100)
Set @A = dbo.Function_split ('Ritesh,Kumar,Kesharwani', 1 , ',')
print @A

 

Return Value : Ritesh

 

Declare @A varchar(100)
Set @A = dbo.Function_split ('Ritesh,Kumar,Kesharwani', 1 , '*')
print @A

 

Return Value : Null

 

Declare @A varchar(100)
Set @A = CAST(dbo.Function_split ('100,200,300', 1 , '*') AS Integer)
print @A

 

Return Value : 100

 

 

Have a Nice Day

 

Ritesh Kumar Kesharwani

Software Professional (MCAD)

Cell  :  011-91-9845657636

Page: www.Riteshk.blogspot.com

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com