22,207
社区成员
发帖
与我相关
我的任务
分享
create table tb(ID int,NAME varchar(10),TEL varchar(50))
insert into tb values(1 ,'www' ,'13669142179,8712292')
insert into tb values(2 ,'xxx' ,null)
insert into tb values(3 ,'zzz' ,'skjdfh')
insert into tb values(4 ,'ddd' ,'8721568,13609220863')
insert into tb values(5 ,'eee' ,'13891278888,8723120')
insert into tb values(6 ,'mmm' ,'15809125855,15991226088')
go
select id , name , tel from
(
SELECT A.id,a.name ,B.tel
FROM(
SELECT id,name, [tel] = CONVERT(xml,'<root><v>' + REPLACE([tel], ',', '</v><v>') + '</v></root>') FROM tb
)A
OUTER APPLY(
SELECT tel = N.v.value('.', 'varchar(100)') FROM A.[tel].nodes('/root/v') N(v)
)B
) t
where left(tel,3) in ('136','159','138','158') and len(tel) = 11
drop table tb
/*
id name tel
----------- ---------- ----------------------------------------------------------------------------------------------------
1 www 13669142179
4 ddd 13609220863
5 eee 13891278888
6 mmm 15809125855
6 mmm 15991226088
(5 行受影响)
*/
create table tb(ID int,NAME varchar(10),TEL varchar(50))
insert into tb values(1 ,'www' ,'13669142179,8712292')
insert into tb values(2 ,'xxx' ,null)
insert into tb values(3 ,'zzz' ,'skjdfh')
insert into tb values(4 ,'ddd' ,'8721568,13609220863')
insert into tb values(5 ,'eee' ,'13891278888,8723120')
insert into tb values(6 ,'mmm' ,'15809125855,15991226088')
go
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b
select id , name , tel from
(
SELECT A.id,a.name, tel = SUBSTRING(A.[tel], B.id, CHARINDEX(',', A.[tel] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[tel], B.id, 1) = ','
) t
where left(tel,3) in ('136','159','138','158') and len(tel) = 11
DROP TABLE #
drop table tb
/*
id name tel
----------- ---------- --------------------------------------------------
1 www 13669142179
5 eee 13891278888
6 mmm 15809125855
4 ddd 13609220863
6 mmm 15991226088
(所影响的行数为 5 行)
*/
/*
标题:数据拆分1
作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间:2008-11-20
地点:广东深圳
描述
有表tb, 如下:
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id value
----------- --------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
*/
--1. 旧的解决方法(sql server 2000)
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b
SELECT A.id, value = SUBSTRING(A.[value], B.id, CHARINDEX(',', A.[value] + ',', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(',' + A.[value], B.id, 1) = ','
DROP TABLE #
--2. 新的解决方法(sql server 2005)
create table tb(id int,value varchar(30))
insert into tb values(1,'aa,bb')
insert into tb values(2,'aaa,bbb,ccc')
go
--方法1)
SELECT A.id, B.value
FROM(
SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb
)A
OUTER APPLY(
SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v)
)B
--方法2)
select
a.id,b.[value]
from
(select id,[value]=convert(xml,'<root><v>'+replace([value],',','</v><v>')+'</v></root>') from tb)a
outer apply
(select [value]=C.v.value('.','nvarchar(100)') from a.[value].nodes('/root/v')C(v))b
--方法3)
;with tt as
(select id,[value]=cast(left([value],charindex(',',[value]+',')-1) as nvarchar(100)),Split=cast(stuff([value]+',',1,charindex(',',[value]+','),'') as nvarchar(100)) from tb
union all
select id,[value]=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from tt where split>''
)
select id,[value] from tt order by id option (MAXRECURSION 0)
DROP TABLE tb
/*
id value
----------- ------------------------------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
(5 行受影响)
*/