Using Columns in a RegExp in MySQL

Question

I'm using the following query with regexp:

SELECT a.id, a.company, a.name, b.title, b.description, b.t_id
FROM a, b
WHERE ( b.title
REGEXP "[[:<:]]a.company[[:>:]]" OR b.description
REGEXP "[[:<:]]a.company[[:>:]]" OR b.title
REGEXP "[[:<:]]a.name[[:>:]]"  OR b.description
REGEXP "[[:<:]]a.name[[:>:]]" ) AND a.company !=  '' AND a.name !=  ''

But, this query is not giving any result nor its giving any syntax error.

When I replace a.company or a.name with any of the company name then this query runs fine. Why doesn't this query work with the column names?

Answer 1

You're searching for the literal string a.company, and not the column. Try this:

SELECT a.id, a.company, a.name, b.title, b.description, b.t_id
FROM a, b
WHERE 
    ( 
        b.title REGEXP concat('[[:<:]]', a.company, '[[:>:]]') 
        OR b.description REGEXP concat('[[:<:]]', a.company, '[[:>:]]') 
        OR b.title REGEXP concat('[[:<:]]', a.name, '[[:>:]]')
        OR b.description REGEXP concat('[[:<:]]', a.name, '[[:>:]]')
    ) 
    AND a.company !=  '' AND a.name !=  ''

This provides the regexp with the value of the column, not the string 'a.company'. Since my guess is that you want to compare the column value (and not the column name), you will need to concatenate your regexp together.

You can test this with this query:

select
    'My col: a.company' as Test1,
    'My col: ' + a.company as Test2
from
    a

Here, Test1 will always be the value My col: a.company, while Test2 will be My col: <company col value here>.