MYSQL concat 结果在 where 条件下

Question

我需要将 concat 函数的输出与 where 子句和 like 子句相匹配。以下是我的查询

SELECT t.id, 
concat(trim(t.address1), ', ',t.zip, ' ',trim(t.city), ', ',c.countryName ) AS fullAddress 
FROM `User` `t` 
INNER JOIN Country c ON t.countryCode = c.countryCode

我需要找到匹配的结果

27 Avenue Pasteur, 14390 Cabourg, France

和

14390 Cabourg, France

..

SELECT  t.id,
        concat(trim(t.address1), ', ',t.zip, ' ',trim(t.city),
                ', ',c.countryName 
              ) AS fullAddress
    FROM  `User` `t`
    INNER JOIN  Country c ON t.countryCode = c.countryCode
    WHERE  (((address1 IS NOT NULL
                  AND  zip IS NOT NULL
                  AND  city IS NOT NULL
                  AND  t.countryCode IS NOT NULL)
                  AND  (concat( t.address1, ', ', t.zip, ' ', t.city, ', ', c.countryName )
                         regexp '^[0-9]+,? [^,]+, [0-9]+,? [^,]+, [a-zA-Z]+$'))
                  AND  (concat(' ',trim(t.address1), ',',t.zip,' ', trim(t.city),
                                ', ', c.countryName) like '%27 Avenue Pasteur, 143%')
           )

Answer 1

查询中最后一个 t.zip 之前的“,”中缺少一个空格。

应该是:

SELECT t.id, concat(trim(t.address1), ', ',t.zip, ' ',trim(t.city), ', ',c.countryName ) AS fullAddress FROM `User` `t` INNER JOIN Country c ON t.countryCode = c.countryCode WHERE (((address1 IS NOT NULL AND zip IS NOT NULL AND city IS NOT NULL AND t.countryCode IS NOT NULL) AND (concat( t.address1, ', ', t.zip, ' ', t.city, ', ', c.countryName ) regexp '^[0-9]+,? [^,]+, [0-9]+,? [^,]+, [a-zA-Z]+$')) AND (concat(' ',trim(t.address1), ', ',t.zip,' ', trim(t.city), ', ', c.countryName) like '%27 Avenue Pasteur, 143%'))