SQL select string that doesn't contain Y but contains X

sql regex select sql-like
Katzumi picture Katzumi · Oct 5, 2011 · Viewed 39k times · Source

I'm attempting to select all of the instances where this database field has a string but does not contain a specific value.

Example: I'm trying to select every instance of "red chair" where "red chair" is not proceeded by "big". If I were to run that query in the following table, I'd only get the row with the ID of 2 back:

+----------+--------------+---------+
| ID       |        content         | 
+----------+------------------------+
| 1        | The big red chair      |
| 2        | I have a red chair     |
| 3        | I have a big chair     |
+----------+------------------------+

Thanks in advance!

Answer

kand picture kand · Oct 5, 2011

You can use:

LIKE '%red chair%' AND NOT LIKE '%big%'

edit: Fixed LIKE matching strings

Related questions

Java - escape string to prevent SQL injection

I'm trying to put some anti sql injection in place in java and am finding it very difficult to work with the the "replaceAll" string function. Ultimately I need a function that will convert any existing \ to \\, any " to \", any …

Read More
How to regex in a MySQL query

I have a simple task where I need to search a record starting with string characters and a single digit after them. What I'm trying is this SELECT trecord FROM `tbl` WHERE (trecord LIKE 'ALA[d]%') And SELECT trecord …

Read More
Regular expressions inside SQL Server

I have stored values in my database that look like 5XXXXXX, where X can be any digit. In other words, I need to match incoming SQL query strings like 5349878. Does anyone have an idea how to do it? I have …

Read More