Home » SQL & PL/SQL » SQL & PL/SQL » REGEXP to find the comma position (Oracle 11.2.0.3)
REGEXP to find the comma position [message #660300] Tue, 14 February 2017 07:01 Go to next message
pointers
Messages: 443
Registered: May 2008
Senior Member
Hi,

I would like to find the position of 3rd comma (nth comma), but that does not include the comma in the double quotes.

e.g select regexp_instr('a,b,"c,d",e,f', ',' , 1,3) from dual

I would like to get the output as 10, because comma of "c,d" is actually data.

Thank you in advance.

Regards,
Pointers
Re: REGEXP to find the comma position [message #660306 is a reply to message #660300] Tue, 14 February 2017 07:43 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2806
Registered: January 2010
Location: Connecticut, USA
Senior Member
Something like:

select  regexp_instr('a,b,"c,d",e,f', '([^,]|("[^"]*")),',1,1,1) - 1 comma1,
        regexp_instr('a,b,"c,d",e,f', '([^,]|("[^"]*")),',1,2,1) - 1 comma2,
        regexp_instr('a,b,"c,d",e,f', '([^,]|("[^"]*")),',1,3,1) - 1 comma3,
        regexp_instr('a,b,"c,d",e,f', '([^,]|("[^"]*")),',1,4,1) - 1 comma4
  from  dual
/

    COMMA1     COMMA2     COMMA3     COMMA4
---------- ---------- ---------- ----------
         2          4         10         12

SQL> 

SY.
Re: REGEXP to find the comma position [message #660317 is a reply to message #660300] Tue, 14 February 2017 13:49 Go to previous message
pointers
Messages: 443
Registered: May 2008
Senior Member
Thank you so much Solomon.

You dont know how your solution helped me.

I am a big fan of your solutions.

Thanks a ton.

Regards,
Pointers
Previous Topic: How to identify one row from duplicate rows
Next Topic: natural join change table
Goto Forum:
  


Current Time: Mon Oct 22 22:27:27 CDT 2018