create table TA
(
id int,
tel varchar(8000)
)
insert into TA
select 1,'010-12345678;13200001111'
union all select 2,'13566663333;0315-6666555;8988666'
union all select 3,'83685555;15801270000;13200009999'select
a.id,b.tel
from
(select id,tel=convert(xml,'<root><v>'+replace(tel,';','</v><v>')+'</v></root>') from TA)a
outer apply
(select tel=C.v.value('.','nvarchar(100)') from a.tel.nodes('/root/v')C(v))b
where b.tel like '1%'
id tel
----------- ----------------------------------------------------------------------------------------------------
1 13200001111
2 13566663333
3 15801270000
3 13200009999(4 行受影响)
create table TA(id int,tel varchar(80))
insert into TA
select 1,'010-12345678;13200001111' UNION ALL
select 2,'13566663333;0315-6666555;8988666' UNION ALL
select 3,'83685555;15801270000;13200009999' SELECT A.id, B.tel
FROM(
SELECT id, tel = CONVERT(xml,' <root> <v>' + REPLACE(tel, ';', ' </v> <v>') + ' </v> </root>') FROM TA )A
OUTER APPLY(
SELECT tel = N.v.value('.', 'varchar(100)') FROM A.tel.nodes('/root/v') N(v)
)B
WHERE len(B.tel)='11'drop table TA
id tel
1 13200001111
2 13566663333
3 15801270000
3 13200009999
大烏龜有個SQL2000的寫法.
create table tb(id int,tel varchar(50))
insert into tb select 1,'010-12345678;13200001111'
insert into tb select 2,'13566663333;0315-6666555;8988666'
insert into tb select 3,'83685555;15801270000;13200009999'
go
--建立个临时表
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO # FROM syscolumns a, syscolumns b select * from
(
SELECT A.id, tel = SUBSTRING(A.[tel], B.id, CHARINDEX(';', A.[tel] + ';', B.id) - B.id)
FROM tb A, # B
WHERE SUBSTRING(';' + A.[tel], B.id, 1) = ';'
) m
where left(tel,1) = '1'DROP TABLE #drop table tb/*
id tel
----------- --------------------------------------------------
1 13200001111
2 13566663333
3 15801270000
3 13200009999(所影响的行数为 4 行)*/