创建查找特定数字和字母模式的 SQL 'LIKE' 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3054783/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create SQL 'LIKE' Statment that looks for specific Patterns of numbers and letters
提问by user234872
Is it possible to use LIKE in a SQL query to look for patterns of numbers and letters. I need to locate all records where the specific field data has the pattern (2 Numbers,1 hyphen, 3 Letters) ## - AAA I am using SSMS with SQL Server 2008. Any help would be appreciated. THANKS.
是否可以在 SQL 查询中使用 LIKE 来查找数字和字母的模式。我需要找到特定字段数据具有模式（2 个数字、1 个连字符、3 个字母）## - AAA 我在 SQL Server 2008 中使用 SSMS 的所有记录。任何帮助将不胜感激。谢谢。
回答by Martin Smith
I think LIKE '%[0-9][0-9]-[A-Z][A-Z][A-Z]%'should work.
我认为LIKE '%[0-9][0-9]-[A-Z][A-Z][A-Z]%'应该工作。
I'm not sure of your case sensitivity requirements but you can stick a COLLATE in as below.
我不确定您的区分大小写要求，但您可以按如下方式粘贴 COLLATE。
select * from
(
SELECT 'GHSASKJK' AS T UNION ALL
SELECT 'HGGH11-ABC' UNION ALL
SELECT 'HGGH11-abc'
) f
WHERE T LIKE '%[0-9][0-9]-[A-Z][A-Z][A-Z]%' COLLATE Latin1_General_CS_AS
回答by Thomas
回答by Ruben
If the data is exactly "##-AAA", you can just use LIKE '[0-9][0-9]-[A-Z][A-Z][A-Z]'. If the data contains this sequence somewhere, use LIKE '%[0-9][0-9]-[A-Z][A-Z][A-Z]%'.
如果数据恰好是“##-AAA”，则可以使用LIKE '[0-9][0-9]-[A-Z][A-Z][A-Z]'. 如果数据在某处包含此序列，请使用LIKE '%[0-9][0-9]-[A-Z][A-Z][A-Z]%'.
Note that if your column is indexed, LIKE '[0-9][0-9]-[A-Z][A-Z][A-Z]'can be a lotfaster than any UFD or CLR regex, because SQL Server understands LIKEbetter, and it can more easily skip parts of the index if it would never match. For example, all records starting with a character outside of 0-9 will be ignored immediately, whereas a UDF or CLR regex will still read these values.
请注意，如果您的列索引，LIKE '[0-9][0-9]-[A-Z][A-Z][A-Z]'可以有很多比任何UFD或CLR正则表达式更快，因为SQL Server可以理解LIKE更好，它可以更容易地跳过指标的部分，如果它永远不会匹配。例如，所有以 0-9 之外的字符开头的记录都将被立即忽略，而 UDF 或 CLR 正则表达式仍将读取这些值。
回答by Chris Klepeis
I would recommend creating a CLR assembly with .Net. That way you can create a function or SP that can use regex.
我建议使用 .Net 创建一个 CLR 程序集。这样你就可以创建一个可以使用正则表达式的函数或 SP。
Edit: Even though this can be done via LIKE as stated in other answers, I would still recommend creating the assembly. If your data changes for some reason and you need an advanced way of looking up data, this regex assembly would be able to accomodate the change
编辑：尽管这可以通过其他答案中所述的 LIKE 完成，但我仍然建议创建程序集。如果您的数据由于某种原因发生更改，并且您需要一种高级的数据查找方式，则此正则表达式程序集将能够适应更改
回答by George Mastros
You should be able to use a like search for this. Your where clause would be similar to:
您应该能够为此使用类似搜索。您的 where 子句类似于：
Where YourColumnName Like '%[0-9][0-9]-[a-z][a-z][a-z]%'
*** This assumes a case insensitive collation
*** 这假设不区分大小写的排序规则
