SQL – Like and wildcard, not expected result

I’m still a newbie with SQL so please be patient with me.

I have got the following WHERE statement in a query:

WHERE Scopes.Name = 'APAC' AND Sites.City LIKE '%o%'

It’s producing the following result (as expected):

While if I remove the ending wildcard, like this:

WHERE Scopes.Name = 'APAC' AND Sites.City LIKE '%o'

It results in an empty table. What I cannot understand is that the city name “Repetto” clearly ends with a “o”, thus LIKE ‘%o’ in the query should be producing the same result as LIKE ‘%o%’.

Am I misunderstanding the use of the wildcards? Can anyone kindly explain to me the logic behind?

Answer

The use of wildcards is correct. What this means is that there is a character after the o in 'Repetto'.

This could be a hidden character or a space. One possibility is that the column is declared as a char() rather than a varchar(), so the value is padded with spaces.