Home » SQL & PL/SQL » SQL & PL/SQL » regexp_substr (12c)
regexp_substr [message #667398] Tue, 26 December 2017 23:11 Go to next message
m.abdulhaq
Messages: 238
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




Re: regexp_substr [message #667399 is a reply to message #667398] Wed, 27 December 2017 00:22 Go to previous messageGo to next message
Littlefoot
Messages: 21325
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>
Re: regexp_substr [message #667401 is a reply to message #667399] Wed, 27 December 2017 00:37 Go to previous messageGo to next message
m.abdulhaq
Messages: 238
Registered: April 2013
Location: Ajman
Senior Member
Thanks littlefoot.
Re: regexp_substr [message #667442 is a reply to message #667399] Thu, 28 December 2017 17:31 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8789
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.
Re: regexp_substr [message #667447 is a reply to message #667399] Fri, 29 December 2017 08:20 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2697
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2697
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.
Re: regexp_substr [message #667450 is a reply to message #667448] Fri, 29 December 2017 10:57 Go to previous message
Littlefoot
Messages: 21325
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.
Previous Topic: mysql and oracle issue using dblink
Next Topic: Cant use replication from SQL Server 2008 to Sybase?
Goto Forum:
  


Current Time: Tue Jan 16 16:13:55 CST 2018

Total time taken to generate the page: 0.02803 seconds