如何在sql server存储过程中溢出'，' [英] how to spilt the ',' in sql server stored procedure

问题描述

大家好，



如何在存储过程中拆分''，''


我的查询是这样的

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

它只工作前两个字但没有工作3个或更多。

请给我电话语法。



感谢和问候

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

thanks and regards

推荐答案

参考此链接

http：/ /stackoverflow.com/questions/2459977/how-to-use-split-in-sql-server-2005

refer this link
http://stackoverflow.com/questions/2459977/how-to-use-split-in-sql-server-2005

使用以下功能

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

eg

Select Data From SplitString('1,2,3,4,5,6,7,8,9', ',')

这篇关于如何在sql server存储过程中溢出'，'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！