/* 大版主邹建大哥提供 */if exists (select * from sysobjects where id=object_id(N't') and objectproperty(id,N'isUserTable')=1) drop table t create table t(id int identity(1,1),col varchar(50)) insert t select '82288110/13011119999' union all select '某某13109011111.某某' union all select '822881110,13401000099' union all select '13011119999 13199887766' go create function dbo.regexReplace ( @source varchar(5000), --原字符串 @regexp varchar(1000), --正则表达式 @replace varchar(1000), --替换值 @globalReplace bit = 0, --是否是全局替换 @ignoreCase bit = 0 --是否忽略大小? ) returnS varchar(1000) AS begin declare @hr integer declare @objRegExp integer declare @result varchar(5000)exec @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT IF @hr <> 0 begin exec @hr = sp_OADestroy @objRegExp return null end exec @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp IF @hr <> 0 begin exec @hr = sp_OADestroy @objRegExp return null end exec @hr = sp_OASetProperty @objRegExp, 'Global', @globalReplace IF @hr <> 0 begin exec @hr = sp_OADestroy @objRegExp return null end exec @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignoreCase IF @hr <> 0 begin exec @hr = sp_OADestroy @objRegExp return null end exec @hr = sp_OAMethod @objRegExp, 'Replace', @result OUTPUT, @source, @replace IF @hr <> 0 begin exec @hr = sp_OADestroy @objRegExp return null end exec @hr = sp_OADestroy @objRegExp IF @hr <> 0 begin return null endreturn @result end GO create function f_split(@col int,@col1 varchar(50)) returns @t table (id int,col varchar(20)) as begin select @col1=replace(@col1,a,b) from( select a=' ',b='/' union all select a=',',b='/' union all select a='.',b='/' ) a declare @i int set @i=len(@col1+'a')-2 while charindex('/',@col1)>0 begin insert @t values (@col,dbo.regexReplace(left(@col1,charindex('/',@col1)-1),'[^0-9]','',1,1)) set @col1=stuff(@col1,1,charindex('/',@col1),'') end if dbo.regexReplace(@col1,'[^0-9]','',1,1)<>''--If there are two telephone numbers in the row. insert @t values (@col,dbo.regexReplace(@col1,'[^0-9]','',1,1)) return end go create function f_go(@col int) returns @t table(t_id int identity(1,1),id int,col varchar(20)) as begin declare @t1 table(t_id int identity(1,1),id int,col varchar(100)) declare @sql varchar(200),@sql1 int,@i int set @sql='' insert @t1 select * from t where @col>=(case when @col=0 then 0 else id end) select @i=count(*) from @t1 while @i>0 begin select top 1 @sql=col,@sql1=id from @t1 order by t_id delete from @t1 where t_id=(select top 1 t_id from @t1 order by t_id) insert @t select id,col from dbo.f_split(@sql1,@sql) set @i=@i-1 end return end goselect * from t select * from dbo.f_go(0)drop function f_split drop function f_go drop function regexReplace drop table t/* id col ----------- -------------------------------------------------- 1 82288110/13011119999 2 某某13109011111.某某 3 822881110,13401000099 4 13011119999 13199887766(所影?的行?? 4 行)t_id id col ----------- ----------- -------------------- 1 1 82288110 2 1 13011119999 3 2 13109011111 4 3 822881110 5 3 13401000099 6 4 13011119999 7 4 13199887766(所影?的行?? 7 行) */
/*
大版主邹建大哥提供
*/if exists (select * from sysobjects where id=object_id(N't') and objectproperty(id,N'isUserTable')=1)
drop table t create table t(id int identity(1,1),col varchar(50))
insert t
select '82288110/13011119999' union all
select '某某13109011111.某某' union all
select '822881110,13401000099' union all
select '13011119999 13199887766'
go
create function dbo.regexReplace
(
@source varchar(5000), --原字符串
@regexp varchar(1000), --正则表达式
@replace varchar(1000), --替换值
@globalReplace bit = 0, --是否是全局替换
@ignoreCase bit = 0 --是否忽略大小?
)
returnS varchar(1000) AS
begin
declare @hr integer
declare @objRegExp integer
declare @result varchar(5000)exec @hr = sp_OACreate 'VBScript.RegExp', @objRegExp OUTPUT
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OASetProperty @objRegExp, 'Pattern', @regexp
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OASetProperty @objRegExp, 'Global', @globalReplace
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OASetProperty @objRegExp, 'IgnoreCase', @ignoreCase
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OAMethod @objRegExp, 'Replace', @result OUTPUT, @source, @replace
IF @hr <> 0 begin
exec @hr = sp_OADestroy @objRegExp
return null
end
exec @hr = sp_OADestroy @objRegExp
IF @hr <> 0 begin
return null
endreturn @result
end
GO
create function f_split(@col int,@col1 varchar(50))
returns @t table (id int,col varchar(20))
as
begin
select @col1=replace(@col1,a,b)
from(
select a=' ',b='/' union all
select a=',',b='/' union all
select a='.',b='/'
) a
declare @i int
set @i=len(@col1+'a')-2
while charindex('/',@col1)>0
begin
insert @t values (@col,dbo.regexReplace(left(@col1,charindex('/',@col1)-1),'[^0-9]','',1,1))
set @col1=stuff(@col1,1,charindex('/',@col1),'')
end
if dbo.regexReplace(@col1,'[^0-9]','',1,1)<>''--If there are two telephone numbers in the row.
insert @t values (@col,dbo.regexReplace(@col1,'[^0-9]','',1,1))
return
end
go
create function f_go(@col int)
returns @t table(t_id int identity(1,1),id int,col varchar(20))
as
begin
declare @t1 table(t_id int identity(1,1),id int,col varchar(100))
declare @sql varchar(200),@sql1 int,@i int
set @sql=''
insert @t1
select * from t where @col>=(case when @col=0 then 0 else id end)
select @i=count(*) from @t1
while @i>0
begin
select top 1 @sql=col,@sql1=id from @t1 order by t_id
delete from @t1 where t_id=(select top 1 t_id from @t1 order by t_id)
insert @t
select id,col from dbo.f_split(@sql1,@sql)
set @i=@i-1
end
return
end
goselect * from t
select * from dbo.f_go(0)drop function f_split
drop function f_go
drop function regexReplace
drop table t/*
id col
----------- --------------------------------------------------
1 82288110/13011119999
2 某某13109011111.某某
3 822881110,13401000099
4 13011119999 13199887766(所影?的行?? 4 行)t_id id col
----------- ----------- --------------------
1 1 82288110
2 1 13011119999
3 2 13109011111
4 3 822881110
5 3 13401000099
6 4 13011119999
7 4 13199887766(所影?的行?? 7 行)
*/