Home » SQL & PL/SQL » SQL & PL/SQL » REGEXP_SUBSTR (ORACLE,12C,UNIX)
REGEXP_SUBSTR [message #666623] Fri, 17 November 2017 09:16 Go to next message
pstanand
Messages: 117
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 Go to previous messageGo to next message
pstanand
Messages: 117
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 #666625 is a reply to message #666624] Fri, 17 November 2017 09:58 Go to previous messageGo to next message
BlackSwan
Messages: 25751
Registered: January 2009
Location: SoCal
Senior Member
use TRANSLATE() function to replace "Cpy " with NULL string
Re: REGEXP_SUBSTR [message #666626 is a reply to message #666623] Fri, 17 November 2017 10:05 Go to previous messageGo to next message
Michel Cadot
Messages: 65255
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
Re: REGEXP_SUBSTR [message #666627 is a reply to message #666625] Fri, 17 November 2017 10:21 Go to previous message
pstanand
Messages: 117
Registered: February 2005
Location: Chennai,India
Senior Member
Hi many thanks. As suggested I used translate and got the required value.

Previous Topic: PL/SQL: SQL Statement Ignored
Next Topic: REST API with DDOS protection
Goto Forum:
  


Current Time: Sat Dec 16 16:58:15 CST 2017

Total time taken to generate the page: 0.00830 seconds