Difference between _%_% and __% in sql server

user_1725131 0
Viewed 9674

I am learning basics of SQL through W3School and during understanding basics of wildcards I went through the following query:

--Finds any values that start with "a" and are at least 3 characters in length
WHERE CustomerName LIKE 'a_%_%'

as per the example following query will search the table where CustomerName column start with 'a' and have at least 3 characters in length.

However, I try the following query also:

WHERE CustomerName LIKE 'a__%'

The above query also gives me the exact same result. I want to know whether there is any difference in both queries? Does the second query produce a different output in some specific scenario? If yes what will be that scenario?

sql-server-2012 tsql sql-server sql
2 Answers

Both start with A, and end with %. In the middle part, the first says "one char, then between zero and many chars, then one char", while the second one says "one char, then one char".

Considering that the part that comes after them (the final part) is %, which means "between zero and many chars", I can only see both clauses as identical, as they both essentially just want a string starting with A then at least two following characters. Perhaps if there were at least some limitations on what characters were allowed by the _, then maybe they could have been different.

If I had to choose, I'd go with the second one for being more intuitive. After all, many other masks (e.g. a%%%%%%_%%_%%%%%) will yield the same effect, but why the weird complexity?

user_21746230

For Like operator a single underscore "_" means, any single character, so if you put One underscore like

ColumnName LIKE 'a_%'

you basically saying you need a string where first letter is 'a' then followed by another single character and then followed by anything or nothing.

ColumnName LIKE 'a__%' OR ColumnName LIKE 'a_%_%'

Both expressions mean first letter 'a' then followed by two characters and then followed by anything or nothing. Or in simple English any string with 3 or more character starting with a.

user_20410920
Related