MySQL Wildcards * and %

Match '%' sign when searching in MySQL database

The default escape character is \. So just prefix % with a \ as: \%:

The manual clearly says:

To test for literal instances of a
wild-card character, precede it by the
escape character. If you do not
specify the ESCAPE character, “\” is
assumed.

Search for % in Stack%Overflow:

mysql> select 'Stack%Overflow' like '%\%%';
+------------------------------+
| 'Stack%Overflow' like '%\%%' |
+------------------------------+
|                            1 |  <----- Found
+------------------------------+
1 row in set (0.00 sec)

Search for % in StackOverflow:

mysql> select 'StackOverflow' like '%\%%';
+-----------------------------+
| 'StackOverflow' like '%\%%' |
+-----------------------------+
|                           0 |   <----- Not Found
+-----------------------------+
1 row in set (0.00 sec)

EDIT:

If you are calling this query from PHP, you'll have to use \\. This is because even PHP uses \ as the escape character. So make MySQL get a \ you need to have \\ in PHP.

MySql Query in Python LIKE with Wildcard

Since you have two placeholders, you need to pass two parameters. The parameter for SOUNDEX() should be the name without the wildcards added.

You're also not using SOUNDEX() correctly. It doesn't return a lIKE pattern, it returns a code string. You need to call it on the column as well and compare them.

query_pre_company = """SELECT `name` 
                    FROM `seta`.`companies` 
                    WHERE SOUNDEX(`companies`.`name`) = SOUNDEX(%s) 
                    OR `companies`.`name` LIKE %s"""
name = sheet.cell(2,2).value #from an excel sheet
name_check='%'+name+'%'
rows_count=cursor.execute(query_pre_company,[name, name_check])

MySQL LIKE wildcard for table

Try:

SELECT * 
FROM feed, recipients 
WHERE feed.title LIKE concat('%', recipients.suburb, '%')
ORDER BY pubDate DESC

MySQL wildcard matching with concat

MySQL's CONCAT statement will use the system default for collations. If your table is defined as UTF-8 but your system is latin1 then your LIKE statement may choose a case-sensitive collation.

Force a case-insensitive match using LOWER
(Using HAVING to take advantage of your alias result):

SELECT CONCAT('#',id,' ',firstname,' ',lastname,' (', company, ')') AS result
  FROM client_detail 
HAVING LOWER(result) LIKE '%jam%'    
 ORDER BY id ASC

Or use a COLLATION (will depend on your system)

...
HAVING result LIKE '%jam%' COLLATE latin1_general_ci
...

MySQL String Comparison with Wildcards

Use a regular expression:

WHERE Description RLIKE '[[:<:]]apple[[:>:]]'

[[:<:]] matches the beginning of a word, [[:>:]] matches the end of a word.

See the documentation for all the regexp operators supported by MySQL

Add wildcard inside CONCAT in mysql

You probably want a full text index. But, if you want a match to any word and don't care about performance, you can use regular expressions:

select subscriberid, CONCAT(firstname, ' ', lastname) as fullname,
       address, city
from subscriber
where subscriberid regexp 'N29|Joh' or
      concat(firstname, ' ', lastname) like 'N29|Joh' or
      address like 'N29|Joh' or
      city like 'N29|Joh';

MySQL NOT LIKE sentence returning like results

Do not do a join; instead do a subquery select and use the "NOT EXISTS" check:

SELECT s.word
FROM srch s
WHERE NOT EXISTS (
  SELECT tag
  FROM tags t 
  WHERE s.word LIKE CONCAT(t.tag,'%')
)

Result:

// snakes

MySQL WHERE - wildcard to ignore option?

I would suggest making a SQL view to make your life a lot easier.

CREATE VIEW DocumentIdView AS
SELECT CONCAT('FM', DATE_FORMAT(year,'%y'), '-A', pk) AS docId, pk FROM table

Then in your PHP code, you can just do a

SELECT b.* FROM DocumentIdView a LEFT JOIN table b ON a.pk = b.pk WHERE a.docId LIKE 'FM15-A1%'

Obviously you could combine the two selects if you would rather not have a SQL view.

SELECT * FROM (SELECT CONCAT('FM', DATE_FORMAT(year,'%y'), '-A', pk) AS docId, pk FROM table) a LEFT JOIN table b ON a.pk = b.pk WHERE a.docId LIKE 'FM15-A1%'

If this doesn't perform well, I would recommend storing the document ID as a column in your table for fast lookup. Or if updating the table is not an option, create a materialized view for the query as @rlanvin suggests.

---

Related Topics