CODEWITHSUNDEEP
sqlregexoracle-database
hexidis
hexidis

Reputation: 23

Oracle REGEXP_REPLACE digits and spaces in beginning

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

Upvotes: 2

Views: 669

Answers (3)

Littlefoot
Littlefoot

Reputation: 143103

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>

Upvotes: 1

nbk
nbk

Reputation: 49410

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

Upvotes: 2

Andreas Violaris
Andreas Violaris

Reputation: 4198

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.

Upvotes: 2

Related Questions