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?

Eric · Accepted Answer · 2009-08-25 09:08:03Z

11

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>.

edited Aug 25, 2009 at 9:08

answered Aug 25, 2009 at 4:38

Eric

95.7k13 gold badges117 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bill Karwin Over a year ago

You're using MS SQL Server string concatenation syntax, which MySQL doesn't support. Use this syntax instead: CONCAT('[[:<:]]', a.company, '[[:>:]]')

developer Over a year ago

Thanks Eric and thanks Bill ! well honestly im learning a lot with the help of you all. Thanks again to stackoverflow and all its members :-)

Collectives™ on Stack Overflow

Using Columns in a RegExp in MySQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related