Use regular expression summary and regular expression summary in the database

Use regular expression summary and regular expression summary in the database

This article provides two examples to illustrate the use of regular expressions in the database.

Example 1:

Create function dbo. regExpTest (@ source varchar (), -- source string to be matched @ regexp varchar (), -- Regular Expression @ ignorecase bit = -- Case Sensitive, false by default) RETURNS bit -- RETURNS the result-false,-true as begin -- (successful) or a non-zero number (failed), which is the integer of the hresult returned by the OLE Automation Object. DECLARE @ hr integer -- used to save the returned object token, so that you can then operate on this 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 confirm that there is VBScript in the machine. regExp class library */EXEC @ hr = sp_OACreate 'vbscript. regExp ', @ objRegExp output if @ hr <> begin set @ results = 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) 'to Execute the search. For each match in matches 'duplicate Match set 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 <> begin set @ results = RETURN @ results end exec @ hr = sp_OASetProperty @ objRegExp, 'global ', false IF @ hr <> begin set @ results = RETURN @ results end exec @ hr = sp_OASetProperty @ objRegExp, 'ignorecasase ', @ ignorecase IF @ hr <> begin set @ results = RETURN @ results END -- call the object method EXEC @ hr = sp_OAMethod @ objRegExp, 'test', @ results OUTPUT, @ source IF @ hr <> begin set @ results = RETURN @ results END -- release the created OLE object EXEC @ hr = sp_OADestroy @ objRegExp IF @ hr <> begin set @ results = RETURN @ results END -- open the advanced option sp_configure 'show advanced options ', reconfigure -- set options to enable sp_configure 'ole Automation Procedures 'and reconfigure

Example 2:

Use the REGEXP and not regexp operators (or RLIKE and not rlike, which are synonyms) for matching tests.

Some Characters of the extended regular expression are:

· '.' Matches any single character.
· The character class "[...]" matches any character in square brackets. For example, "[abc]" matches "a", "B", or "c ". To name the character range, use a hyphen (-). "[A-z]" matches any letter, and "[0-9]" matches any number.
· "*" Matches zero or multiple characters before it. For example, "x *" matches any number of "x" characters, "[0-9] *" matches any number, and ". * "matches any number of characters.

If the REGEXP pattern matches any part of the tested value, the pattern matches (different from the LIKE pattern match. The pattern matches only the entire value ).

To locate a pattern so that it must match the start or end of the tested value, use "^" at the start of the pattern or "$" at the end of the pattern ".

Mysql instance:

Query data starting with w in myname:

Query Data ending with g in myname:

Query data containing w:

Data that contains six characters:

You can use the "{n}" "repeated n times" OPERATOR:

The above content is a summary of the regular expression used in the database shared with you through two examples. I hope it will help you.