Split string by specific character SQL-Standard

In my SQL statement, I have to extract the substring from the string at the '_' character. The strings can be, for example, "A_XXX" "AB_XXX" "ABC_XXXX", so the extracted substrings should look 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;





I need this query to check if a specific substring is in a string array.

The whole query will look like this:

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 to SQL-Standard?

Thanks in advance for your help.


If PostgreSQL provides an alternative function, it helps as well. The rest can be solved, for example B. The really important part is to get the substring.


source to share

3 answers

The second example is a little confusing because you are confusing 'ABC_AB_A'

and 'AB_XXX'

not sure if this is a typo.

But if you just want all the characters before the first _

, then the following will work in Postgres:

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


or using regex:

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


You can also use regex in Oracle:

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



Postgres substring

always returns the first regex capturing group, whereas in Oracle you can specify the group you want: this is the last parameter of the function regexp_substr()


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



Standard SQL string functions are described in: String Functions and SQL Statements .

There is a function substring

that can fetch content directly without having to make function calls. It is detailed in Pattern Matching as:

A three-parameter subscript function, substring (a string from pattern for an escape character) , extracts a substring that matches an SQL regular expression pattern. As with the similar case, the specified pattern must match the entire data string, otherwise the function will fail and return null. To indicate a portion of the pattern that must be returned for success, the pattern must contain two occurrences of the escape character, followed by a double quote ("). Text matching the portion of the pattern between these markers is returned.

In your case:

select substring('AB_XX' from '#"%#"#_%' for '#');



(1 row)

The syntax is a bit weird, especially since it _

is a wildcard for one character, so it needs to be specified, but this is the SQL standard.

For syntax that more people use, consider regexp_replace()

or a similar function that works with POSIX regular expressions.



TL; dr

Use which was specially created for this: split_part

split_part(string, '_', 1)



Quoting this API document :


splits the string at the specified delimiter and returns the nth substring.

The 3 parameters are the string to be split, the separator and the part / substring number (starting at 1) to be returned.

Thus, if you have a field with a name string

that contains things like AB_XXX

and you want to get all up _

, then you divide it and get the first part / The substring: split_part(string, '_', 1)




All Articles