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%'
useful tip! thank you!