MySQL Query to extract only a specific value that not in a string-CodePudding

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,%'
            )

CodePudding user response：

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

FIND_IN_SET

REPLACE

PREPARED STATEMENTS

UPDATE

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.

CodePudding user response：

In MYSQL we can use regular expressions. Here we use the expression (.*[^0-9])?12([^0-9].*)? which means that the string must contain 12 and, if the are characters before or after it, the character touching it must not be a number [^0-9].

create table base(
val varchar(100));
insert into base values
('55,44,1265,13,12'),
('12,55,1265,44,13'), 
( 'A45, or 45A or '),
(' ABCDE52JQST 1265'),
('12 13 15 or a'),
('123,212, '),
('haza12');

select val from base
where val regexp
 '(.*[^0-9])?12([^0-9].*)?'

| val               |
| :---------------- |
| 55,44,1265,13,12  |
| 12,55,1265,44,13  |
|  ABCDE52JQST 1265 |
| 12 13 15 or a     |
| 123,212,          |
| haza12            |

db<>fiddle here