Click here to Skip to main content
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 5: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)



Advertise | Privacy | Mobile
Web01 | 2.8.1411022.1 | Last Updated 29 Jan 2013
Copyright © CodeProject, 1999-2014
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