SQL - Like and wildcard, not expected result

user_13482926 0
Viewed 44

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): click here to see the picture

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?

wildcard sql-like sql
2 Answers

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.

user_11440350

It may be possible that "Repetto " has an ending "space" character. 

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

If this is the case, "Repetto" with the ending space does not match the '%o' wildcard expression but '%o%' does.

user_50204620
Related