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?
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.
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.