14

In t-sql, is there a way to do pattern matching in a like statement such that you can search for 1 or more characters in a given set?

To be specific, I'm trying to come up with a LIKE statement that will return strings that begin with 1 or more letters and end in 1 or more numbers.

So these strings should match:

  • abcd1234
  • a1
  • abcdef2
  • ab123456

And these strings should not match:

  • abcd
  • 1234
  • abcd1234abcd
  • 1abcd1

I know you can use the % wildcard to match a string of 0 or more characters, and you can use brackets[] to match a single character in a given set. But is there any way to combine those so that I can match on 1 or more characters in a given set?

Something like this would be nice, but of course doesn't work:

WHERE ColumnName LIKE '[%a-z][%0-9]'

Does anyone know of a solution to this problem? Or is it just not possible in SQL Server?

Thanks, Jim

Jim
  • 224
  • 1
  • 3
  • 10
  • 1
    don't you just need to check that the entire thing is alpha numeric and that the first character is text and last one is numeric? – xQbert Dec 19 '14 at 16:18
  • 1
    no that won't work because i want all alpha characters first, followed by only numeric characters. Martin Smith's comment below actually does what I'm looking for. – Jim Dec 19 '14 at 18:25

2 Answers2

14

Like pattern matching is very limited, it does not allow for normal regular expressions.

See here for details.

For your needs use:

WHERE ColumnName LIKE '[a-z]%[0-9]'

This will match any letter followed by anything, followed by a number. SQL will enforce that the letter and number are at the two ends of the string because there is no pattern or literal character before or after our [] match set.

Jeffrey Wieder
  • 2,336
  • 1
  • 14
  • 12
  • 2
    Yeah I've come to the realization that it can't be done without CLR. Thanks for the suggestion, but that doesn't do what I need. It will make something like "a1234bcde1" match, when it shouldn't. – Jim Dec 19 '14 at 16:39
  • 4
    @Jim `WHERE ColumnName LIKE '[a-z]%[0-9]' AND ColumnName NOT LIKE '%[0-9][a-z]%'`? – Martin Smith Dec 19 '14 at 16:40
  • 1
    Yes @Martin, that's actually what I was looking for and failing to come up with. I guess I needed 2 separate statements to accomplish it. Thank you. – Jim Dec 19 '14 at 18:28
  • 3
    @Jim - Though I suppose you actually need a third to exclude strings containing characters that are neither alpha nor numeric `AND ColumnName NOT LIKE '%[^0-9A-Z]%'` – Martin Smith Dec 19 '14 at 18:32
  • 1
    Yes @Martin, you're right with that as well, and I plan on doing that – Jim Dec 19 '14 at 18:34
1

If you want to use Regex and have permissions, you can write a user defined function to give yourself access to the Regular Expressions parser on the SQL server's .NET CLR:

http://msdn.microsoft.com/en-us/magazine/cc163473.aspx (SQL 2005 and higher)

Charles D.
  • 87
  • 3