Menu
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:
And these strings should not match:
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
516
1) If you have SQL keyword, space or any other illegal characters then you need to use square brackets. 2) SQL server parse and compiler much more easy to validate and compile code. 3) code search tools easy to find table names or column names only.
There are two wildcards used in conjunction with the LIKE operator. The percent sign represents zero, one, or multiple characters. The underscore represents a single number or character.
The brackets are required if you use keywords or special chars in the column names or identifiers. You could name a column [First Name] (with a space) – but then you'd need to use brackets every time you referred to that column. The newer tools add them everywhere just in case or for consistency.
“The SQL LIKE operator allows performing logical evaluation for any matching records. Using the LIKE operator, you can specify single or multiple conditions. This allows you to perform an action such as select, delete, and updating any columns or records that match the specified conditions.
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.
133
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)
36
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
