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.
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.