SQl截取含逗号的字符串，取出指定逗号前面的值

[jiajiaren]

SQl截取含逗号的字符串，取出指定逗号前面的值，以下SQl如何实现？

DECLARE @Code NVARCHAR(max)
DECLARE @length INT
SET @Code='1,2,3,4,5,6,7,8,9,10,11,12,13,14,15'


如果@length =5
print @Code现要得到三行：
1,2,3,4,5
6,7,8,9,10
11,12,13,14,15

如果 @length =8
print @Code得到两行：
1,2,3,4,5,6,7,8
9,10,11,12,13,14,15

[华夏小卒]

貌似直接取不好取， 先拆分，拆分的时候，标志每个逗号位置的数字， 然后取数后，再组合

[唐诗三百首]

declare @code nvarchar(max),@length int
select @code='1,2,3,4,5,6,7,8,9,10,11,12,13,14,15'


-- @length=5
select @length=5

;with t as
(select (row_number() over(order by getdate())-1)/@length 'rn',
        substring(a.c,b.number,charindex(',',a.c+',',b.number)-b.number) 'c'
  from (select @code 'c') a
  inner join master..spt_values b
  on b.type='P' and b.number between 1 and len(a.c)
  and substring(','+a.c,b.number,1)=','
)
select stuff((select ','+c from t d 
              where d.rn=c.rn for xml path('')),1,1,'') 'code'
 from t c
 group by c.rn

/*
code
---------------------
1,2,3,4,5
6,7,8,9,10
11,12,13,14,15

(3 row(s) affected)
*/


-- @length=8
select @length=8

;with t as
(select (row_number() over(order by getdate())-1)/@length 'rn',
        substring(a.c,b.number,charindex(',',a.c+',',b.number)-b.number) 'c'
  from (select @code 'c') a
  inner join master..spt_values b
  on b.type='P' and b.number between 1 and len(a.c)
  and substring(','+a.c,b.number,1)=','
)
select stuff((select ','+c from t d 
              where d.rn=c.rn for xml path('')),1,1,'') 'code'
 from t c
 group by c.rn

/*
code
---------------------
1,2,3,4,5,6,7,8
9,10,11,12,13,14,15

(2 row(s) affected)
*/

[jiajiaren]

引用 2 楼 ap0405140 的回复:

declare @code nvarchar(max),@length int
select @code='1,2,3,4,5,6,7,8,9,10,11,12,13,14,15'


-- @length=5
select @length=5

;with t as
(select (row_number() over(order by getdate())-1)/@length 'rn',
        substring(a.c,b.number,charindex(',',a.c+',',b.number)-b.number) 'c'
  from (select @code 'c') a
  inner join master..spt_values b
  on b.type='P' and b.number between 1 and len(a.c)
  and substring(','+a.c,b.number,1)=','
)
select stuff((select ','+c from t d 
              where d.rn=c.rn for xml path('')),1,1,'') 'code'
 from t c
 group by c.rn

/*
code
---------------------
1,2,3,4,5
6,7,8,9,10
11,12,13,14,15

(3 row(s) affected)
*/


-- @length=8
select @length=8

;with t as
(select (row_number() over(order by getdate())-1)/@length 'rn',
        substring(a.c,b.number,charindex(',',a.c+',',b.number)-b.number) 'c'
  from (select @code 'c') a
  inner join master..spt_values b
  on b.type='P' and b.number between 1 and len(a.c)
  and substring(','+a.c,b.number,1)=','
)
select stuff((select ','+c from t d 
              where d.rn=c.rn for xml path('')),1,1,'') 'code'
 from t c
 group by c.rn

/*
code
---------------------
1,2,3,4,5,6,7,8
9,10,11,12,13,14,15

(2 row(s) affected)
*/

请问如果要循环打印怎么写？

DECLARE  @Code NVARCHAR(max) --要分割的字符串
DECLARE @location INT
DECLARE @start INT
DECLARE @length INT--含有逗号的个数
DECLARE @pacelength INT --以多少个逗号长度隔开
DECLARE @Split NVARCHAR(10)  --分隔符号
SET  @pacelength=5

SET @Code='1,2,3,4,5,6,7,8,9,10,11,12,13,14,15'  
SET @Split=','
SET @Code = LTRIM(RTRIM(@Code))
SET @location = CHARINDEX(@split, @Code)
SET @length = 1
	WHILE @location <> 0 
		BEGIN
			SET @start = @location + 1
			SET @location = CHARINDEX(@split, @Code, @start)
			SET @length = @length + 1
		END
 
 PRINT  @length --逗号的个数
 ------------------------------------底下有误
While   @pacelength<@length
    BEGIN
          --------底下循环打印出三行怎么写?-----
        PRINT  @Code 
        
        
        --第一次输出前面5个
        --第二次输出6-10
        --第三次输出11-15
         @pacelength=@pacelength+@pacelength
    END
    
    ---结果应该为
             1,2,3,4,5
             6,7,8,9,10
             11,12,13,14,15

[lzw_0736]

declare @code nvarchar(max),@length int
select @code='1,2,3,4,5,6,7,8,9,10,11,12,13,14,15'
 
set @length=5
 
;with t as
(select (row_number() over(order by getdate())-1)/@length 'rn',
        substring(a.c,b.number,charindex(',',a.c+',',b.number)-b.number) 'c'
  from (select @code 'c') a
  inner join master..spt_values b
  on b.type='P' and b.number between 1 and len(a.c)
  and substring(','+a.c,b.number,1)=','
)
select stuff((select ','+c from t d 
              where d.rn=c.rn for xml path('')),1,1,'') 'code'
,ROW_NUMBER() OVER(ORDER BY GETDATE()) re
INTO #temp
from t c
group by c.rn

DECLARE @i1 INT,@i2 int,@cstr varchar(MAX)
SELECT @i1=1,@i2=MAX(re) FROM #temp
WHILE @i1<=@i2
BEGIN
	SELECT @cstr=code FROM #temp WHERE re=@i1
	PRINT @cstr
	SET @i1=@i1+1
END

[jiajiaren]

请问如果要循环打印怎么写？ DECLARE @Code NVARCHAR(max) DECLARE @length INT SET @Code='1,2,3,4,5,6,7,8,9,10,11,12,13,14,15' -- @Code可能很长 While @length=5 BEGIN 循环打印三行该怎么写？ print @Code END