-1

Let's say I have following string: a=A#abc=Y#sps=Y# in some field of the table. I want to query for the a and get A with this query:

select UPPER(REGEXP_SUBSTR(REGEXP_SUBSTR(
    'a=Y#abc=Y#sps=Y#' , 
    'a\=([^#]+)#?'), '[[:alpha:]]')) from dual;

I get :

a
---------------
N
1 row selected
Mishti
  • 47
  • 6
  • Welcome to Stackoverflow! To get the most out of the site it is important to ask good questions. A guide to asking questions is at: http://stackoverflow.com/help/how-to-ask – Stephen Rauch Jan 18 '17 at 00:22

1 Answers1

0

You may need a single REGEXP_SUBSTR:

SQL> select regexp_substr(s,'(nonExcludableInd=)([^#]*)', 1, 1, '', 2)
  2  from (
  3          select 'nonExcludableInd=ABCD#includePrstInd=Y#cpeInd=Y#' as s from dual
  4       );

REGE
----
ABCD

A solution without regexp could be:

select substr(s, startPosition, instr(s, '#', startPosition ) - startPosition)
from ( 
       select instr(s,'nonExcludableInd=')+17 as startPosition, s
       from (
                select 'nonExcludableInd=A#includePrstInd=Y#cpeInd=Y#' as s from dual
             )
     )
Aleksej
  • 22,443
  • 5
  • 33
  • 38
  • Thanks. It worked but suppose i have multiple characters in place of 'A' and i need to extract all, then? – Mishti Jan 19 '17 at 16:21
  • the one without regexp works for all but one with regexp_substr only extract 'A'. I needed a solution for regexp_substr. – Mishti Jan 19 '17 at 16:55
  • Said that I don't understand why you need to use regexp even when you can avoid them, the solution with regexp even works with strings like 'ABC'. Just edited to show. The only issue can be with strings containing #, but this would need some clarification on how to handle such situations. – Aleksej Jan 19 '17 at 17:00
  • Okay i see now.. There was some issue with data on my side so was not getting it. Now i see. Thanks! Its just that i dont want multiple selects so using regexp. – Mishti Jan 19 '17 at 17:32
  • If the answer is right for you, please accept it. [Here](http://stackoverflow.com/help/someone-answers) you find something on what to do when someone answers you. – Aleksej Jan 21 '17 at 16:01