CODEWITHSUNDEEP
sqlwildcardsql-like
Luca S.
Luca S.

Reputation: 63

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): 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?

Upvotes: 2

Views: 61

Answers (2)

Hazrelle
Hazrelle

Reputation: 856

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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 4

Related Questions