如何从sql2008存储过程中获取特定的字符串

Question

前台是可以多选的下拉框，勾选多个仓库。我的后台检测到传入的仓库ID是多个ID连在一起。如"SCID00000001|SCID00000002|SCID00000003|SCID00000004"，现在我要获取这些ID，不要那个竖线|，我应该在存储过程中怎么修改呢。存储过程如下ALTER procedure [dbo].[SH_CurrentInventory_SEL]--仓库类别@SCID nvarchar(20),asbegindeclare @sqlstr nvarchar(4000)set @scid= set @sqlstr='select SIP.SMCID, SMC.SMCCode , SMC.SMCName , SMC.SMCSpec , SMC.SMCBrand , SMC.SMCUnit , SMC.SMCWeight, SMC.SMCHighQty , SMC.SMCLowQty , SMC.SMCLatelyPrice , SMC.SMCLatelyInDate , SMC.SMCLatelyOutDate , SC.SCName , RT.RTName , SMCI.SMCID,FROM StoreInPriceList AS SIP INNER JOIN StoreMaterialCode AS SMC ON SMCI.SMCID=SMC.SMCID WHERE SMC.RUSID = dbo.fn_RefUseStateGetRUSID() AND SIP.SIPLStockQty>0 GROUP BY SIP.SMCID, SMC.SMCCode, SMC.SMCName, SMC.SMCSpec, SMC.SMCBrand, SMC.SMCUnit, SMC.SMCWeight, SMC.SMCHighQty, SMC.SMCLowQty, SMC.SMCLatelyPrice, SMC.SMCLatelyInDate, SMC.SMCLatelyOutDate, SIP.SCID, SC.SCName, RT.RTName, SMCI.SMCID 'IF(isnull(@SCID,'')!='') SET @sqlstr=@sqlstr+' AND SC.SCID=@SCID'IF(isnull(@SMCName,'')!='') SET @sqlstr=@sqlstr+' AND SMC.SMCName=@SMCName' SET @sqlstr=@sqlstr+' ORDER BY SIP.SCID, SMC.SMCCode 'EXEC sp_executesql @sqlstr, N'@SMCName nvarchar(20),@SCID nvarchar(20)',@SMCName,@SCIDend
也就从从"SCID00000001|SCID00000002|SCID00000003|SCID00000004|SCID00000005|SCID00000006|SCID00000007|SCID00000008|SCID00000009|SCID00000010|SCID00000011|SCID00000017|SCID00000018|SCID00000019|SCID00000020|SCID00000021|SCID00000022|SCID00000023|SCID00000024|SCID00000026|SCID00000027|SCID00000029|SCID00000034|SCID00000035|SCID00000036|SCID00000038|SCID00000039|SCID00000040|SCID00000041|SCID00000042|SCID00000043|SCID00000044|SCID00000045|SCID00000046|SCID00000047"中获取|左边的ID

Answer 1

这就是一个字符串处理。我写个简单的你看看有没有启发。
Declare @StrInput nvarchar(max)
Set @StrInput = 'SCID00000001|SCID00000002|SCID00000003|SCID00000004'
Declare @StrLen int
Set @StrLen = LEN(@StrInput)
Declare @PoStart int
Set @PoStart = 1
Declare @StrCnt int
Set @StrCnt = 12
Declare @StrSon nvarchar(12)

While @PoStart <= @StrLen
Begin
Set @StrSon = SUBSTRING(@StrInput,@PoStart,@strcnt)
Select @strson
Set @PoStart = @PoStart + @StrCnt + 1
End

追问

我新手。可不可以帮我修改一下存储过程啊 ，麻烦你了。感谢万分

追答

Netmeeting呀，还是QQ？

Answer 2

sql也可以拆分的，
http://www.cnblogs.com/aierong/archive/2008/11/19/sqlserver_split.html