Split string at specific character SQL-Standard

Question

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'.

In Oracle this is easy with the substr() and instr() functions:

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?

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.

Answer 1

Your second example is a bit confusing because you are mixing 'ABC_AB_A' and 'AB_XXX' not sure if that is typo.

But if you just want all characters before the first _ then the following works in 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:

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

Postgres' substring function 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.

SQLFiddle for Oracle: http://sqlfiddle.com/#!4/b138c/1
SQLFiddle for Postgres: http://sqlfiddle.com/#!15/4b2bb/1