sql server 按指定字符循环截取字符串 

 1 USE [TCX_1909]
 2 GO
 3 
 4 SET ANSI_NULLS ON
 5 GO
 6 SET QUOTED_IDENTIFIER ON
 7 GO
 8 
 9 ALTER PROCEDURE [dbo].[a_test]
10     
11 AS
12 BEGIN
13 
14     SET NOCOUNT ON;
15 
16     
17     declare @barcodeList nvarchar(1000)
18     declare @split nvarchar(10)
19     declare @location int 
20     declare @start int
21     declare @barCodeNo nvarchar(100)
22 
23     set @barcodelist = ltrim(rtrim('000002,000003,000007,444,555,'))
24     set @split = ','
25     set @start=1
26     set @location=charindex(@split,@barcodelist)
27 
28      while @location<>0 
29        begin
30         
31         set @barcodeno = substring(@barcodelist,@start,@location-@start)
32         print @barcodeno
33          
34         set @start=@location+1
35         set @location=charindex(@split,@barcodelist,@start)
36 
37        end
38 
39 END

存储过程执行结果：

 

posted @ 2020-03-30 16:58  单纯的桃子  阅读(1462)  评论(0)  编辑  收藏  举报