SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL 2000 DBA Toolkit Part 2

By Michael Coles, (first published: 2006/04/20)

SQL 2000 DBA Toolkit: Part Two - Regular Expressions

"Begin at the beginning and go on till you come to the end: then stop." - The King, Alice in Wonderland


Download the DBA Toolkit Here

This is Part Two of a series of articles on the SQL 2000 DBA Toolkit. In Part One we discussed the DBA Toolkit's encryption and key management features. In this article we'll take a look at the included regular expression parsing tools.

As always, feel free to use these tools as you see fit. If you find them useful, please drop me a line and let me know which ones you use, how you use them, and what you find most useful.


Click here to read the first article

Text Processing

Text processing is a big part of SQL Server database and application development. Sometimes you need to transform, find patterns in, or just search text; but text processing is the bread and butter of many SQL-based applications.

In recognition of the importance of text processing on SQL Server, I've included several text processing tools in the toolkit. Some are re-writes of previously released tools, like the Sound Matching and Phonetic Toolkit. Others, like regular expression parsing, are new to the toolkit. This article focuses on regular expression tools in the toolkit; the next article will discuss the phonetic matching tools included.


Installation of the Toolkit is covered in Part One of this series. Basically, just copy the DLL's to your MSSQL\BINN directory and run the INSTALL.SQL script. Installation troubleshooting steps are covered in the README.RTF file included in the \DOCS directory.

In addition, there is a Windows Compiled Help file (DBATOOLKIT.CHM) included in the \DOCS directory. This Help File is included as your first line for online Q&A.

Regular Expression Tools

