How to Extract Substrings in Oracle SQL Up to a Specific Character?

Substring Selection in Oracle SQL Up to a Specific Character

In many database applications, it is often necessary to select only a portion of a string column based on a specific delimiter. In Oracle SQL, extracting substrings up to a specific character can be challenging due to the position-based nature of the SUBSTRING function. This article addresses this issue by presenting a comprehensive solution that accurately captures substrings based on a designated delimiter.

SOLUTION

To isolate substrings up to a specified character, a combination of three functions can be employed: SUBSTR, INSTR, and NVL. SUBSTR allows for the extraction of a portion of a string based on starting and ending positions. INSTR, on the other hand, identifies the location of a specified substring or character within a given string. NVL serves as a fallback mechanism, returning the original string if the specified character is not found.

For instance, consider the following table column with values:

ABC_blahblahblah
DEFGH_moreblahblahblah
IJKLMNOP_moremoremoremore

To retrieve only the substrings up to the underscore character, the following query can be used:

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

In this query, the NVL function ensures that even strings without an underscore character are returned, albeit with their original value.

REFERENCE

• [SUBSTR](https://docs.oracle.com/cd/B28359_01/appdev.111/b28396/functions148.htm#CHDBCHCJ)
• [INSTR](https://docs.oracle.com/cd/B28359_01/appdev.111/b28396/functions152.htm#CHDBCHCL)
• [NVL](https://docs.oracle.com/cd/B28359_01/appdev.111/b28396/functions060.htm#SQLRF00120)

ADDENDUM

In Oracle10g and later versions, the REGEXP_SUBSTR function can be utilized for more flexible substring extraction using regular expressions.

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