Where condition do not work in MySQL

Question

I have a query in mysql.

 SELECT * FROM tblName WHERE colName = "name";

However, no records are selected. I checked the table and there are fields that matched the condition. By the way, the records are inserted by a stored procedure. When I tried to edit some records (colName column), the edited records are selected. colName has a datatype varchar

Looks like white space issue, run this first `update tablename set colName = trim(colName)` then try your select query and see if it does anything. — Abhik Chakraborty, Jun 03 '15 at 07:04
Can you show some sample data from the table, including records that match? — Tim Biegeleisen, Jun 03 '15 at 07:04
When I tried to edit the field, there is no other spaces. Does this still have white spaces? — san-san, Jun 03 '15 at 07:08
@ Abhik Chakraborty: I tried to remove the whitespaces in my table by updating that column. I used SET colName = Trim(colName) However it says that no rows affected — san-san, Jun 03 '15 at 07:15

John · Answer 1 · 2015-06-03T07:22:29.507

0

Try this to see if you will get any results and check if you have issues with white spaces or something...

SELECT *,LENGTH(colName) FROM tblName WHERE colName LIKE "%name%";

There is also the possibility ( i have this issue my self on application i use) that characters that match your language are 'translated' in you data e.g for Greek :
GEORGE seems the same with GΕΟRGΕ But there are NOT when you convert them to lower case

gεοrgε  <> george

edited Jun 03 '15 at 07:22

answered Jun 03 '15 at 07:14

John

974
8
16

Hi John, yes i got some columns that have my expected length. I've tried to update the columns by SET colName = TRIM(colName) however it always says 0 affected rows – san-san Jun 03 '15 at 07:22
is there any chance for the problem i mentioned ? do you have multi language data..the best thing to allow quick and accurate response is to provide a SQLFiddle. – John Jun 03 '15 at 07:23
im sorry but what do you mean multi language? I have tried to selecting Lower(colName), it was translated well – san-san Jun 03 '15 at 07:26
is English your native language ? also take note that mySQL is case sensitive – John Jun 03 '15 at 07:33
yeah. english. in my select query, it says that it has 4 characters however what is visible is only 3. I tried to update it by the use of TRIM however it did not work. it always says no rows affected. – san-san Jun 03 '15 at 07:36
export some data to SQLFiddle...this will help solving it – John Jun 03 '15 at 07:39
1

@san-san try using HEX() function on the text - it will show you byte values for those invisible characters so you will know what they are – jkavalik Jun 03 '15 at 07:56

score 0 · Answer 2 · answered Jun 03 '15 at 07:28

0

I'm not sure

but please try to use single quote instead double quote

Like this

SELECT * FROM tblName WHERE colName = 'name';

answered Jun 03 '15 at 07:28

Chanom First

1,136
1
11
25

score 0 · Answer 3 · edited May 23 '17 at 11:51

0

This thread helped me a lot. I haven't noticed that i have some other whitespace in my field.

How to remove leading and trailing whitespace in a MySQL field?

edited May 23 '17 at 11:51

Community

1
1

answered Jun 03 '15 at 09:41

san-san

64
8

Where condition do not work in MySQL

3 Answers3