PL/SQL How do i remove characters from data

user_11334072 0
Viewed 583

I have rows that contain certain data such as:

EUR-40E
CP1-40E
CP1-36E
CP1FP-48E

My question is, how do i remove all characters leaving only the numbers after the hyphen.

I have tried to do it using this:

REGEXP_REPLACE (po.config_id, '\D')

But end up with this output:

140
140
136

It takes all characters but unfortunately the 1 before the hyphen remains?

Thanks

plsql oracle
2 Answers

You could do it like that:

select substr(regexp_substr('EUR-40E','-[[:digit:]]+'),2) from dual;
user_11883180

Try :

select regexp_replace(substr(text,instr(text,'-'),length(text)), '[^0-9]', '') from dual;

replace text with the actual value e.g. CP1FP-48E56T

Example ( I created a dummy table foo with your values ) :

SQL> select * from foo;

TEXT
------------
EUR-40E
CP1-40E
CP1-36E
CP1FP-48E
CP1FP-48E54R

SQL> select regexp_replace(substr(text,instr(text,'-'),length(text)), '[^0-9]', '') output from foo;

OUTPUT
--------------------------------------------------------------------------------
40
40
36
48
4854
user_67481080
Related