Remove all characters after a specific character in PL/SQL

Question

How do I get a substring from this example value:

 john.abc_1234

I want it to return john.abc.So basically we need to remove all the information after _.

More examples: 1234_abc

Does this answer your question? [How to remove part of the string in oracle](https://stackoverflow.com/questions/19852516/how-to-remove-part-of-the-string-in-oracle) — Himanshu, Dec 14 '19 at 09:04

Adriano Carneiro · Accepted Answer · 2011-10-10T19:55:51.660

25

You can use SUBSTR and INSTR:

select substr('john.abc_1234', 1, instr('john.abc_1234', '_') -1)
from dual

Warning: This is only guaranteed to work if your string actually has an underscore in it

Update

Additionally, if you are running from Oracle 10g on, you could take the Regex path, which would more powerfully handle exceptions.

Here are some links on how to do it in Oracle:

edited Oct 10 '11 at 19:55

answered Oct 10 '11 at 19:29

Adriano Carneiro

57,693
12
90
123

Glad to help. Make sure you upvote/accept the answers that help you – Adriano Carneiro Oct 10 '11 at 19:40
@Adrian, are regexes available in 9i? I thought they were 10g plus only? – Ben Oct 10 '11 at 19:50
@Ben I totally missed the oracle9i tag. I guess Regex is only available from 10 on. – Adriano Carneiro Oct 10 '11 at 19:55
3

For the sake of having a regular expression example, here's a way with a regex: `select regexp_substr('john.abc_1234', '[^_]*') from dual;` It assumes you want keep all up until the first underscore. – Gary_W Aug 20 '15 at 20:25
or `select regexp_replace('john.abc_1234', '(.*)_.*','\1') from dual` might be used for version 10+ – Barbaros Özhan Jan 23 '19 at 20:21
I wonder whats a big deal in this question and answer. Its a simple combo of instr and substr or REGEXP_REPLACE if the version supports – Himanshu Dec 14 '19 at 08:58

score 2 · Answer 2 · answered Dec 14 '19 at 09:11

You can use via a simple combination of substr(..) and instr(...) to find the string which is free from underscore special charecter. Also in order to only select _ containing strings you can make use of additional where clause as below

       select substr('john.abc_1234', 1, 
       instr('john.abc_1234', '_') -1)
      from dual where 
      instr('john.abc_1234', '_')>0

score 1 · Answer 3 · answered Aug 20 '15 at 17:55

You can use this monstrosity, when you're not sure if all your values contain the character you want to cut off your string at.

SELECT
    (CASE WHEN INSTR(field, '_') > 0
        THEN substr(field, 1, instr(field, '_') -1)
        ELSE field
    END) AS field
FROM
    dual

score -1 · Answer 4 · edited Mar 01 '16 at 18:58

This guy figured it out! http://programcsharp.com/blog/post/strip-non-numeric-characters-from-a-string-in-sql-server

SELECT
    (SELECT CAST(CAST((
        SELECT SUBSTRING(FieldToStrip, Number, 1)
        FROM master..spt_values
        WHERE Type='p' AND Number <= LEN(FieldToStrip) AND
            SUBSTRING(FieldToStrip, Number, 1) LIKE '[0-9]' FOR XML Path(''))
    AS xml) AS varchar(MAX)))
FROM
    SourceTable

Remove all characters after a specific character in PL/SQL

4 Answers4

Linked