Why doesn't this LIKE query return any results?

Question

I made table copied from W3schools 2015 ... Here's some of the data:

here the columns and data types i used as follows.

CustomerID = int
CustomerName = varchar
ContactName = varchar
Address = varchar
City = varchar
PostalCode = varchar
Country = text

When i used the following query i got the actual result.

SELECT * FROM Customers WHERE Country LIKE 'U%';    SELECT * FROM Customers WHERE Country LIKE 'M%';    SELECT * FROM Customers WHERE Country LIKE 'G%';

Now the problem was that When i used the following query, i weren't getting actual result.

SELECT * FROM Customers WHERE Country LIKE 's%';

i didn't see any row !! But We should have seen the following output,

why ?? can anybody explain pls...

Moreover I am totally in Novice phase..

Thanks in Advance

Case sensitivity can be a subtle thing in your table: https://dev.mysql.com/doc/refman/5.6/en/case-sensitivity.html — David, Nov 06 '15 at 18:19
Take a look at your collation and character set. That is, run `SHOW CREATE TABLE Customers` and see what you get. It looks like you're using a binary (case-sensitive) collation. — elixenide, Nov 06 '15 at 18:20
sorry ...Country Column data type was tinytext and charter set =latin1 — Rajib Mahmud, Nov 06 '15 at 18:35

score 0 · Answer 1 · answered Nov 06 '15 at 18:33

May be, This is Because of Your Country Column Starting with 'S'(which we think) are not really Starting with 'S', May be it's first charter is a Space.(Just A Guess)

so, For this Case you can try Once,

SELECT * FROM Customers WHERE rtrim(ltrim(Country)) LIKE 's%';

or

SELECT * FROM Customers WHERE replace(Country,' ','') LIKE 's%';

score -1 · Answer 2 · edited May 23 '17 at 12:23

-1

See How can I search (case-insensitive) in a column using LIKE wildcard?

edited May 23 '17 at 12:23

Community

1
1

answered Nov 06 '15 at 18:17

Mark He

735
7
14

1

Is this `mysql`or `sql-server` or `ms-access`, you listed all 3 tags – Mark He Nov 06 '15 at 18:22
sorry for that .. its will be mysql – Rajib Mahmud Nov 06 '15 at 18:28

Why doesn't this LIKE query return any results?

2 Answers2