Application of Regular Expressions in ms SQL Server

Application of Regular Expressions in ms SQL Server the Oracle database provides regular expressions by default. For details, see wikipedia. For example: REGEXP_LIKE (), REGEXP_INSTR (), REGEXP_SUBSTR (), REGEXP_REPLACE (), which is not directly provided by the ms SQL Server. However, MS has left a hand, that is, the Common Language Runtime when CLR is running in the public Language ). This method can be used for SQL SERVER 2005 and later versions. Is MS doing this to push. NET?

To put it bluntly, I have fully implemented ms SQL Server's support for regular expressions by referring to Oracle. Mainly uses CLR to compile the following five scalar functions: REGEXP_LIKE (), REGEXP_INSTR (), regexp_limit 2 (), REGEXP_SUBSTR (), REGEXP_REPLACE ().

Function Description:

1. Regexp_Like (soure, pattern)
Check whether a regular expression-matched string exists in the source string. If yes, 1 is returned. If no regular expression exists, 0 is returned.
Source: source string
Pattern: Regular Expression
2. Regexp_InStr (soure, pattern, position, num)
Matches the source string with the regular expression, and returns the starting position value of the match.
Source: source string
Pattern: Regular Expression
Possition: Start position of the string
Num: returns the nth match.
3. regexp_1_2 (soure, pattern)
Matches the source string and regular expression, and returns the first 1st matching start position values.
(It is actually a reload of Regexp_InStr. Ms SQL does not support overloading, so it is also named)
Source: source string
Pattern: Regular Expression
4. Regexp_SubStr (soure, pattern)
Matches the source string with the regular expression, and returns the matched substring.
Source: source string
Pattern: Regular Expression
5. Regexp_Replace (soure, pattern, replace_string)
Matches the source string and regular expression. The return value is equal to replacing the matching part with the new string of the specified string.
Source: source string
Pattern: Regular Expression
Replace_string: string to be replaced

Deployment:
1. It must be deployed on the database server;
2. environment requirements: the. Net Framework 3.5 operating system has only tested 64-bit, 32-bit has not been tested );
3. Download the program and place it in the C: \ WINDOWS directory. Of course, it can be another directory, but you need to change the deployment script yourself). Download the program.
4. Download the deployment script. Download the deployment script.
5. run SQL Server Management Studio. log on to the database using regular expressions and open and execute the deployment script.

Verification:
Note:
1. The execution results of native Oracle functions are compared with the self-compiled CLR execution results;
2. The regular expression '(\ w) \ 1' is used to match any repeated word characters in a string;

In the current context, it is to find the information 650 that contains the stacked words in the User table name field) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/130032KC-0.gif "/>)

Oracle:

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/130032NY-1.png "style =" float: none; "title =" 1.png"/>

SQL Server:

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/13003220P-2.png "style =" float: none; "title =" 2.png"/>

The results are consistent. 650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131229/130032H10-3.gif "/>

This article is from the "liangfeng Fengshui Bi Lian Xiang" blog. For more information, please contact the author!