Mysql LIKE clause find 1 and not 11

Question

First I want to say, I'm sorry if this question have be asked already (haven't been able to find it then).

My problem is that I've got a LIKE clause in mysql where I have to find some numbers in a comma seperated row. (Yes I know comma separated rows aint a good thing to use, but I'm not the one who made the database structure).

So I have the following:

  SELECT pfd.*, pu.username, pu.user_email 
  FROM phpbb_profile_fields_data pfd
  LEFT JOIN phpbb_users pu ON pfd.user_id=pu.user_id 
  WHERE pfd.pf_pf_produktionsteknik LIKE '%" . ($id) . "%' 
      AND pu.user_type!=1 AND pfd.pf_status=2";

But the problem is here that it returns where the LIKE equal 1 (as it should) but also 11 (which it should not).

I have tried using field_in_set wich I couldn't get to work, and I'm now lost at what to do about it.

First define what you want, then check what you can use and after that it's simple math - it can be done or cannot be done. Matching `1` and `11` is not "should not" behaviour, it's exactly how LIKE behaves and it's good. If you want specific results, define rules first. For example, why should it not return 11 if it returns 1? What rule have you set up to come to that conclusion? — N.B., Nov 06 '12 at 15:21

score 1 · Answer 1 · answered Nov 06 '12 at 15:43

The value searched for could be embraced by commas or followed by a comma when positioned at the list start or following a comma when positioned at the list end or it is the only value (no commas in the list)

WHERE (pfd.pf_pf_produktionsteknik LIKE '%," . ($id) . ",%' 
    OR pfd.pf_pf_produktionsteknik LIKE '"($id) . ",%'    
    OR pfd.pf_pf_produktionsteknik LIKE '%," .($id) . "'    
    OR pfd.pf_pf_produktionsteknik = '" .($id) . "'    )  AND ...

score 0 · Accepted Answer · answered Nov 06 '12 at 15:22

0

If your list is comma separated, can you look for number then comma, OR comma then number, or equalling number on its own. That should cover all cases including single number instances.

answered Nov 06 '12 at 15:22

A.M. D.

928
6
10

score 0 · Answer 3 · answered Nov 06 '12 at 15:27

You're dealing with data that's badly structured, so you need to have that addressed -- comma-delimited sets of numbers are not in 1nf, which is pretty basic.

Now that I've got that out of the way, you can try using a regexp instead of LIKE:

As SQL (note the value 1):

WHERE pfd.pf_pf_produktionsteknik REGEXP '\b1\b'

or as a PHP string:

..." WHERE pfd.pf_pf_produktionsteknik REGEXP '\b" . $id . "\b'"

...which will match 1 but not 11: \b is the regex for "word boundary", which means it requires the start of a string or punctuation or whitespace on either side of your value $id.

Alain Tiemblo · Answer 4 · 2012-11-06T15:46:10.693

The find_in_set (manual) mysql function is what you're looking for : it searches a value inside a comma separated value.

select find_in_set('1', 'a,b,1,c');

Gives this :

+-----------------------------+
| find_in_set('1', 'a,b,1,c') |
+-----------------------------+
|                           3 |
+-----------------------------+
1 row in set (1.10 sec)

If the function fails, it returns 0.

In your case, doing WHERE find_in_set('1', pfd.pf_pf_produktionsteknik) > 0 will give you what you need.

Mysql LIKE clause find 1 and not 11

4 Answers4