Is there an operator or an easy way to match an expression one or more times with the LIKE operator in SQL?

For example if I run the following query, the expression in the WHERE clause will only match on the first letter before a number but is there a way to match on one or more letters first (e.g. in the case where there's a variable number of letters before the number in the field I'm filtering on)?

SELECT FilteredField
FROM Table
WHERE FilteredField LIKE '[a-zA-Z][0-9]'

Example data:

• ABC9

• DEF2

• GH7

• Z1

• XYH2

Essentially I'm looking for the SQL Server 2016 equivalent of the RegEx + when using the LIKE operator.

If you are talking about solving this purely in Transact-SQL, you can cover all those cases in your example data with the following filter:

WHERE FilteredField LIKE '%[a-zA-Z][0-9]'

If you want to additionally stipulate that all the characters before the numeral must be Latin letters, you will need to get a little creative:

WHERE FilteredField     LIKE           '%[a-zA-Z][0-9]'
  AND FilteredField NOT LIKE '%[^a-zA-Z]%[a-zA-Z][0-9]'

Basically, you are saying:


The value must end with a letter and a numeral, but whatever precedes the letter must not be a non-letter.

There is no way to specify this with a single condition using only built-in syntax, if that was what you were after.

StackExchange Database Administrators Q#252239, answer score: 12