Home » SQL & PL/SQL » SQL & PL/SQL » REGEXP_SUBSTR (ORACLE,12C,UNIX)
REGEXP_SUBSTR [message #666623] |
Fri, 17 November 2017 09:16 |
pstanand
Messages: 133 Registered: February 2005 Location: Chennai,India
|
Senior Member |
|
|
Hi,
I have a description column in which the data would like below.
Ded 0,90%, Cpy 150, ABCL CPY Mx 0%, Pen 0, FSA Y,HRA Y,CM T,Type C
Ded 31,100%, Cpy 0, ABCL CPY Mx 0%, Pen 0, FSA Y,HRA Y,CM T,Type C
Ded 31,100%, Cpy 20, ABCL CPY Mx 0%, Pen 0, FSA Y,HRA Y,CM T,Type C
I want to get the numeric portion alone after the Cpy and before the third comma. i.e. in Cpy 150, I need only 150.
Below is my query.
SELECT REGEXP_SUBSTR(SE_DESC,'(.*?)(\,$)',1,3,null,1) FROM SE_DESC;
But I get null as the result. Kindly let me know the correct solution.
Many thanks in advance.
|
|
|
Re: REGEXP_SUBSTR [message #666624 is a reply to message #666623] |
Fri, 17 November 2017 09:37 |
pstanand
Messages: 133 Registered: February 2005 Location: Chennai,India
|
Senior Member |
|
|
Hi,
I have tried to get the value using the below query but it fetches Cpy 150.
SELECT REGEXP_SUBSTR(SE_DESC,'[^,]+',1,3) FROM SE_DESC;
I'm getting Cpy 150, Cpy 0, Cpy 30 as rows. How do I eliminate the Cpy and get only the numbers.
Please help me.
|
|
|
|
Re: REGEXP_SUBSTR [message #666626 is a reply to message #666623] |
Fri, 17 November 2017 10:05 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
SQL> with data as (
2 select 'Ded 0,90%, Cpy 150, ABCL CPY Mx 0%, Pen 0, FSA Y,HRA Y,CM T,Type C' val from dual union all
3 select 'Ded 31,100%, Cpy 0, ABCL CPY Mx 0%, Pen 0, FSA Y,HRA Y,CM T,Type C' val from dual union all
4 select 'Ded 31,100%, Cpy 20, ABCL CPY Mx 0%, Pen 0, FSA Y,HRA Y,CM T,Type C' val from dual
5 )
6 select val,
7 to_number(regexp_replace(val, '^.*Cpy (\d+).*$','\1')) res
8 from data
9 /
VAL RES
------------------------------------------------------------------- ----------
Ded 0,90%, Cpy 150, ABCL CPY Mx 0%, Pen 0, FSA Y,HRA Y,CM T,Type C 150
Ded 31,100%, Cpy 0, ABCL CPY Mx 0%, Pen 0, FSA Y,HRA Y,CM T,Type C 0
Ded 31,100%, Cpy 20, ABCL CPY Mx 0%, Pen 0, FSA Y,HRA Y,CM T,Type C 20
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 06:39:24 CDT 2024
|