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

2013-09-06 05:23:39 2.4k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Oracle字符串截取实战：精准提取逗号分隔字段的SUBSTR与INSTR组合技巧在数据库开发与数据清洗过程中，字符串处理是基础且关键的技术环节。其核心原理在于对字符串的定位、分割与提取，这直接关系到数据解析的准确性和效率。掌握高效的字符串处理技术，对于提升数据质量、实现业务逻辑至关重要，尤其在处理日志分析、配置解析、标签系统等常见应用场景时。本文聚焦Oracle数据库，深入探讨如何利用SUBSTR和INSTR函数组合，解决“截取两个逗号之间”这一典型需求，并对比正则表达式方案，为数据清洗和字段标准化提供实用解决方案。阅读详情

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

...全文

Adams与Matlab联合仿真入门：从零搭建你的第一个动力学模型本文为Adams与Matlab联合仿真新手提供了一份详尽的入门指南。文章从软件环境配置、版本兼容性检查讲起，逐步演示了如何在Adams中构建单摆动力学模型并定义输入输出接口，以及在Matlab/Simulink中搭建控制回路。通过一个完整的受控单摆实例，手把手教你完成从模型导出、模块生成到执行联合仿真的全流程，帮助你快速搭建并运行第一个联合仿真模型，实现动力学与控制系统的协同验证。阅读详情

jiajiaren

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

jiajiaren 2013-09-09

打赏
举报

请问如果要循环打印怎么写？ 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

lzw_0736 2013-09-09

打赏
举报


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 2013-09-09

打赏
举报

引用 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

唐诗三百首 2013-09-06

打赏
举报


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)
*/

华夏小卒 2013-09-06

打赏
举报

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

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

相关推荐