Sql Server, Combining % and [] Wildcards in Like Statement
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...
![Sql Server, Combining % and [] Wildcards in Like Statement](https://www.alledaags.nl/uploads/sql-server-combining-and-wildcards-in-like_1200.jpg)
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
2 Answers
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.
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:
(SQL 2005 and higher)
Alexander Ross has covered the video game industry for a decade, writing deep dives on game design, esports tournaments, VR developments, and gaming culture.
