Click here to Skip to main content
11,641,971 members (63,016 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL Server
hi all,

how to split the ',' in stored procedure

my query is like this

Declare @client1 varchar(100);
Declare @client2 varchar(100);
Declare @client3 varchar(100);
Declare @client4 varchar(100);
SET @client1= SUBSTRING(@CLIENTNAME,0,CHARINDEX(',',@CLIENTNAME,0))
 Set @client2= SUBSTRING(@CLIENTNAME,(CHARINDEX(',',@CLIENTNAME,0)+1),(lEN(@CLIENTNAME)-(CHARINDEX(',',@CLIENTNAME,0))))
 Set @client3= SUBSTRING(@CLIENTNAME,(CHARINDEX(',',@CLIENTNAME,0)+2),(lEN(@CLIENTNAME)-(CHARINDEX(',',@CLIENTNAME,1))))
 Set @client4= SUBSTRING(@CLIENTNAME,(CHARINDEX(',',@CLIENTNAME,0)+3),(lEN(@CLIENTNAME)-(CHARINDEX(',',@CLIENTNAME,2))))
--select @sta
--select @city
if(@client1 is null or @client1='')
begin
set @client1=' '
end
 
if(@client2 is null or @client2='')
begin
set @client2=' '
end
 
if(@client3 is null or @client3='')
begin
set @client3=' '
end
 
if(@client4 is null or @client4='')
begin
set @client4=' '
end


its working only first two words but not working 3 or more.
please tel me exactly syntax.

thanks and regards
Posted 29-Jan-13 4:56am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

refer this link
http://stackoverflow.com/questions/2459977/how-to-use-split-in-sql-server-2005
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

use the following function

CREATE FUNCTION [dbo].[SplitString]  
(  
@SplitStr nvarchar(1000),  
@SplitChar nvarchar(5)  
)   
RETURNS @RtnValue table   
(  
Data nvarchar(50)  
)   
AS   
BEGIN   
Declare @Count int  
Set @Count = 1  
  
While (Charindex(@SplitChar,@SplitStr)>0)  
Begin  
Insert Into @RtnValue (Data)  
Select   
Data = ltrim(rtrim(Substring(@SplitStr,1,Charindex(@SplitChar,@SplitStr)-1)))  
  
Set @SplitStr = Substring(@SplitStr,Charindex(@SplitChar,@SplitStr)+1,len(@SplitStr))  
Set @Count = @Count + 1  
End  
  
Insert Into @RtnValue (Data)  
Select Data = ltrim(rtrim(@SplitStr))  
  
Return  
END  

eg
Select Data From SplitString('1,2,3,4,5,6,7,8,9', ',')
  Permalink  
Comments
Marcus Kramer at 30-Jan-13 9:58am
   
My 1. Plagiarism: Source If you are going to copy/paste an answer from elsewhere, you MUST give credit to the original source.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Mika Wendelius 460
1 DamithSL 353
2 OriginalGriff 315
3 Sergey Alexandrovich Kryukov 179
4 Afzaal Ahmad Zeeshan 169


Advertise | Privacy | Mobile
Web01 | 2.8.150731.1 | Last Updated 29 Jan 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100