SQL Like statement

15 May 2009

Today I learnt a few new (well to me) SQL commands. The Like statement can do some basic regex type things. It supports character specifiers like this:

Column Like '%[a-z]Test[a-z]%'

This will find the word test as long as there is a letter at either end of the word in a block of text. You can also say Not a letter like so:

Column Like '%[^a-z]Test[^a-z]%'

This should find any words Test that do not have letters before or after them. Very useful for searching for a complete word in a block of text. However I could not get this to work (MSSQL Server 2005) so I ended up doing this:

Select     Columns
From TableName
Where    BlockOfText Like '%' + @word +'%'
  and    BlockOfText not like '%[a-z]' + @word + '[a-z]%'

Which works well for what I needed and is reasonably quick on a million records or so.

code, sql

« Conflicting Unrelated Options: Alps Trackpad vs Microsoft Mouse Finally, I have used a Model View Controller! »
comments powered by Disqus