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

Question

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

user1191247 · Accepted Answer · 2022-03-13 11:26:47Z

2

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.

edited Mar 13, 2022 at 11:26

answered Mar 13, 2022 at 1:13

user1191247

13k2 gold badges25 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Keith D Kaiser Over a year ago

I love the simplicity of the answer. It works for 1 or 2 digit searches but doesn't for the 1296 example. The query returns a empty set. But some of the fields with the word 'tactical' in them are all returned when I search for tactical. Thoughts? Oh and you're right I have no choice but to work with it the way it is, I'm very careful.

Keith D Kaiser Over a year ago

I had to adjust my query a bit and exclude some obvious incorrect results but in the long run this was the answer that seems to work the best. I'll upgrade my response to 'solved' after I vet a few more issues.

score 0 · Accepted Answer · 2022-03-13 19:39:24Z

0

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 one touching it must not be a number.
We use a negative lookback and a negative look forward.

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'),
(1265),
(1212),
(12),
(2012);

select val from base
where val regexp
    '(?<![0-9])12(?![0-9])'

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

db<>fiddle here

edited Mar 13, 2022 at 19:39

answered Mar 13, 2022 at 1:33

user18098820

4 Comments

Bill Karwin Over a year ago

Microsoft SQL Server does have that feature, but MySQL — the brand of database tagged in this question — does not. MySQL does have a more complete REGEXP comparison operator.

user18098820 Over a year ago

Thank you Bill Karwin - I was sure that I'd seen that it was MSSQL. I've modified my answer.

Keith D Kaiser Over a year ago

I added a new row to the @kendle example of ('1265') adjusting commas and semicolons as needed and then did a search for 12, but 1265 still comes up. I'm sure if I new regular expressions better I could fix this myself but try as I might I just mess it up more. My query looks like this; {SELECT ID, netID, callsign, timestamp, REPLACE(comment, ' ', ',') comment_set FROM TimeLog WHERE netID = $netID AND FIND_IN_SET('$findit', REPLACE('comment', ' ', ',')) > 0 ORDER by timestamp DESC}

user18098820 Over a year ago

I've modified the pattern. It seems that mySQL's implementation of regex is more limited than the tester I was using. Please see new DBfiddle link. Thank you

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related