MSSQL 根据正则表达式判断更新字段

本文关键字: mssql, sqlserver, 正则

需求说明：已经入库的mobile字段需要更新判断运营商（移动/联通/电信），图方便需要直接在数据库更新。

步骤：

1,创建SQLSERVER的自定义正则匹配函数

create function dbo.RegexMatch (

@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

 

2, 修改安全控制

sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ole Automation Procedures’, 1;
GO
RECONFIGURE;
GO

3, 网上找到最新号段和正则

移动号段：
134 135 136 137 138 139 144 147 148 150 151 152 157 158 159 172 178 182 183 184 187 188 198

联通号段：
130 131 132 145 146 155 156 166 171 175 176 185 186

电信号段：
133 141 149 153 170 173 174 177 179 180 181 189 199

4, 开始更新

update XXX set mobiletype=’移动’ where dbo.RegexMatch(‘1(3[4-9]|4[478]|5[012789]|7[28]|8[23478]|9[8])\d{8}$’,mobile)=1
update XXX set mobiletype=’联通’ where dbo.RegexMatch(‘1(3[0-2]|4[56]|5[56]|6[6]|7[156]|8[56])\d{8}$’,mobile)=1
update XXX set mobiletype=’电信’ where dbo.RegexMatch(‘1(3[3]|4[19]|5[3]|7[03479]|8[019]|9[9])\d{8}$’,mobile)=1