postgresql 选择中的PostgreSQL正则表达式捕获组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41818279/
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
PostgreSQL regular expression capture group in select
提问by AlexG
How can the matched regular expression be returned from an SQL select? I tried using REGEXP_EXTRACTwith no luck (function not available). What I've done that does workis this:
如何从 SQL 选择返回匹配的正则表达式？我尝试使用但REGEXP_EXTRACT没有运气（功能不可用）。我所做的工作是这样的：
SELECT column ~ '^stuff.*$'
FROM table;
but this gives me a list of true / false. I want to know what is extracted in each case.
但这给了我一个真/假列表。我想知道在每种情况下提取了什么。
回答by Hambone
If you're trying to capture the regex match that resulted from the expression, then substringwould do the trick:
如果您试图捕获由表达式产生的正则表达式匹配，那么substring可以解决这个问题：
select substring ('I have a dog', 'd[aeiou]g')
Would return any match, in this case "dog."
将返回任何匹配项，在本例中为“狗”。
I think the missing link of what you were trying above was that you need to put the expression you want to capture in parentheses. regexp_matcheswould work in this case (had you included parentheses around the expression you wanted to capture), but would return an array of text with each match. If it's one match, substringis sort of convenient.
我认为您在上面尝试的缺失环节是您需要将要捕获的表达式放在括号中。 regexp_matches在这种情况下会起作用（如果您在要捕获的表达式周围包含括号），但会返回每个匹配项的文本数组。如果是一场比赛，substring还是挺方便的。
So, circling back to your example, if you're trying to return stuffif and only if it's at the beginning of a column:
因此，回到您的示例，如果您尝试返回stuff当且仅当它位于列的开头时：
select substring (column, '^(stuff)')
or
或者
select (regexp_matches (column, '^(stuff)'))[1]
回答by Vamsi Prabhala
Use regexp_matches.
SELECT regexp_matches(column,'^stuff.*$')
FROM table
The regexp_matches function returns a text array of all of the captured substrings resulting from matching a POSIX regular expression pattern. It has the syntax regexp_matches(string, pattern [, flags ]). The function can return no rows, one row, or multiple rows (see the g flag below). If the pattern does not match, the function returns no rows. If the pattern contains no parenthesized subexpressions, then each row returned is a single-element text array containing the substring matching the whole pattern. If the pattern contains parenthesized subexpressions, the function returns a text array whose n'th element is the substring matching the n'th parenthesized subexpression of the pattern (not counting "non-capturing" parentheses; see below for details). The flags parameter is an optional text string containing zero or more single-letter flags that change the function's behavior. Flag g causes the function to find each match in the string, not only the first one, and return a row for each such match.
regexp_matches 函数返回由匹配 POSIX 正则表达式模式产生的所有捕获子字符串的文本数组。它的语法为 regexp_matches(string, pattern [, flags ])。该函数可以不返回行、一行或多行（请参阅下面的 g 标志）。如果模式不匹配，则该函数不返回任何行。如果模式不包含带括号的子表达式，则返回的每一行都是一个单元素文本数组，其中包含与整个模式匹配的子字符串。如果模式包含带括号的子表达式，则该函数返回一个文本数组，其第 n 个元素是与模式的第 n 个带括号的子表达式匹配的子字符串（不包括“非捕获”括号；有关详细信息，请参见下文）。flags 参数是一个可选的文本字符串，包含零个或多个改变函数行为的单字母标志。标志 g 使函数查找字符串中的每个匹配项，而不仅仅是第一个，并为每个此类匹配项返回一行。
回答by AlexG
I'm using Amazon Redshift which uses PostgreSQL 8.0.2 (I should have mentioned this in the question). For me what worked was REGEXP_SUBSTR
我正在使用使用 PostgreSQL 8.0.2 的 Amazon Redshift（我应该在问题中提到这一点）。对我来说有效的是REGEXP_SUBSTR
e.g.
例如
SELECT REGEXP_SUBSTR(column,'^stuff.*$')
FROM table
