MySQL search for a number in VARCHAR field

Question

Hi There,

I have a field in my database that contain comma separated values these values are numbers, and I am trying to do a search and count the number of times that a number appears in that column throughout the column,

$sql = "SELECT sector_id_csv, COUNT(sector_id_csv) as count FROM cv WHERE sector_id_csv LIKE '$sectorId'";

This seems slow and does not return any results, and I know the sector_id it is search exists in the table.

Answer 1

Don't you need to pad the value with the % wildcard for LIKE to work?

$sql = "SELECT sector_id_csv, COUNT(sector_id_csv) as count FROM cv WHERE sector_id_csv LIKE '%".$sectorId."%'";

At least that's my understanding from reading this article, your use of wildcards will depend on your desired condition.

Answer 2

Basically, this should work fine if you use % wildcards:

WHERE sector_id_csv LIKE '%$sectorId%'";

what tends to cause problems in this scenario, though, is the fact that a search for 50 will also find 501 502 503 and so on.

If you can rely on your comma separated list to have a trailing comma behind every entry, it would be more reliable to search for

50,

to catch that value only.

Answer 3

Actually the number could be at he beginning or the end too. So you need to do

WHERE sector_id_csv='$sectorId' OR sector_id_csv LIKE '%,$sectorId' OR sector_id_csv LIKE '$sectorId,%' OR sector_id_csv LIKE '%,$sectorId,%'

Answer 4

...but if your scema was normalized you wouldn't need to jump through these hoops - and it would run a lot faster.

C.

Answer 5

SELECT count(*) from TABLENAME where FIND_IN_SET('VALUE',FILDNAME)>0;

Others u can use instr, regexp.... It's advisable to have FILDNAME indexed.

Answer 6

WHERE CONCAT(',', sector_id_csv, ',') LIKE '%,$sectorId,%'

or

WHERE FIND_IN_SET('$sectorId', sector_id_csv);

This will ensure that your query returns only rows with sector id in given field. Provided that sector id-s in this field are comma separated.

Any query using LIKE or FIND_IN_SET will be slow as it cannot take advantage of indexes. Please consider putting all sector id-s in separate table.

Also for security reasons please remember to ensure that $sectorId is a number by casting it to int like that:

$sectorId = (int)$sectorId;

before using it in a query.