in Hacking

MySQL Concat method can make string searching case-sensitive

I have the following table-structure in the database.
An integer column for the number and a varchar as the prefix.

billings:

billing_prefix VARCHAR(200)
billing_nr     INTEGER NOT NULL
.. and more field, but unimportant for this example

The following record is in the database:

billing_prefix: FatOrders-
billing_nr    : 42

The complete billing number is represented to the client as "FatOrders-42".
Now I have a application that enables the user to search the list with a string. (on several fields)
A client searching for 'fat' results in the following query:

SELECT * FROM billings WHERE CONCAT( billing_prefix, billing_nr ) LIKE '%fat%'

No results..
This is not the behaviour I need :P

Mysql tells me this:

If all arguments are nonbinary strings, the result is a nonbinary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent binary string form

An simple solution is to modify the query to this one:

SELECT * FROM billings WHERE CONCAT( billing_prefix, CAST( billing_nr AS char) ) LIKE '%fat%'

Comments are closed.

  • Related Content by Tag