用sql话语截取某特定的值

2013-12-15

用sql语句截取某特定的值有一串长度没固定的文本，比如：CIN000291:10,CIN000292:30,CIN000296:20规律固定的

用sql语句截取某特定的值
有一串长度没固定的文本，比如：CIN000291:10,CIN000292:30,CIN000296:20
规律固定的，以逗号分隔开的一串字符为一次插入的值；但长度没固定，或许有10段值合在一起的，现在想用SQL语句把冒号后面且逗号前面的数字截取出来（10 30 20），有什么好办法？
[解决办法]


if OBJECT_ID('tempdb..#temp', 'u') is not null   drop table #temp;
create table #temp([msg] varchar(max));
insert #temp
SELECT 'CIN000291:10,CIN000292:30,CIN000296:20'

WITH a1 AS
(
SELECT b.*
FROM
(SELECT msg=CONVERT(XML, '<root><v>'+replace(RTRIM(LTRIM(msg)),',','</v><v>')+'</v></root>') FROM #temp) a
OUTER APPLY
(SELECT msg = C.v.value('.','NVARCHAR(MAX)') FROM a.msg.nodes('/root/v') C(v)) b
)
,a2 AS
(
SELECT b.*,ROW_NUMBER() OVER(ORDER BY @@servername)%2 re
FROM
(SELECT msg=CONVERT(XML, '<root><v>'+replace(RTRIM(LTRIM(msg)),':','</v><v>')+'</v></root>') FROM a1) a
OUTER APPLY
(SELECT msg = C.v.value('.','NVARCHAR(MAX)') FROM a.msg.nodes('/root/v') C(v)) b
)
select msg=STUFF((SELECT ' '+msg FROM a2 WHERE re=0 FOR XML PATH('')),1,1,'')

[解决办法]




  SELECT  *
  FROM dbo.[Split]('CIN000291:10,CIN000292:30,CIN000296:20',',') s
  OUTER APPLY(
SELECT * FROM dbo.[Split](s.[Value],':') s2 WHERE s2.Id=2
  ) r1


--result-------------

IdValueIdValue
1CIN000291:10210
2CIN000292:30230
3CIN000296:20220

[解决办法]

create table #tb (col varchar(8000))

insert into #tb 
select 'CIN000291:10,CIN000292:30,CIN000296:20'
select * from #tb 

;with cte as 
(select a.col ,b.number,str=SUBSTRING (a.col ,b.number ,CHARINDEX (',',a.col+',' ,b.number )-b.number)  from #tb  a join master .dbo.spt_values b  on b.number <len(a.col) 
where b.type ='P' and substring (','+a.col ,b.number,1)=',' )

select *,SUBSTRING (str,CHARINDEX (':',str,1)+1,LEN(str)) from cte 

drop table #tb

热点排行

SQL Server

用sql话语截取某特定的值