SQL PostgreSQL LIKE 子句中的正则表达式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24368404/
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
Regular expression in PostgreSQL LIKE clause
提问by borarak
I'm stuck with a simple regular expression. Not sure what I'm missing. A little rusty on regex skills.
我被一个简单的正则表达式困住了。不知道我错过了什么。对正则表达式技能有点生疏。
The expression I'm trying to match is:
我试图匹配的表达式是：
select * from table where value like '00[1-9]%'
-- (third character should not be 0)
So this should match '0090D0DF143A'(format: text) but it's NOT!
所以这应该匹配'0090D0DF143A'（格式：文本）但它不是！
回答by Erwin Brandstetter
Like @a_horse commented, you would have to use the regular expression operator ~to use bracket expressions.
But there's more. I suggest:
就像@a_horse 评论的那样，您必须使用正则表达式运算符~才能使用括号表达式。
但还有更多。我建议：
SELECT *
FROM tbl
WHERE value ~ '^00[^0]'
^... match at start of string (your original expression could match at anyposition).[^0]... a bracket expression (character class) matching anycharacter that is not 0.
^... 在字符串开头匹配（您的原始表达式可以在任何位置匹配）。[^0]...匹配任何不是 的字符的括号表达式（字符类）0。
Or better, yet:
或者更好，但是：
SELECT *
FROM tbl
WHERE value LIKE '00%' -- starting with '00'
AND value NOT LIKE '000%' -- third character is not '0'
Why? LIKEis not as powerful, but typically faster than regular expressions. It's probably substantially faster to narrow down the set of candidates with a cheap LIKEexpression.
为什么？LIKE没有那么强大，但通常比正则表达式快。用廉价的LIKE表达式缩小候选集的范围可能要快得多。
Generally, you would use NOT LIKE '__0', but since we already establish LIKE '00%'in the other predicate, we can use the narrower (cheaper) pattern NOT LIKE '000'.
通常，您会使用NOT LIKE '__0'，但由于我们已经LIKE '00%'在另一个谓词中建立了，我们可以使用更窄（更便宜）的模式NOT LIKE '000'。
Postgres can use a simple btree indexfor the left-anchored expressions value LIKE '00%'(important for big tables), while that might not work for a more complex regular expression. The latest version of Postgres can use indexes for simple regular expressions, so it mightwork for this example. Details:
Postgres 可以为左锚定表达式使用简单的 btree索引value LIKE '00%'（对于大表很重要），而这可能不适用于更复杂的正则表达式。最新版本的 Postgres 可以为简单的正则表达式使用索引，所以它可能适用于这个例子。细节：
