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 on Open source databases

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:

SearchDataCenter

SearchServerVirtualization

SearchCloudComputing

SearchEnterpriseDesktop

Close