OraFAQ Forum: SQL & PL/SQL » regexp

Home » SQL & PL/SQL » SQL & PL/SQL » regexp_substr (12c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

regexp_substr [message #667398]

Tue, 26 December 2017 23:11

m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman

Senior Member

Hello Experts,

Is there a method to extract second string from right in the following pattern.Any second string after ',' and in between quotes.


'AE-DXBAST-CTCWTCW','AE-DXBAST-CTCWT','AE-DXBAST-CT','AE-DXBAST','AE'

i need to extract AE-DXBAST portion.

'AE-DXBAST-CTCWTCW','AE-DXBAST-CTCWT','AE-DXBAST-CT','AE-ZXB','AE'

--In the above one it should be AE-ZXB

Report message to a moderator

Re: regexp_substr [message #667399 is a reply to message #667398]

Wed, 27 December 2017 00:22

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

It is simple enough without regular expressions.

SQL> with test
  2       as (select q'['AE-DXBAST-CTCWTCW','AE-DXBAST-CTCWT','AE-DXBAST-CT','AE-DXBAST','AE']'
  3                     col
  4             from dual
  5           union
  6           select q'['AE-DXBAST-CTCWTCW','AE-DXBAST-CTCWT','AE-DXBAST-CT','AE-ZXB','AE']'
  7             from dual)
  8  select
  9    col,
 10    substr(col,
 11           instr(col, ',', -1, 2) + 1,
 12           instr(col, ',', -1, 1) - instr(col, ',', -1, 2) - 1) result
 13  from test;

COL                                                                   RESULT
--------------------------------------------------------------------- ---------------
'AE-DXBAST-CTCWTCW','AE-DXBAST-CTCWT','AE-DXBAST-CT','AE-DXBAST','AE' 'AE-DXBAST'
'AE-DXBAST-CTCWTCW','AE-DXBAST-CTCWT','AE-DXBAST-CT','AE-ZXB','AE'    'AE-ZXB'

SQL>

Report message to a moderator

Re: regexp_substr [message #667401 is a reply to message #667399]

Wed, 27 December 2017 00:37

m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman

Senior Member

Thanks littlefoot.

Report message to a moderator

Re: regexp_substr [message #667442 is a reply to message #667399]

Thu, 28 December 2017 17:31

Barbara Boehmer
Messages: 9086
Registered: November 2002
Location: California, USA

Senior Member

SCOTT@orcl_12.1.0.2.0> with test
  2  	  as (select q'['AE-DXBAST-CTCWTCW','AE-DXBAST-CTCWT','AE-DXBAST-CT','AE-DXBAST','AE']'
  3  			col
  4  		from dual
  5  	      union
  6  	      select q'['AE-DXBAST-CTCWTCW','AE-DXBAST-CTCWT','AE-DXBAST-CT','AE-ZXB','AE']'
  7  		from dual)
  8  select col,
  9  	    regexp_substr (col, '[^'',]+', 1, regexp_count (col, ',')) result
 10  from   test
 11  /

COL                                                                   RESULT
--------------------------------------------------------------------- ---------------
'AE-DXBAST-CTCWTCW','AE-DXBAST-CTCWT','AE-DXBAST-CT','AE-DXBAST','AE' AE-DXBAST
'AE-DXBAST-CTCWTCW','AE-DXBAST-CTCWT','AE-DXBAST-CT','AE-ZXB','AE'    AE-ZXB

2 rows selected.

Report message to a moderator

Re: regexp_substr [message #667447 is a reply to message #667399]

Fri, 29 December 2017 08:20

Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA

Senior Member

Littlefoot wrote on Wed, 27 December 2017 01:22

It is simple enough without regular expressions.

Only with assumption comma can't appear between quotes.

SY.

[Updated on: Fri, 29 December 2017 08:21]

Report message to a moderator

Re: regexp_substr [message #667448 is a reply to message #667442]

Fri, 29 December 2017 08:28

Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA

Senior Member

Same way, by using regexp_count(col,',') you are making assumption comma can't appear between quotes.

with test as (
              select q'['AE-DXBAST-CTCWTCW','AE-DXBAST-CTCWT','AE-DXBAST-CT','AE-DXBAST','AE']' col from dual union all
              select q'['AE-DXBAST-CTCWTCW','AE-DXBAST-CTCWT','AE-DXBAST-CT','AE-ZXB','AE']' from dual
             )
select  col,
        regexp_substr(col,q'!'([^']+)','[^']+'$!',1,1,null,1) result
  from  test
/

COL                                                                   RESULT
--------------------------------------------------------------------- ---------
'AE-DXBAST-CTCWTCW','AE-DXBAST-CTCWT','AE-DXBAST-CT','AE-DXBAST','AE' AE-DXBAST
'AE-DXBAST-CTCWTCW','AE-DXBAST-CTCWT','AE-DXBAST-CT','AE-ZXB','AE'    AE-ZXB
SQL>

SY.

Report message to a moderator

Re: regexp_substr [message #667450 is a reply to message #667448]

Fri, 29 December 2017 10:57

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Right; well, all we know is what the OP has said and see examples he provided. Can there be a comma between quotes? Space? Snake? Nope, as far as we can tell.

Report message to a moderator

Previous Topic:	mysql and oracle issue using dblink
Next Topic:	Cant use replication from SQL Server 2008 to Sybase?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 13:17:12 CDT 2024