Ask the Expert

Eliminate rows with a certain string value in MySQL

I'm new to database management systems and SQL. I'm currently working with MySQL and interfacing with it via driver MySQL ODBC 3.51. This is a straightforward desktop project using the database as flat files, no relational database, just a small number of tables.

If I issue SELECT * FROM table_name WHERE field_name1 !=0 ORDER BY field_name2, everything performs as expected when field_name1 is an integer. That is, rows that have a zero value in field_name1 are not returned from the table. This SQL statement functions as expected as long as field_name1 is an integer.

When field_name1 is VARCHAR (XX) then all of the table is returned and is properly sorted by the integer values in field_name2. I expected no row returned where field_name1 is a match for the string. Tried <> for not equal. I'm putting single quotes around the string. Tried using NOT LIKE instead of WHERE. Tried other syntax with no sucess.

I want to order data return by one field and eliminate all rows that have a certain string value in another field. This works OK for integers but not for string values. Can you help?

Requires Free Membership to View

It sounds like you are trying to eliminate rows that contain a certain string value. In this case the NOT LIKE syntax will probably serve you best:

mysql> USE test;
Database changed
mysql> CREATE TABLE unlikeTest(
     -> myval VARCHAR(15)
     -> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO unlikeTest VALUES('This and that'),('Like That'),('Watch This');
Query OK, 3 rows affected, 1 warning (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 1

mysql> SELECT * FROM unlikeTest WHERE myval NOT LIKE ('%this%');
+-----------------+
| myval           |
+-----------------+
| Like That       |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM unlikeTest WHERE myval NOT LIKE ('%that%');
+------------+
| myval      |
+------------+
| Watch This |
+------------+
1 row in set (0.00 sec)
See the String Comparison Functions section of the MySQL Reference Manual for more information.

This was first published in June 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: