Using Regular Expression in SQL Server

Who doesn’t love regular expressions?! If you don’t know what they are, you should take some time to learn. Regular expressions or from here on RegEx are a very powerful way to search/replace string within strings. That being said, almost any language supports RegEx and for a while I’ve been using them with SQL Server thanks to xp_pcre - Regular Expressions in T-SQL Here is an example use of two of the functions that come with the above extended sql server library:

declare @out varchar(8000)
declare @str varchar(500)

-- Create a string containing an ldap common name

set @str = lower(’LDAP://CN=APO Conference Room,OU=Locations,OU=APO,OU=Sites,DC=ica,DC=com’)

-- Strip everything from the string and replace with the ’ou’ that comes after ’ou=locations’

exec master.dbo.xp_pcre_replace @str, ’.*ou=locations,ou=(’w+).*’, ’$1’, @out out

-- Print the results
print @out

-- Create a string containing an ldap common name
set @str = lower(’LDAP://OU=SMD,OU=Sites,DC=ica,DC=com’)

-- Return 1 if there is a match of ’ou=’ followed by ’ou=sites’
select master.dbo.fn_pcre_match(@str, ’^ldap://ou=’w+,ou=sites’)

More information here: http://www.codeproject.com/database/xp_pcre.asp?df=100&forumid=16452&exp=0&select=1191266

Uppercase first Letter with CF RegEx

I needed to uppercase the first letter of a word before displaying it. Here is neat RegEx (CF specific but will probably work in Perl) that will do just that.

<cfset variable = rereplace(variable , '^(\w)(.*)', '\u\1\2') />

You can use the above when you need it done on the server. Here is the more elegant client side solution with CSS:

<span style="text-transform: capitalize;">boyan</span>

While I'm at it here is nice CF RegEx tester: http://www.cftopper.com/contentfiles/tools/regularExpTester.cfm

Why I Love Regular Expressions

If you haven't used Regular Expression, you haven't lived. It's as simple as that :-) I used the following RegEx to rename a bunch of files in the nifty XYplorer (more on that in another post):

(The)\s(.*)\.(.*)>$2, $1.$3

In the case of XYplorer, what that did was rename a bunch of files from "The Stupid File.rar" to "Stupid File, The.rar". Pretty neat...not like the good old days when you'd have to rename each file by hand.