22,207
社区成员
发帖
与我相关
我的任务
分享
select dbo.RegexReplace(email, '(?<=@[^@、]*)@[^@、]*|(?<=\.com)\.com', '', 1, 1) from @table
declare @table table(email varchar(200))
insert into @table
select '1.adfadf@126.com@163.com、adfadf@sina.com@sohu.com、adfadf@tom.com@163.com'
union all
select '2.1.adfadf@126.com.com、adfadf@sina.com.com'
select dbo.regexReplace (email,'.com[@][a-z0-9]+.com[、]','.com、',1,0) from @table
/*
1.adfadf@126.com、adfadf@sina.com、adfadf@tom.com@163.com
2.1.adfadf@126.com.com、adfadf@sina.com.com
*/
--开启xp_cmdshell
--SQL Server blocked access to procedure 'xp_cmdshell'
sp_configure 'show advanced options', 1
go
reconfigure
go
sp_configure 'xp_cmdshell', 1
go
reconfigure
go
--开启sp_OACreate
--SQL Server blocked access to procedure 'sys.sp_OACreate'
sp_configure 'show advanced options', 1;
go
reconfigure;
go
sp_configure 'ole automation procedures', 1;
go
reconfigure;
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
end
return @result
end
declare @table table(email varchar(200))
insert into @table
select '1.adfadf@126.com@163.com、adfadf@sina.com@sohu.com、adfadf@tom.com@163.com'
union all
select '2.1.adfadf@126.com.com、adfadf@sina.com.com'
select dbo.regexReplace (email,'.com[@][s][o][h][u].com、','.com、',1,0) from @table
/*
1.adfadf@126.com@163.com、adfadf@sina.com、adfadf@tom.com@163.com
2.1.adfadf@126.com.com、adfadf@sina.com.com
*/