SQL (MySQL): Match first letter of any word in a string?

Question

(Note: This is for MySQL's SQL, not SQL Server.)

I have a database column with values like "abc def GHI JKL". I want to write a WHERE clause that includes a case-insensitive test for any word that begins with a specific letter. For example, that example would test true for the letters a,c,g,j because there's a 'word' beginning with each of those letters. The application is for a search that offers to find records that have only words beginning with the specified letter. Also note that there is not a fulltext index for this table.

Answer 1

Check the Pattern Matching and Regular Expressions sections of the MySQL Reference Manual.

Answer 2

Using REGEXP opearator:

SELECT * FROM `articles` WHERE `body` REGEXP '[[:<:]][acgj]'

It returns records where column body contains words starting with a,c,g or i (case insensitive)

Be aware though: this is not a very good idea if you expect any heavy load (not using index - it scans every row!)

Answer 3

You can use a LIKE operation. If your words are space-separated, append a space to the start of the string to give the first word a chance to match:

SELECT
  StringCol
FROM
  MyTable
WHERE
  ' ' + StringCol LIKE '% ' + MyLetterParam + '%'

Where MyLetterParam could be something like this:

'[acgj]'

To look for more than a space as a word separator, you can expand that technique. The following would treat TAB, CR, LF, space and NBSP as word separators.

WHERE
  ' ' + StringCol LIKE '%['+' '+CHAR(9)+CHAR(10)+CHAR(13)+CHAR(160)+'][acgj]%'

This approach has the nice touch of being standard SQL. Id would work unchanged across the major SQL dialects.