SQL Server combining% and [] wildcards in LIKE statement
Is there a way in t-sql to do pattern matching in a statement like this so 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 starting with 1 or more letters and ending with 1 or more numbers.
So these lines should match:
- abcd1234
- a1
- abcdef2
- AB123456
And these lines don't have to match:
- ABCD
- 1234
- abcd1234abcd
- 1abcd1
I know that you can use the% wildcard to match a string of 0 or more characters, and you can use the [] brackets to match a single character in a given set. But is there a way to combine them so that I can match 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
source to share
Like pattern matching, it is very limited, it does not allow normal regexes.
See here for details .
For your needs use:
WHERE ColumnName LIKE '[a-z]%[0-9]'
This will match any letter followed by anything and then a number. SQL will enforce letter and number on both ends of the string because there is no pattern or literal before or after our match set []
.
source to share
If you want to use Regex and have permissions, you can write a user-defined function to give you access to the regex parser in the .NET CLR SQL server:
http://msdn.microsoft.com/en-us/magazine/cc163473.aspx (SQL 2005 and up)
source to share