CODEWITHSUNDEEP
sqlconcatenationsql-like
kokoseq
kokoseq

Reputation: 209

SQL query with CONCAT LIKE AND

I have an query like this:

SELECT * FROM `tbl_shop` 
WHERE
  (LOWER (CONCAT(address, name)) LIKE (LOWER ('%boston%')) AND
   LOWER (CONCAT(address, name)) LIKE (LOWER('%smoke%')));

My question is simple - is there any way how to write this code without need to repeat CONCAT(address, name) part?

I tried

SELECT * FROM `tbl_shop`
WHERE 
  (LOWER (CONCAT(address, name)) LIKE (LOWER ('%boston%')) AND (LOWER('%smoke%')));

But this was not giving any results. I simply need all results, which contain both words. I can not use full text, because I am using inno db and want to keep it.

Thanks in advance.

Upvotes: 4

Views: 9452

Answers (1)

a1ex07
a1ex07

Reputation: 37364

You can do

SELECT b.* FROM
(
   SELECT a.*, LOWER(CONCAT(a.address, a.name)) AS field_to_check
)b 
WHERE b.field_to_check LIKE (LOWER ('%boston%')) 
 AND  b.field_to_check LIKE (LOWER('%smoke%'));

However, it's just syntax sugar, and it shouldn't be a difference in performance.

Upvotes: 3

Related Questions