CODEWITHSUNDEEP
mysqlsqldatabase
Asiri Liyana Arachchi
Asiri Liyana Arachchi

Reputation: 2673

Take string value in where clause - MySQL

I've created a simple mysql table like this

models column takes VARCHAR(30)

But when I execute this query

 SELECT *
 FROM `Vehicle_Duty_Chart`
 WHERE models = "SE3P"
 LIMIT 0 , 30

It returns this

 MySQL returned an empty result set (i.e. zero rows). (Query took 0.0004 sec)

What ever the 'models' column value I give result is the same. For queries like

 WHERE  ins =7000

returns desired outputs. I've no idea what I'm doing wrong here.

Thank you.

Upvotes: 2

Views: 23424

Answers (3)

TxRegex
TxRegex

Reputation: 2425

If you have some unwanted whitespace in your models field, you may want to trim it off for all records. Be sure you are ok losing leading and trailing whitespace on all your Vehicle_Duty_Chart models.

UPDATE `Vehicle_Duty_Chart` SET `models` = TRIM(`models`);

After this, your string comparison queries should work without needing to call trim for every query.

If models column contains more than one value separated by a comma or some other delimiter, you might want to change the query to something more like Nikul's answer.

Upvotes: 0

Nikul
Nikul

Reputation: 1025

Try with below query

SELECT * FROM Vehicle_Duty_Chart WHERE models LIKE  '%SE3P%' LIMIT 0 , 30

Upvotes: 0

Sunny Sharma
Sunny Sharma

Reputation: 4934

try this:

SELECT *
FROM `Vehicle_Duty_Chart`
WHERE TRIM(models) = "SE3P"
LIMIT 0 , 30

Upvotes: 5

Related Questions