How to Extract Substrings Before an Underscore in Oracle SQL?

Extracting Substrings to a Specific Character in Oracle SQL

Problem:
Selecting substrings from a column containing results with varying character sequences before an underscore ("_") character, while excluding the underscore itself.

Solution:

To achieve the desired result, a combination of SQL functions provides a solution:

SELECT NVL(SUBSTR(column, 0, INSTR(column, '_')-1), column) AS output
FROM YOUR_TABLE;

Explanation:

• SUBSTR function extracts a substring starting from the specified position (0) and continuing for a specified length.
• INSTR function determines the position of the first occurrence of the underscore character within the string.
• NVL function evaluates to the first non-null value, ensuring that substrings with no underscore character return the original value.
• The subtraction of 1 from INSTR(column, '_') ensures that only characters preceding the underscore are included in the output.

Example:

Given:

ORG | VALUE
1   | ABC_blahblahblah
2   | DEFGH_moreblahblahblah
3   | IJKLMNOP_moremoremoremore

Query:

SELECT NVL(SUBSTR(VALUE, 0, INSTR(VALUE, '_')-1), VALUE) AS output
FROM ORG;

Result:

OUTPUT
ABC
DEFGH
IJKLMNOP

Note:

For Oracle10g versions, the REGEXP_SUBSTR function can be used as an alternative to SUBSTR:

SELECT REGEXP_SUBSTR(column, '^.*(?=_)') AS output
FROM YOUR_TABLE;

The above is the detailed content of How to Extract Substrings Before an Underscore in Oracle SQL?. For more information, please follow other related articles on the PHP Chinese website!