Using regular expressions in SQL Server

One, check the configuration, if ' Show advanced options ', ' Ole Automation procedures ', ' Ad Hoc distributed Queries ' config_value and Run_value is not 1, Two scripts are required to execute.

Second, execute the script, open SQL Server access block to the component.

Use master
GO
sp_configure ' show advanced options ', 1;
GO
RECONFIGURE;
GO
sp_configure ' Ole Automation procedures ', 1;
GO
RECONFIGURE;
GO
sp_configure ' Ad Hoc distributed Queries ', 1;
GO
RECONFIGURE;
GO

Third, create the function (except for the database name and the name of the function will remain unchanged)

Use edrmsofficedb;--(database name)
GO
If object_id (N ' dbo. Reg ') is not null--(function name)
Drop function dbo. Reg;
GO
CREATE FUNCTION Reg
(
@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

Iv. Testing

The query field contains records other than letters and numbers:

SELECT CategoryId from Category WHERE dbo. Reg (' ^[a-za-z0-9]+$ ', CategoryId) =0

Using regular expressions in SQL Server