SQL使用正则替换指定位置(SQLSERVER)

--使用正则查询第三位之后是否有字母
SELECT ContractNo,* FROM business WHERE dbo.RegexMatch3('.*\d{6}([a-z])+.*',ContractNo)=1

--更新字符串从数字起第7~8位，替换为1a
SELECT dbo.RegexReplace('YJ181002217019000004','(\D*\d{6})(\d{2})(.*)','$11a$3',1)
update business set ContractNo =(dbo.RegexReplace(ContractNo,'(\D*\d{6})(\d{2})(.*)','$11a$3',1))

--新建函数dbo.RegexReplace
IF OBJECT_ID(N'dbo.RegexReplace') IS NOT NULL
DROP FUNCTION dbo.RegexReplace
GO
CREATE FUNCTION dbo.RegexReplace
(
@string VARCHAR(MAX), --被替换的字符串
@pattern VARCHAR(255), --替换模板
@replacestr VARCHAR(255), --替换后的字符串
@IgnoreCase INT = 0 --0区分大小写 1不区分大小写
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @objRegex INT, @retstr VARCHAR(8000)
--创建对象
EXEC sp_OACreate 'VBScript.RegExp', @objRegex OUT
--设置属性
EXEC sp_OASetProperty @objRegex, 'Pattern', @pattern
EXEC sp_OASetProperty @objRegex, 'IgnoreCase', @IgnoreCase
EXEC sp_OASetProperty @objRegex, 'Global', 1
--执行
EXEC sp_OAMethod @objRegex, 'Replace', @retstr OUT, @string, @replacestr
--释放
EXECUTE sp_OADestroy @objRegex
RETURN @retstr
END
GO

--为保证本函数的正常使用，还需要将Ole Automation Procedures选项置为1，具体语句为
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'Ole Automation Procedures', 1
RECONFIGURE

 

参考：https://www.cnblogs.com/worfdream/articles/3045017.html

 

#####其他可能有用的内容#####

--新建查询函数
go
create function dbo.RegexMatch3
(
@pattern varchar(2000),
@matchstring varchar(8000)
)
returns int
as
begin
declare @objRegexExp int
declare @strErrorMessage varchar(255)
declare @hr int,@match bit
exec @hr= sp_OACreate 'VBScript.RegExp', @objRegexExp out
if @hr = 0
exec @hr= sp_OASetProperty @objRegexExp, 'Pattern', @pattern
if @hr = 0
exec @hr= sp_OASetProperty @objRegexExp, 'IgnoreCase', 1
if @hr = 0
exec @hr= sp_OAMethod @objRegexExp, 'Test', @match OUT, @matchstring
if @hr <>0
begin
return null
end
exec sp_OADestroy @objRegexExp
return @match
end
go

--查询第三位之后是否有字母
SELECT ContractNo,* FROM business WHERE dbo.RegexMatch3('\S{3}([a-z])+.*',ContractNo)=1

--更新字符串指定位置的
update business set ContractNo = STUFF(ContractNo,8,1,'a')
UPDATE business SET ContractNo = STUFF(ContractNo,18,0,'-1234') WHERE businessid IN (378873,240656,683317)

 

--如有报错,执行以下脚本
exec sp_configure 'show advanced options', '1';
go
reconfigure;
go
exec sp_configure 'clr enabled', '1'
go
reconfigure;
exec sp_configure 'show advanced options', '1';
go

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ole Automation Procedures';
GO

 

参考：http://blog.sina.com.cn/s/blog_4e6dffee0102e7uz.html