I have a data base column (comment) with almost any combination of alpha characters, numbers or alphanumerics can appear. In one example it looks like this this 55,44,1265,13,12 in another it might be 12,55,1265,44,13 there also might be A45, or 45A or ABCDE52JQST or 12 13 15 or anything really.
I need a MySQL query to extract any row based on a search entered by the user. For example the user might want to identify all rows with 12 in it. Not 1265 not 12A just 12, the only acceptable values other than 12 would be ,12 or 12, (comma12 or 12comma) or spaces before or after the number ( 12 or 12 ). But not 1265.
My current where clause looks like below and while it sometimes works, it sometimes doesn’t and even if it did work every time, it’s ugly. How else could I write the where clause to do what I need, could I use perl expressions in some way? Please give an example.
WHERE netID = $netID AND ( comment LIKE '%$findit' OR comment LIKE '$findit%' OR comment = '$findit' OR comment = ',$findit' OR comment = '$findit,' OR comment = ',$findit,' OR comment LIKE '% $findit ' OR comment LIKE ' $findit %' OR comment LIKE '%$findit,' OR comment LIKE ',$findit%' OR comment LIKE '%,$findit' OR comment LIKE '$findit,%' OR comment LIKE '%,$findit ' OR comment LIKE ' $findit,%' OR comment LIKE '% $findit' OR comment LIKE '$findit %' OR comment LIKE '%$findit ' OR comment LIKE ' $findit%' OR comment LIKE '%,$findit,%' )
You seem to be describing set data with either commas or spaces as delimiters. Instead of going into the typical lecture on storage of serialised data, I will just assume there is nothing you can do about it.
Obviously, passing strings directly into your SQL like this poses a SQLi risk and you should be using parameterised queries or at least some robust sanitisation and validation of the user input.
So, treat the set as a set by turning the space separated lists into comma separated lists –
WHERE FIND_IN_SET('$findit', REPLACE(`comment`, ' ', ',')) > 0
This db<>fiddle works for the examples you have provided. Please provide a specific example of value(s) for which this does not work. Using REGEXP as suggested by Kendle/Bill Karwin may be the better route to go down.
Answered By – nnichols
Answer Checked By – Mary Flores (BugsFixing Volunteer)