在特定字符处拆分字符串SQL-标准

Question

在我的SQL语句中，我必须从字符'_‘处的字符串中提取一个子串。字符串可以是例如'A_XXX‘'AB_XXX’'ABC_XXXX'，因此提取的子字符串应该类似于'A‘'AB’'ABC‘。

在Oracle中，使用substr()和instr()函数很容易做到这一点：

select substr('AB_XXX', 1, instr('AB_XXX', '_')-1) as substring
from dual;

结果将是：

SUBSTRING
------------------------
AB

我需要这个查询来检查特定的子字符串是否在字符串数组中。

整个查询将如下所示：

select 'AB_XXX' from dual
where (instr('ABC_AB_A', substr('AB_XXX', 1, instr('AB_XXX', '_')-1))>0);

有没有办法用SQL-Standard编写它？

提前感谢您的帮助。

编辑：

如果PostgreSQL提供了一个替代函数，它也会有所帮助。其余的可以用IN来解决。真正重要的部分是获得子串。

Answer 1

你的第二个例子有点混乱，因为你混淆了'ABC_AB_A'和'AB_XXX'，不确定这是不是拼写错误。

但是，如果您只想要第一个_之前的所有字符，则以下命令在Postgres中有效：

left(col, strpos(col, '_') - 1)

或使用正则表达式：

substring(col from '([A-Z]+)(_{1})')

您也可以在Oracle中使用正则表达式：

regexp_substr(col, '([A-Z]+)(_{1})', 1, 1, 'i', 1)

Postgres的substring函数总是返回正则表达式的第一个捕获组，而在Oracle中，您可以指定所需的组:这是regexp_substr()函数的最后一个参数。

用于Oracle的SQLFiddle：http://sqlfiddle.com/#!4/b138c/1

Postgres的SQLFiddle：http://sqlfiddle.com/#!15/4b2bb/1

Answer 2

tl;dr

使用专门为此而构建的split_part：

split_part(string, '_', 1)

解释

引用此API docs

SPLIT_PART()函数在指定的分隔符上拆分字符串，并返回第n个子字符串。

这3个参数分别是要拆分的字符串、分隔符和要返回的分块/子字符串编号(从1开始)。

因此，如果您有一个名为string的字段，其中包含像AB_XXX这样的内容，并且您想要获取_之前的所有内容，那么您可以对其进行拆分，并获得第一部分/子字符串：split_part(string, '_', 1)。