How to Select a Substring in Oracle SQL Up to a Specific Character

How to Select a substring in Oracle SQL up to a specific character?

Using a combination of SUBSTR, INSTR, and NVL (for strings without an underscore) will return what you want:

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

Result:

output
------
ABC

Use:

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

Reference:

• SUBSTR
• INSTR

Addendum

If using Oracle10g+, you can use regex via REGEXP_SUBSTR.

How to get string after character oracle

For a string operation as simple as this, I might just use the base INSTR() and SUBSTR() functions. In the query below, we take the substring of your column beginning at two positions after the hyphen.

SELECT
    SUBSTR(col, INSTR(col, '-') + 2) AS subject
FROM yourTable

We could also use REGEXP_SUBSTR() here (see Gordon's answer), but it would be a bit more complex and the performance might not be as good as the above query.

Oracle substr before a specific character

One option, using REGEXP_SUBSTR:

WITH yourTable AS (
    SELECT 'Lorik_1' AS col FROM dual UNION ALL
    SELECT 'Lorik' FROM dual
)

SELECT REGEXP_SUBSTR (col, '^[^_]+')
FROM yourTable;

This outputs Lorik for both columns, see the demo here:

Demo

How do I Select a substring in Oracle SQL after a specific word in the string?

Your regex does not do what you want. You are defining a character class that must not contain characters from string 'Hændelse'. Basically it is looking for a series of characters other than 'H', 'æ', 'n', 'd', 'e', 'l', 's', 'e'at the end of the string.

One option is regexp_replace():

regexp_replace(string, '^.*Hændelse\s*', '') new_string

Note that this would be more efficiently done with simple string functions (although a bit more convoluted):

ltrim(substr(string, instr(string, 'Hændelse') + length('Hændelse'))) new_string 

Extract integer from a string in Oracle SQL and find and replace a specific character with another

To extract only number part please use '[^[:digit:]]' instead of '[[:alpha:]]' with regexp_replace()

regexp_replace(column_name, '[^[:digit:]]', '')

You can use below query to resolve your second problem.

select '"'||replace('Example"','"','""')||'"' from yourtable

Using REGEXP_SUBSTR with Strings Qualifier

The problem with your query is that if you use [^PLE] it would match any characters other than P or L or E. You are looking for an occurence of PLE consecutively. So, use

select REGEXP_SUBSTR(colname,'(.+)PLE',1,1,null,1) 
from tablename

This returns the substring up to the last occurrence of PLE in the string.

If the string contains multiple instances of PLE and only the substring up to the first occurrence needs to be extracted, use

select REGEXP_SUBSTR(colname,'(.+?)PLE',1,1,null,1) 
from tablename

---

Related Topics