CODEWITHSUNDEEP
mysqlsqldatabase
hshihab
hshihab

Reputation: 416

Simple MySql select statement returns 0 rows

This is rather silly, but i really don't know what's the problem here. I have a table 'profiles' which a column 'country', i filled this table from a tab separated value file and the table view seems to be fine.

Now when executing this query:

select * from profiles where country = 'Sweden'

Nothing gets returned, although the table has more than a hundred entry with country 'Sweden' and i double checked my spelling.

But when executing this query:

select * from profiles where country REGEXP 'Sweden'

it returns results as expected.

What's the cause of this ? and how do i fix it ?

Here is some data from the file used to fill the table:

0   f   Germany
1   f   Canada
2       Germany
3   m   Mexico
4   m   United States
5   m   United Kingdom
6   m   Finland

Thanks for reading

Upvotes: 0

Views: 2490

Answers (4)

Sadikhasan
Sadikhasan

Reputation: 18600

Try with TRIM function if any leading or trailing space in country field

select * from profiles where TRIM(country) = 'Sweden'

OR

select * from profiles where TRIM(country) LIKE 'Sweden'

OR

select * from profiles where country LIKE '%Sweden%'

OR check for carrige return value in field list

SELECT * FROM profiles WHERE country REGEXP "\r\n";

Upvotes: 0

Naren Falcao
Naren Falcao

Reputation: 31

May be you must have stored 'sweden' with trailing spaces in your table, So just check

select * from profiles where TRIM(country) = 'Sweden'

REGEX operator lists rows with 'Sweden' anywhere in it

EG: Sweden_ , __Sweden, Sweden.

Upvotes: 0

Charlesliam
Charlesliam

Reputation: 1313

If your filter data types in character it's best to use operator such as LIKE.

SELECT * FROM profiles WHERE upper(country) LIKE upper('Sweden');

Upvotes: 0

Girish
Girish

Reputation: 447

Please try with like operator.

select * from profiles WHERE country LIKE '%Sweden%'

Upvotes: 1

Related Questions