Use regular expressions in SQL Server 2000

There is a need in the past two days to determine the string format in the database, so I collected some information from the Internet and sorted it out.

The following is a user-defined function. You can call this function to determine whether the specified string complies with the regular expression rules.
Create Function DBO. find_regular_expression
(
@ Source varchar (5000), -- source string to be matched
@ Regexp varchar (1000), -- Regular Expression
@ Ignorecase bit = 0 -- case sensitive. The default value is false.
)
Returns bit -- Return 0-false, 1-true
As
Begin

-- 0 (successful) or non-zero number (failed) is the integer of the hresult returned by the OLE Automation Object.
Declare @ HR integer

-- Used to save the returned object token for subsequent operations on the object
Declare @ objregexp integer declare @ objmatches integer

-- Save the result
Declare @ results bit

/*
To create an OLE object instance, only members of the SysAdmin fixed server role can execute sp_oacreate and make sure that the machine contains the VBScript. Regexp class library.
*/
Exec @ hR = sp_oacreate 'vbscript. Regexp ', @ objregexp output
If @ HR <> 0 begin
Set @ Results = 0
Return @ results
End
/*
The following three attributes are set for the new object. The following is an example of common attributes in 'vbscript. Regexp:
Dim RegEx, match, matches 'to create a variable.
Set RegEx = new Regexp 'creates a general expression.
RegEx. pattern = patrn 'setting mode.
RegEx. ignorecase = true' is used to set Case sensitivity.
RegEx. Global = true' to set global availability.
Set matches = RegEx. Execute (string) 'duplicate match set
Regexptest = RegEx. Execute (strng.
For each match in matches
Retstr = retstr & "match found at position"
Retstr = retstr & Match. firstindex & ". Match value is '"
Retstr = retstr & Match. Value & "'." & vbcrlf next
Regexptest = retstr

*/
Exec @ hR = sp_oasetproperty @ objregexp, 'pattern', @ Regexp
If @ HR <> 0 begin
Set @ Results = 0
Return @ results
End
Exec @ hR = sp_oasetproperty @ objregexp, 'global', false
If @ HR <> 0 begin
Set @ Results = 0
Return @ results
End
Exec @ hR = sp_oasetproperty @ objregexp, 'ignorecase ', @ ignorecase
If @ HR <> 0 begin
Set @ Results = 0
Return @ results
End
-- Call the object Method
Exec @ hR = sp_oamethod @ objregexp, 'test', @ results output, @ Source
If @ HR <> 0 begin
Set @ Results = 0
Return @ results
End
-- Release the created OLE object
Exec @ hR = sp_oadestroy @ objregexp
If @ HR <> 0 begin
Set @ Results = 0
Return @ results
End
Return @ results
End

The following is a simple test SQL statement that can be run directly in the query analyzer.
Declare @ intlength as integer
Declare @ vchregularexpression as varchar (50)
Declare @ vchsourcestring as varchar (50)
Declare @ vchsourcestring2 as varchar (50)
Declare @ bithasnospecialcharacters as bit

-- Initialize the variable
Set @ vchsourcestring = 'test one this is a test !! '
Set @ vchsourcestring2 = 'test two this is a Test'

-- Our regular expression should be similar
-- [A-Za-Z] {}
-- For example: [A-Za-Z] {10}... a cross character string

-- Get the string length
Set @ intlength = Len (@ vchsourcestring)

-- Set the complete Regular Expression
Set @ vchregularexpression = '[A-Za-Z] {' + Cast (@ intlength as varchar) + '}'

-- Whether there are any special characters
Set @ bithasnospecialcharacters = DBO. find_regular_expression (@ vchsourcestring, @ vchregularexpression, 0)

Print @ vchsourcestring
If @ bithasnospecialcharacters = 1 begin
Print 'no special characters .'
End else begin
Print 'special characters found .'
End

Print '**************'

-- Get the string length
Set @ intlength = Len (@ vchsourcestring2)

-- Set the complete Regular Expression
Set @ vchregularexpression = '[A-Za-Z] {' + Cast (@ intlength as varchar) + '}'

-- Whether there are any special characters
Set @ bithasnospecialcharacters = DBO. find_regular_expression (@ vchsourcestring2, @ vchregularexpression, 0)

Print @ vchsourcestring2
If @ bithasnospecialcharacters = 1 begin
Print 'no special characters .'
End else begin
Print 'special characters found .'
End

Go