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



需求说明:已经入库的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