MySQL Like In()

MySQL LIKE IN()?

A REGEXP might be more efficient, but you'd have to benchmark it to be sure, e.g.

SELECT * from fiberbox where field REGEXP '1740|1938|1940'; 

MySQL like with IN clause

That is bad database design. But you can use REGEXP instead:

WHERE permission.PERMISSION_ID REGEXP '_(123|456|789)_'

It is still bad database design.

MySQL IN with LIKE

You can use a number of LIKE expressions:

SELECT fields 
  FROM table 
  WHERE age = "50" 
        AND (
             name LIKE "2010-09-17%" 
             OR name LIKE "2010-09-16%"
            );

or you can use a regex:

SELECT fields 
  FROM table 
 WHERE age = "50" 
       AND name REGEXP "2010-09-17.*|2010-09-16.*";

or, cleverly

SELECT fields 
  FROM table 
 WHERE age = "50" 
       AND name REGEXP "2010-09-1(6|7).*";

mysql SELECT where like and like

In MySQL, you can use find_in_set():

select * 
from products 
where find_in_set('3', papers) and find_in_set('4', papers) and find_in_set('5', papers)

Note that your question suggests a flawed database design. You should not be storing multiple integer values in a single string column. Instead, you should have each integer value in a separate row, probably in another table, that would refer to the main table through a foreign key constraint. Recommended reading: Is storing a delimited list in a database column really that bad?.

MySQL LIKE IN() and using AND

AND is a bit more difficult to do with regexes, and it doesn't seem to be possible with MySQL's limited regular expression syntax. If you are using the myISAM engine or MySQL 5.6, you can use the MATCH() function to get most of what you want:

SELECT title FROM titles WHERE MATCH (title)
AGAINST ('+red* +green* +yellow*' IN BOOLEAN MODE);

However, I would recommend sticking to your simple chaining of ANDs. It's less fragile, more powerful, and easier to code.

Mysql LIKE clause and separate words in a field

You could use a REGEXP to match any of the words in your search string:

select *
from tbl
where
  title REGEXP CONCAT('[[:<:]](', REPLACE('Acme burger', ' ', '|'), ')[[:>:]]')

Please notice that this will not be very efficient. See fiddle here.

If you need to match every word in your string, you could use a query like this:

select *
from tbl
where
  title REGEXP CONCAT('[[:<:]]', REPLACE('Acme burger', ' ', '[[:>:]].*[[:<:]]'), '[[:>:]]')

Fiddle here. But words have to be in the correct order (es. 'Acme burger' will match, 'burger Acme' won't). There's a REGEXP to match every word in any order, but it is not supported by MySql, unless you install an UDF that supports Perl regexp.

MySQL LIKE Operator with Special Characters Confusion

Maybe this help you understand the usage of escape chars in mySQL

https://stackoverflow.com/a/27061961/634698

MySQL - using LIKE operator with UNHEX() function

Your problem is that you have put the wildcard characters into the call to UNHEX instead of concatenating them outside it, and that is making UNHEX return NULL, which will always cause LIKE to return false.

Change

LIKE UNHEX('%{$name_2}%')

to

LIKE  CONCAT('%', UNHEX('{$name_2}'), '%')

and your code will work as expected. Alternatively, you could do as @Akina suggested in the comments, and replace the % signs in the call to UNHEX with their hex representation (25) and replace your code with

LIKE UNHEX('25{$name_2}25')

MySQL Like multiple values

The (a,b,c) list only works with in. For like, you have to use or:

WHERE interests LIKE '%sports%' OR interests LIKE '%pub%'

MySQL LIKE operator isn't working as expected for pattern matching

LIKE and REGEXP do not follow the same rules for pattern matching. REGEXP supports regular expressions, while LIKE understands wildcards '_' (a single character) and '%' (an arbitrary number of characters, including no characters at all).

This is well explained in the pattern matching documentation:

SQL pattern matching enables you to use _ to match any single character and % to match an arbitrary number of characters (including zero characters). In MySQL, SQL patterns are case-insensitive by default. Some examples are shown here. Do not use = or <> when you use SQL patterns. Use the LIKE or NOT LIKE comparison operators instead.

[...]

The other type of pattern matching provided by MySQL uses extended regular expressions. When you test for a match for this type of pattern, use the REGEXP_LIKE() function (or the REGEXP or RLIKE operators, which are synonyms for REGEXP_LIKE()).

Side note: both pattern matching methods are case-insensitive by default (unless you use some special collations), so you regex could possibly be simplified to not use the lower() function:

city like regexp '^[aeiou]'

If you were to express this with like, that would be:

city like 'a%' 
or city like 'e%'
or city like 'i%'
or city like 'o%'
or city like 'u%'

---

Related Topics