Regular expressions (regex's) are a precise notation for approximate matching. One of the most popular styles of regular expression syntax is the Perl syntax. The regular expression tools in the toolkit use the Boost::Regex++ library, which is compliant with Perl syntax. NOTE: Microsoft's regular expression syntax differs somewhat from Perl syntax, so .NET-specific regular expressions might require tweaking to use with these tools.

The Functions

fn_regex_match (@searchexpression, @searchstring, @options)

The first regex tool in the toolkit is the fn_regex_match function. This function accepts three parameters:

  • @searchexpression is the Perl-syntax regular expression to use in the search,
  • @searchstring is the string to match against, and
  • @options are the user-assignable options for this match. The only option supported in this version of the toolkit is 'I+', which turns case-insensitivity on. By default regex matches are considered case-sensitive.

The fn_regex_match function will look for a regular expression match in the search string. If a match is found, fn_regex_match returns a 'Y', otherwise it returns an 'N' to indicate no match was found. The following examples illustrate fn_regex_match.

This example returns 'Y', since a pattern of one or more numbers can be found in the search string:

SELECT dbo.fn_regex_match ('\d+', '$1,234.56', NULL)

This example returns 'N' since the first character of the search string is not a quotation mark (\")

SELECT dbo.fn_regex_match ('^\"', 'He said "Hi"', NULL)

This example returns 'N' since there is a comma in the middle of the search string

SELECT dbo.fn_regex_match ('^\d+$', '123,456', NULL)

This example returns 'Y', but only because we turned case-insensitivity on (I+ option). It will not match if case-insensitivy is off (default).

SELECT dbo.fn_regex_match ('^[A-Z]+$', 'George', 'I+')

Figure 1. Sample fn_regex_match result

xp_regex_search (@searchexpression, @searchstring, @options)

This extended procedure searches for, and returns, all matches of the search expression within the search string. The parameters are:

  • @searchexpression is the Perl-style regular expression,
  • @searchstring is the string to search
  • @options are user options. Currently there are two options supported for xp_regex_search: 'I+' turns case-insensitivity on. 'G+' turns group captures on. When group capture is on, all sub-groups of matches in your regular expression are returned as well as the complete match. See below for an example of group captures.

By default, regex searches are case-sensitive, and group captures are turned off.

The xp_regex_search extended procedure returns a table with three columns: MatchNum, GroupNum and MatchText.

  • MatchNum is the number of the match. Matches are numbered from left to right as they are encountered.
  • GroupNum is the number of the sub-group captured in this match. The complete match is always numbered 1; subsequent sub-group captures will have the same MatchNum, but the GroupNum increases by 1 for each sub-group.
  • MatchText is the text that matched the regular expression, or in the case of sub-group captures, the text that matched the sub-group in the regular expression.

Sub-groups are marked in Perl regular expression syntax by wrapping them in parentheses: i.e., '(\d+)' will capture a sub-group of 1 or more digits [0-9].

Here are some examples of xp_regex_search:

This example returns the rows 1,1,'George' and 2,1,'Harold'. Notice we used the case-insensitive regex searching option:

EXEC dbo.xp_regex_search '[A-Z]+', 'George,Harold', 'I+'

This example returns the rows 1,1,'1/2/2005' and 2,1,'2/3/2006':

EXEC dbo.xp_regex_search '(\d+)/(\d+)/(\d+)', '1/2/2005,2/3/2006', NULL

This example is the same as above, but with sub-group captures turned on. This will return 1,1,'1/2/2005'; 1,2,'1'; 1,3,'3'; 1,4,'2005'; 2,1,'2/3/2006'; 2,2,'2'; 2,3,'3'; and 2,4,'2006'. Subgroups can be returned here because we specified the Perl regex grouping operator '()' in the regular expression. So each set of one or more contiguous digits is a sub-group capture.

EXEC dbo.xp_regex_search '(\d+)/(\d+)/(\d+)', '1/2/2005,2/3/2006', 'G+'

Figure 2. xp_regex_search sample with group capture on

xp_regex_split @searchexpression, @searchstring, @options

This extended procedure is similar to xp_regex_search. Instead of returning matches to the search expression, however, this function uses the search expression to split the search string into tokens. The parameters are similar to xp_regex_search:

  • @searchexpression is a valid Perl-syntax regular expression,
  • @searchstring is a string to be searched/split
  • @options are the user options. This extended procedure supports only the 'I+', case-insensitivity on, option.

The xp_regex_split extended procedure returns a table with two columns: TokenNum and TokenText.

  • TokenNum is the number of the 'token' - the text returned by the split, and
  • TokenText is the text returned.

Results are returned by xp_regex_split in increasing order from left to right, as with the xp_regex_search procedure. Here are a few examples:

Splits based on white space. Returns 1,'One'; 2,'Two'; and 3,'Three':

EXEC dbo.xp_regex_split '\s+', 'One Two Three', NULL

This split is a little more complex. It uses Perl regex look-aheads to split a comma-delimited (CSV) format string. The result is 1,"Make"; 2,"A"; 3,"Wish".

EXEC dbo.xp_regex_split '\s*,\s*(?=(?:[^\"]*\"[^\"]*\")*(?![^\"]*\"))\s*', '"Make","A","Wish"', 'I+'

Figure 3. fn_regex_split comma-delimited (CSV) data sample

fn_regex_replace (@searchexpression, @searchstring, @replacement, @options)

The fn_regex_replace function replaces all search expression matches in the search string with the replacement string. The parameters are:

  • @searchexpression is a valid Perl-syntax regular expression,
  • @searchstring is a string to be searched
  • @replacement is the replacement string
  • @options are the user options. This extended procedure supports only the 'I+', case-insensitivity on, option.

By default the regex replace operation is case-sensitive. Here are a couple of examples of fn_regex_replace usage.

In this first simple example, all white space in the search string is replaced with periods:

SELECT dbo.fn_regex_replace ('\s', 'Hello There How Are You?', '.', 'I+')

In this next example, a list of hexadecimal numbers is prefixed with '0x'. Notice the use of the Perl grouping parentheses and the $1 group specifier in the @replacement string:

SELECT dbo.fn_regex_replace ('([0-9A-F]{2})', '01 1a 4f ff 36 92 87', '0x$1', 'I+')

Perl Regex Syntax

Regular Expressions are as much an art as they are a science, but they are built on a very specific syntax. Regular expressions are built up by combining single characters and metacharacters known collectively as 'atoms'. Here's a quick overview of some of the most common 'atoms' in Perl regular expression syntax:

A B Cliteral characters like 'A' are matched exactly. Lowercase characters are matched exactly as well, unless you are performing a case-insensitive search. Numbers (1, 2, 3...) are likewise matched exactly.
\The backslash character is used to denote special meta-characters. Use \\ to denote an exact match of a backslash.
\dMatches a digit (0 - 9) character.
\DMatches a non-digit character.
\wMatches a 'word' character, which includes letters A-Z, a-z, 0-9 and the underscore character ('_').
\WMatches a non-word character.
\sMatches a whitespace character.
\SMatches a non-whitespace character.
\nMatches a new-line character.
\xffMatches a hexadecimal character (0xff in this instance).
.Matches any character. Use \. to match a period in your search string.

In addition you may also use repetition characters. Repetition characters normally follow a literal character, metacharacter or group of characters:

^Matches from the beginning of the search string.
$Matches at the end of the search string.
*Matches the preceding atom or group 0 or more times.
+Matches the preceding atom or group 1 or more times.
?Matches the preceding atom or group 1 or 0 times.
{n}Matches the preceding atom or group exactly n times.
{n,}Matches the preceding atom or group at least n times.
{n, m}Matches the preceding atom or group at least n times, but no more than m times.
[AEIOU]Character classes are placed in brackets. The example [AEIOU] matches any of the characters 'A', 'E', 'I', 'O', or 'U'.
[A-Z]Character class ranges can be specified by using a dash between two characters.
[^AEIOU]A caret (^) at the beginning of a bracket indicates that any character other than the ones specified are a match. In this instance, any character but 'A', 'E', 'I', 'O' or 'U' is considered a good match.

You can also group atoms together using parentheses. We did this in some of the examples above. Here are the specifics:

( )Parentheses indicate that anything between them should be matched as a group.
(BEN|JERRY)The 'or' ('|') operator indicates either of the items on the left or right of it is a match. In this example, 'BEN' or 'JERRY' is a good match.

This is just a brief overview of some of the options available. Many very long books have been written on specific regular expression details. An excellent resource for beginners is Jukka Korpela's Regular Expressions in Perl. On this site, Jukka gives descriptions of the basic Perl regex operations, and examples.

Another great resource is Jeffrey Friedl's book Mastering Regular Expressions or Nathan A. Good's Regular Expression Recipes: A Problem-Solution Approach.

DBA Toolkit (Regular Expressions) Quick Reference

User-Defined FunctionsDescription
fn_regex_match (@searchexpression, @searchstring, @options)Returns 'Y' if the search string is a match for the search expression, 'N' otherwise.
fn_regex_replace (@searchexpression, @searchstring, @replacement, @options)Returns the search string with all occurrences of the search expression replaced with the specified replacement string.
Extended ProceduresDescription
xp_regex_match @searchexpression, @searchstring, @options, @ret OUTPUTReturns 'Y' if the search string is a match for the search expression, 'N' otherwise.
xp_regex_search @searchexpression, @searchstring, @optionsReturns all matches of the search expression from the search string.
xp_regex_split @searchexpression, @searchstring, @optionsUses the search expression to split the search string, and returns all tokens produced by the split.
xp_regex_replace @searchexpression, @searchstring, @replacement, @ret OUTPUT, @optionsReplaces all search expression matches in the search string with the replacement string.


This is the end of Part Two of this series on the DBA Toolkit. In this article we talked about regular expression tools included in the toolkit. In Part Three we will discuss phonetic matching tools.

Download the DBA Toolkit Here

Total article views: 20865 | Views in the last 30 days: 14
Related Articles

Regular Expression or RegEx in SQL Server 2005

How do we configure or use "Regular Expression" and RegEx in SQL Server 2005


Applied SQL: Find and replace using regular expressions in SSMS

Regular expressions are searches on steroids, the wildcats of the wildcard world*.  Even if you  nev...


RegEx Error SQL IS 2005

Source: Verify Data and Load Stage Table Regex Description: No match for regular expression; com...


Regular Expression

Regular Expression in sql express 2005


Learning Regular Expressions

I’m a regular watcher of the Midnight DBA Webshow and one day they mentioned regular expressions. N...

advanced querying