Oracle REGEXP_REPLACE digits and spaces in beginning

Question

I have data of this pattern:

000000001 ItemA
000000002 ItemB
000000003 ItemC

and I want to get this result:

ItemA
ItemB
ItemC

I tried REGEXP_REPLACE with this regex: @"^[\d-]*\s*" that I found in the accepted answer of this: regex remove digits and - in beginning question like this:

SELECT REGEXP_REPLACE(test_column, '@"^[\d-]*\s*"', '') FROM test_table;

but it doesn't work...

score 2 · Accepted Answer · answered Jan 31 '23 at 22:11

The solution's code snippet is written in C# and utilizes .NET Regular Expressions. However, the Oracle database requires POSIX Extended Regular Expressions (ERE) for compatibility with its REGEXP_REPLACE function. To resolve this issue, you can use an Oracle POSIX-compliant regular expression, like the one below:

^[0-9]*[[:space:]]*

^ - It matches the beginning of the text being searched.
[0-9] - It matches any single character that is a digit (0 to 9).
* - It matches zero or more occurrences of the preceding character.
[[:space:]] - It matches any white-space character, including spaces, tabs, and line breaks.
* - It matches zero or more white-space characters.

Thank you very much for the detailed explanation, I didnt know there were many types of regular expressions. — hexidis, Feb 01 '23 at 18:46

score 2 · Answer 2 · answered Jan 31 '23 at 23:18

You have some small errors, but you can use folowing two queries

CREATE TABLE table1
    ("data" varchar2(15))
;

INSERT ALL 
    INTO table1 ("data")
         VALUES ('000000001 ItemA')
    INTO table1 ("data")
         VALUES ('000000002 ItemB')
    INTO table1 ("data")
         VALUES ('000000003 ItemC')
SELECT * FROM dual

3 rows affected

SELECT REGEXP_REPLACE("data", '^[0-9-]*\s', '') FROM table1;

REGEXP_REPLACE("DATA",'^[0-9-]*\S','')
ItemA
ItemB
ItemC

SELECT REGEXP_REPLACE("data", '^[[:digit:]-]*\s', '') FROM table1;

REGEXP_REPLACE("DATA",'^[[:DIGIT:]-]*\S','')
ItemA
ItemB
ItemC

fiddle

Thank you very much for the 2 variations. – hexidis Feb 01 '23 at 18:42 — hexidis, Feb 01 '23 at 18:42

score 1 · Answer 3 · answered Feb 01 '23 at 06:03

If it doesn't have to be regular expression, try (fast!) substr + instr combination. For small data sets you won't notice any difference, but with A LOT of data, this should behave better.

Sample data:

SQL> with test (col) as
  2    (select '000000001 ItemA' from dual union all
  3     select '000000002 ItemB' from dual
  4    )

Query:

  5  select substr(col, instr(col, ' ') + 1) result
  6  from test;

RESULT
---------------
ItemA
ItemB

SQL>

It has, but thank you very much, I will use it in other cases. — hexidis, Feb 01 '23 at 18:42

Oracle REGEXP_REPLACE digits and spaces in beginning

3 Answers3