What does command LIKE '[atoz:a]%' mean in SQL Server?

Question

I've inherited a database and application from a project and I'm trying to debug a problem with a database query.

There's a line in the query that reads:

WHERE property_title LIKE '[atoz:a]%' 

I'm struggling to understand what the atoz command is doing as I've never come across it before - I assumed it was stating that it would only allow characters in the title - but some of the titles contain symbols such as () or -

I've tried searching for it on Google but I must be using the wrong terminology as nothing is appearing. If someone could explain it to me, or point me to a resource that would be great!

Thanks

Answer 1

This is looking for property_title that starts with the letters "a", "t", "o", "z" and ":". The second "a" is redundant.

I would guess the intention is actually:

WHERE property_title LIKE '[a-z]%' 

which would specify that the property title starts with a letter (or a lower case letter, depending on the collation being used).

Answer 2

This is just part of the LIKE operator of T-SQL:

[ ]
Any single character within the specified range ([a-f]) or set ([abcdef]).
WHERE au_lname LIKE '[C-P]arsen' finds author last names ending with arsen and starting with any single character between C and P, for example Carsen, Larsen, Karsen, and so on. In range searches, the characters included in the range may vary depending on the sorting rules of the collation.

The exact expression you're seeing:

'[atoz:a]%'

basically means this:

• First any single character that can be one of the following:
  • a, t, o, z, or :
• Then followed by anything (even nothing)

Note that atoz does not mean any character from a to z, it literally means the 4 characters a, t, o and z. To get any character from a to z you would use [a-z]. The second a in the expression is redundant, as [aa] means the same as [a].