Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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 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

like image 516
Jim Avatar asked Dec 19 '14 16:12

Jim


People also ask

Why do you put [] in SQL?

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.

What are the two main wildcards you can use in a like?

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.

What is [] in SQL query?

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.

Can we use multiple values in like operator in SQL?

“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.


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.

like image 133
Jeffrey Wieder Avatar answered Sep 26 '22 07:09

Jeffrey Wieder


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)

like image 36
Charles D. Avatar answered Sep 24 '22 07:09

Charles D.