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