4

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:

  1. ABC9
  2. DEF2
  3. GH7
  4. Z1
  5. XYH2

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

J.D.
  • 22,196
  • 3
  • 34
  • 78

2 Answers2

12

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.

Andriy M
  • 20,973
  • 6
  • 52
  • 93
  • This is a good start but only solves a subset of what I'm looking for unfortunately. (I should've included this scenario in my question, so apologies.) What if I need to check N occurrences of numbers that come after the letters, e.g. ABC123. My scenario is a little more complicated than that too, the generic format I'm trying to regex against is [1 or more letters or numbers]["space" character][1 or more numbers]["space" character"][1 or more numbers OR a specific word] – J.D. Oct 30 '19 at 20:47
  • 3
    @J.D.: Yes, I wish you'd posted the actual pattern from the start. It was nice feeling the problem was solved. So unlike the feeling of having merely scratched the surface :) However, I actually believe this question about a simplified version of your problem still has its own value. My answer shows an approach to solving this type of problem, and I would suggest that you first try and adapt it to the one you're actually facing before asking for more help. It could be a great learning experience for you. – Andriy M Oct 30 '19 at 22:33
  • 3
    @J.D.: If/when you post a new question, feel free to include your unsuccessful attempts in the question. People are more motivated to help when they see an honest effort from the OP, just saying. You can also include a link to this question for context if necessary. – Andriy M Oct 30 '19 at 22:33
3

As said by mustaccio in comments:

Is there anything that prevents you from using a proper regular expression implementation instead of trying to hack around LIKE?

There's an option to use CLR, Common Language Runtime, basically you create some C# functions, and you import it in your SQL server, from there on you can just call a SQL function, and it will execute your C# code for you.

This naturally gives you all the possibilies you have in .NET regex, straight from your SSMS.

There's a ton of different resources on how to go about this.
Red gate regex assembly
Code project example

Yannick Liekens
  • 1,556
  • 3
  • 14