Q

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?

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

Dig Deeper

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataCenter

SearchServerVirtualization

SearchCloudComputing

SearchEnterpriseDesktop

Close