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...

CodePudding user response：

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.

CodePudding user response：

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;

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

fiddle