Regular expression Summary _ regular expression in database

This article through two examples to introduce you to the database using regular expression summary, this is not much to say, the specific content please read the detailed below.

Example one:

The CREATE FUNCTION dbo. Regexptest ( 
@source varchar (),--Need a matching source string 
@regexp varchar (),--The regular expression 
@ignorecase bit =--is case-sensitive, Default to False 
) 
RETURNS bit-Returns the result-false,-true 
as 
BEGIN 
-(success) or not 0 digits (failure), the integer value of the HRESULT returned by the OLE Automation object. 
DECLARE @hr Integer 
--for saving the returned object token so that the object can then be manipulated 
DECLARE @objRegExp integer DECLARE @objMatches integer 
--Save Results 
DECLARE @results bit/ 
* 
Creates an OLE object instance, and only members of the sysadmin fixed server role can perform sp_oacreate and determine if there is a VBScript.RegExp class library in the machine. 
EXEC @hr = sp_OACreate ' VBScript.RegExp ', @objRegExp OUTPUT 
IF @hr <> BEGIN 
SET @results = return 
@results 
end 

The following three sets of three properties for the new object are set. Below is ' VBScript. Examples of commonly used properties in REGEXP ':

Dim regex,match,matches ' establishes the variable. 
Set regEx = New RegExp ' establishes a generic expression. 
regex.pattern= patrn ' Set mode. 
Regex.ignorecase = True ' Sets whether case sensitive. 
Regex.global=true ' Sets global availability. 
Set Matches=regex.execute (String) ' duplicate matching set regexptest = Regex.execute (strng) ' performs a search. For the match in matches ' repeat 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 SE T @results = return @results end EXEC @hr = sp_OASetProperty @objRegExp, ' IgnoreCase ', @ignorecase IF @hr <> BEG In SET @results = return @results End--Invoke object method EXEC @hr = sp_OAMethod @objRegExp, ' Test ', @results OUTPUT, @source I F @hr <> BEGIN SET @results = return @results End--Free the Created OLE object EXEC @hr= sp_OADestroy @objRegExp IF @hr <> BEGIN SET @results = return @results end return @results End--Open advanced options sp_ Configure ' Show advanced options ', reconfigure--set options to open sp_configure ' Ole automation procedures ', reconfigure

Example two:

Match tests using the regexp and not regexp operators (or rlike and not rlike, which are synonyms).

Some characters of the extended regular expression are:

· ‘.' matches any single character.
· Character class "[...]" Matches any character within the square brackets. For example, "[ABC]" matches "a", "B", or "C". To name the range of characters, use a "-". "[A-z]" matches any letter, and "[0-9]" matches any number.
· "*" matches 0 or more characters in front of it. For example, "x*" matches any number of "X" characters, "[0-9]*" matches any number of numbers, and ". *" matches any number of any characters.

If the regexp pattern matches anywhere that is tested, the pattern matches (unlike like pattern matching, which 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 beginning of the pattern or at the end of the pattern with "$".

MySQL instance:

Query the data in the myname that starts with W:

Query the data in myname with the end of G:

The query contains the data for W:

Contains six characters of data:

You can use the "{n}" "Repeat N Times" operator:

The above content is the cloud Habitat Community Small series to everybody through two examples to share the database to use the regular expression summary, hoped that has the help to everybody.