SQL Server，在 LIKE 语句中结合 % 和 [] 通配符 [英] SQL Server, combining % and [] wildcards in LIKE statement
问题描述
在 t-sql 中，有没有办法在 like 语句中进行模式匹配，以便您可以在给定的集合中搜索 1 个或多个字符?
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?
具体来说，我试图提出一个 LIKE 语句，该语句将返回以 1 个或多个字母开头并以 1 个或多个数字结尾的字符串.
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
我知道您可以使用 % 通配符来匹配 0 个或多个字符的字符串，并且您可以使用方括号 [] 来匹配给定集合中的单个字符.但是有没有办法将它们组合起来，以便我可以匹配给定集合中的 1 个或多个字符?
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]'
有人知道这个问题的解决方案吗?或者它在 SQL Server 中是不可能的?
Does anyone know of a solution to this problem? Or is it just not possible in SQL Server?
谢谢，吉姆
推荐答案
like 模式匹配非常有限，它不允许普通的正则表达式.
Like pattern matching is very limited, it does not allow for normal regular expressions.
有关详细信息，请参阅此处.
See here for details.
根据您的需要使用:
WHERE ColumnName LIKE '[a-z]%[0-9]'
这将匹配任何字母后跟任何内容，后跟一个数字.SQL 将强制字母和数字位于字符串的两端，因为在我们的 [] 匹配集之前或之后没有模式或文字字符.
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.
这篇关于SQL Server，在 LIKE 语句中结合 % 和 [] 通配符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
