Shulou(Shulou.com)06/02 Report--

This article mainly shows you "how to use regular expressions in the database". The content is easy to understand and clear. I hope it can help you solve your doubts. Let me lead you to study and learn the article "how to use regular expressions in the database".

Example 1:

CREATE FUNCTION dbo.RegExpTest (@ source varchar (),-- a matching source string @ regexp varchar ()),-- regular expression @ ignorecase bit =-- whether case-sensitive, default is false) RETURNS bit-- returns the result-- false,-true AS BEGIN-- (success) or non-zero number (failure), which is the integer value of HRESULT returned by the OLE automation object. DECLARE @ hr integer-used to save the returned object token for later operations on the object DECLARE @ objRegExp integer DECLARE @ objMatches integer-save the result DECLARE @ results bit / * create an OLE object instance, only members of the sysadmin fixed server role can execute sp_OACreate, and make sure that there is a VBScript.RegExp class library * / EXEC @ hr = sp_OACreate 'VBScript.RegExp', @ objRegExp OUTPUT IF @ hr BEGIN SET @ results = RETURN @ results END / *

The following three are the three properties that set the new object. Here are examples of attributes commonly used in 'VBScript.RegExp':

Dim regEx,Match,Matches' creates a variable. Set regEx = New RegExp 'establishes a general expression. RegEx.Pattern= patrn 'setting mode. RegEx.IgnoreCase = True 'sets whether it is case-sensitive. RegEx.Global=True 'sets global availability. Set Matches=regEx.Execute (string) 'repeat matching set RegExpTest = regEx.Execute (strng)' performs the search. For each match in matches' duplicate matching 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 = RETURN @ results END EXEC @ hr = sp_OASetProperty @ objRegExp,' Global', false IF @ hr BEGIN SET @ results = RETURN @ results END EXEC @ hr = sp_OASetProperty @ objRegExp, 'IgnoreCase' @ 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 RETURN @ results END-- Open the advanced option sp_configure' show advanced options',reconfigure-- setting option Open sp_configure 'Ole Automation Procedures',reconfigure

Example 2:

Match tests are performed using the REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, which are synonyms).

Some of the characters that extend regular expressions are:

'.' Matches any single character.

Character class "[.]" Matches any character in square brackets. For example, "[abc]" matches "a", "b", or "c". To name the range of characters, use a "-". "[amurz]" matches any letter, while "[0-9]" matches any number.

"*" matches zero or more characters that precede it. For example, "x*" matches any number of "x" characters, "[0-9] *" matches any number of numbers, and ". *" matches any number of characters.

If the REGEXP pattern matches anywhere in the value under test, the pattern matches (unlike LIKE pattern matching, where the pattern matches only if it matches the entire value).

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

Mysql instance:

Query the data in myname that begins with w:

Query the data ending in g in myname:

Query the data containing w:

Data containing six characters:

You can use the "{n}"repeat n times" operator:

These are all the contents of the article "how to use regular expressions in a database". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.