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

Retrieving Substrings Up to a Specific Character in Oracle SQL

Selecting substrings from a table column but limiting the range to a specific character can be challenging. Consider a scenario where you have a column with values formatted as "ABC_blahblahblah." The goal is to extract only the part before the underscore.

The SUBSTRING function, which relies on position-based selection, won't suffice as the underscore's position varies. RTRIM, aimed at removing specific characters from the end of a string, doesn't precisely align with the requirement.

To address this challenge, a combination of Oracle SQL functions can be employed. By leveraging SUBSTR, INSTR, and NVL, we can achieve the desired results:

SELECT NVL(SUBSTR('ABC_blah', 0, INSTR('ABC_blah', '_')-1), 'ABC_blah') AS output
  FROM DUAL

Explanation:

• NVL ensures a valid result even if the substring doesn't contain an underscore.
• SUBSTR extracts a portion of the string, starting from position 0 (the beginning) and ending one character before the underscore. INSTR locates the position of the underscore.
• The result is assigned to the output column.

Usage:

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

In Oracle10g , REGEXP_SUBSTR can be utilized to perform regular expression matching, providing an alternative approach for extracting the desired substring.

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