Wednesday, July 13, 2005

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
 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 
   select @l_pati=charindex(@p_deli,@p_value),@l_pos=@l_pos+1 
   if @l_pati<=0 or @l_pos=@p_pos 
     if @l_pati<=0 
      set @l_value=@p_value 
      set @l_value=substring(@p_value,1,@l_pati-1)       
   set @p_value=stuff(@p_value,1,@l_pati,'') 
 if @p_pos>@l_pos 
  return '' 
  return isnull(@l_value,'') 
 return null 
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

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around

No comments: