Search code examples
mysqlsql-likeconcatenation

MySQL wildcard matching with concat

I have this code which returns the result I want:

SELECT CONCAT('#', id, ' ', firstname, ' ', lastname, ' (', company, ')') AS result FROM client_detail

Here are the results:

#1 James Hadley (OpenBill)
#2 Lucinda Hadley (Make a Squish)

But when trying to search in the same string when using LIKE (I want to search for a variable string anywhere inside the above results), I get no output and no errors:

SELECT CONCAT('#', id, ' ', firstname, ' ', lastname, ' (', company, ')') AS result FROM client_detail WHERE CONCAT('#', id, ' ', firstname, ' ', lastname, ' (', company, ')') LIKE '%jam%' ORDER BY id ASC

I am right to use the LIKE operand, or is there a better/correct way of searching inside the result?

Solution

  • MySQL's CONCAT statement will use the system default for collations. If your table is defined as UTF-8 but your system is latin1 then your LIKE statement may choose a case-sensitive collation.

    Force a case-insensitive match using LOWER (Using HAVING to take advantage of your alias result):

    SELECT CONCAT('#',id,' ',firstname,' ',lastname,' (', company, ')') AS result
  FROM client_detail 
HAVING LOWER(result) LIKE '%jam%'    
 ORDER BY id ASC

    Or use a COLLATION (will depend on your system)

    ...
HAVING result LIKE '%jam%' COLLATE latin1_general_ci
...