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