在MySQL中的RegExp中使用列 [英] Using Columns in a RegExp in MySQL

问题描述

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

当我用任何公司名称替换​​a.company或a.name时，此查询运行良好.为什么此查询不能与列名一起使用?

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?

推荐答案

您要搜索的是文字字符串a.company，而不是该列.试试这个:

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 !=  ''

这为regexp提供列的值，而不是字符串'a.company'.由于我的猜测是您要比较列值(而不是列名)，因此需要将regexp连接在一起.

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

在这里，Test1将始终为值My col: a.company，而Test2将始终为My col: <company col value here>.

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

这篇关于在MySQL中的RegExp中使用列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！