Oleg Ushakov Oleg Ushakov - 19 days ago 7
MySQL Question

MySQL return empty result when use Like "ABC%" construction

I use
MySQL 5.1.4, with utf8_general_ci

I have a correct answer when I use query

SELECT code, searchString FROM places WHERE searchString LIKE "%45%" LIMIT 15


or

SELECT code, searchString FROM places WHERE searchString LIKE "%km" LIMIT 15


But when I use query

SELECT code, searchString FROM places WHERE searchString LIKE "45%" LIMIT 15


I have response from mysql: MySQL returned an empty result set.

My table contain values with leading simbols "45xxx"

"43014000" "745 km"
"50022000" "Base N 45"
"54008000" "45 km"


How I can fix that?

Upd.: p.s. The problem initially occurred due to incorrect import from an external file

Answer

It is possible that there is some leading white space in that column that is not shown when you run a select query, I have seen some problems like this before.

As a work around, you can TRIM() the column, which will remove leading white space, and then do your search:

SELECT code, searchString
FROM myTable
WHERE TRIM(searchString) LIKE '45%'
LIMIT 15;