MySQL query - condition (comparing strings) does not work

Question

My table has only got 1 Column 'Name' with 100 unique entries.

I need to find out if a given Value exists in that table.

I am using:

SELECT 1 FROM `tbl_names` WHERE `Name` = "Lisa"

MySQL returns an empty result, so no 0 for not found and no 1 for found, even though the given name is an entry in that table.

What am I missing here?

Are you sure about characters? Have you tried this one: SELECT 1 FROM "tbl_names" WHERE "Name" = 'Lisa' — stepio
– stepio, Commented Nov 28, 2017 at 12:50
So you are trying to return the result with Name = 'Lisa', is that correct? — Cookie Monster
– Cookie Monster, Commented Nov 28, 2017 at 13:13
The TRIM did it. Apparently each value had an "\r" at the end that was not visible from the phpmyadmin overview — Lisa Cleaver
– Lisa Cleaver, Commented Nov 28, 2017 at 13:17

Michał Zaborowski · Accepted Answer · 2017-11-28 12:50:49Z

1

select count(*) from tbl_name where name = 'Lisa' - will return count of entries with Lisa in the column. You can do as before with select 1, and calculate results - zero size means no occurance

answered Nov 28, 2017 at 12:50

Michał Zaborowski

4,4032 gold badges23 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Lisa Cleaver Over a year ago

This returns Count 0

Michał Zaborowski Over a year ago

Maybe try with this LOWER(name) = LOWER("Lisa") instead of simple comparison - if name is lowercased... Other option is to call select '>>' || name || '<<' from tbl_names where Name like '%Lisa%' to see if there are some spaces in the name...

Lisa Cleaver Over a year ago

None of the names are lowercased and there are no spaces or anything in the Values.

Lisa Cleaver Over a year ago

Nevermind I missed "\r" that I could not see without actually clicking each of the values. I trim'ed the whole DB and now it seems to work. Thanks for the help. Much appreciate it

Gordon Linoff · Accepted Answer · 2017-11-28 12:50:58Z

0

If you want to return as 0 or 1, I would suggest:

select (exists (select 1 from tbl_names where Name = 'Lisa')) as flag

This will not fix the problem that you describe -- but it will always return one row with a single column whose value is 0 or 1.

'Lisa' is not in the table. You might try where Name like '%Lisa%'.

answered Nov 28, 2017 at 12:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Lisa Cleaver Over a year ago

This returns Flag 0. If i try where Name like '%Lisa% I will get not only the name Lisa, but also other names containing Lisa

Gordon Linoff Over a year ago

@LisaCleaver . . . The point of this answer is to return one row with one column. Your data does not seem to have the name 'Lisa' -- at least as you have typed it in the query.

Collectives™ on Stack Overflow

MySQL query - condition (comparing strings) does not work

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related