postgresql 在特定字符处拆分字符串 SQL-Standard
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29895896/
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
Split string at specific character SQL-Standard
提问by user3906778
In my SQL statement I have to extract a substring from a string at the character '_'. Strings can be for example 'A_XXX' 'AB_XXX' 'ABC_XXXX', so the extracted substrings should be like 'A' 'AB' 'ABC'.
在我的 SQL 语句中，我必须从字符 '_' 处的字符串中提取一个子字符串。字符串可以是例如 'A_XXX' 'AB_XXX' 'ABC_XXXX'，因此提取的子字符串应该类似于 'A' 'AB' 'ABC'。
In Oracle this is easy with the substr() and instr() functions:
在 Oracle 中，使用 substr() 和 instr() 函数很容易做到这一点：
select substr('AB_XXX', 1, instr('AB_XXX', '_')-1) as substring
from dual;
The result would be:
结果将是：
SUBSTRING
------------------------
AB
I need this query to check if a specific substring is in an array of strings.
我需要这个查询来检查特定的子字符串是否在字符串数组中。
The whole query would look like:
整个查询看起来像：
select 'AB_XXX' from dual
where (instr('ABC_AB_A', substr('AB_XXX', 1, instr('AB_XXX', '_')-1))>0);
Is there a way to write it in SQL-Standard?
有没有办法用 SQL-Standard 编写它？
Thanks in advance for your help.
在此先感谢您的帮助。
Edit:
编辑：
If PostgreSQL provides an alternative function, it also helps. The rest could be solved with e.g. IN. The really important part is to get the substring.
如果 PostgreSQL 提供了替代功能，它也会有所帮助。其余的可以用例如 IN 解决。真正重要的部分是获取子字符串。
回答by a_horse_with_no_name
Your second example is a bit confusing because you are mixing 'ABC_AB_A'and 'AB_XXX'not sure if that is typo.
你的第二个例子有点令人困惑，因为你正在混合'ABC_AB_A'并且'AB_XXX'不确定这是否是错字。
But if you just want all characters before the first _then the following works in Postgres:
但是，如果您只想要第一个字符之前的所有字符，_那么以下内容在 Postgres 中有效：
left(col, strpos(col, '_') - 1)
or using a regular expression:
或使用正则表达式：
substring(col from '([A-Z]+)(_{1})')
You can use a regular expression in Oracle as well:
您也可以在 Oracle 中使用正则表达式：
regexp_substr(col, '([A-Z]+)(_{1})', 1, 1, 'i', 1)
Postgres' substringfunction always returns the first capturing group of the regex whereas in Oracle you can specify the group you want: that is the last parameter to the regexp_substr()function.
Postgres 的substring函数总是返回正则表达式的第一个捕获组，而在 Oracle 中，您可以指定所需的组：这是regexp_substr()函数的最后一个参数。
SQLFiddle for Oracle: http://sqlfiddle.com/#!4/b138c/1
SQLFiddle for Postgres: http://sqlfiddle.com/#!15/4b2bb/1
SQLFiddle对于Oracle：http://sqlfiddle.com/#!4/b138c/1
SQLFiddle Postgres的：http://sqlfiddle.com/#!15/4b2bb/1
回答by totymedli
tl;dr
tl;博士
Use split_partwhich was purposely built for this:
使用split_part这是特意为这个内置：
split_part(string, '_', 1)
Explanation
解释
Quoting this API docs:
引用此API 文档：
SPLIT_PART()function splits a string on a specified delimiter and returns the nth substring.
SPLIT_PART()函数在指定的分隔符上拆分字符串并返回第 n 个子字符串。
The 3 parameters are the string to be split, the delimiter, and the part/substring number (starting from 1) to be returned.
3 个参数是要拆分的字符串、分隔符和要返回的部分/子字符串编号（从 1 开始）。
So if you have a field named stringthat contains stuff like AB_XXXand you would like to get everything before _, then you split by that and get the first part/substring: split_part(string, '_', 1).
因此，如果您有一个名为的字段string，其中包含诸如此类的内容，AB_XXX并且您希望获得之前的所有内容_，那么您可以将其拆分并获得第一部分/子字符串：split_part(string, '_', 1)。
回答by Daniel Vérité
The standard SQL string functions are described at: SQL String Functions and Operators.
标准 SQL 字符串函数在以下位置进行了描述： SQL 字符串函数和运算符。
There's a substringfunction that can extract contents directly, without having to nest function calls. It's detailed in Pattern matchingas:
有一个substring函数可以直接提取内容，无需嵌套函数调用。它在模式匹配中详细描述为：
The substring function with three parameters, substring(string from pattern for escape-character), provides extraction of a substring that matches an SQL regular expression pattern. As with SIMILAR TO, the specified pattern must match the entire data string, or else the function fails and returns null. To indicate the part of the pattern that should be returned on success, the pattern must contain two occurrences of the escape character followed by a double quote ("). The text matching the portion of the pattern between these markers is returned.
带有三个参数的 substring 函数substring(string from pattern for escape-character)提供了与 SQL 正则表达式模式匹配的子字符串的提取。与 SIMILAR TO 一样，指定的模式必须匹配整个数据字符串，否则函数失败并返回 null。要指示成功时应返回的模式部分，模式必须包含两次出现的转义字符，后跟双引号 (")。返回与这些标记之间的模式部分匹配的文本。
In your case:
在你的情况下：
select substring('AB_XX' from '#"%#"#_%' for '#');
Result:
结果：
substring ----------- AB (1 row)
The syntax is a bit weird, especially since _is a wildcard for a single character so it has to be quoted, but it's the SQL standard.
语法有点奇怪，特别是因为它_是单个字符的通配符，因此必须用引号引起来，但这是 SQL 标准。
For the syntax that more people use, consider regexp_replace()or a similar function working with POSIX regular expressions.
对于更多人使用的语法，请考虑regexp_replace()使用 POSIX 正则表达式的类似函数。
