Regex Expression in single SQL query

Question

If an input string is in the format String#String or String#String;String#String where String can have any number of Capital letter alphabets, underscore,then it should return Y else it should return N.

How can it be done using Regex in PL/SQL only in single SQL query?
(P.S. not using PL/SQL procedures/function.)

Example:- If input string is "CASISA#Y" or "INVOPT#LUMREG;LUMSUM#2000;REGSUM#8000", a 'Y' should be returned else an 'N' should be returned. Note - Please do not build a PL/SQL procedure to accomplish this instead it should be just a SQL that makes use of oracle Regular expression functions.*

Could you clarify your question a little bit? Provide an example string and indicate the substrings you are trying to match. What have you tried already? — woemler
– woemler, Commented Jan 22, 2013 at 17:39

Nick Krasnov · Accepted Answer · 2013-01-22 18:03:20Z

2

You can use regexp_like regular expression function to achieve desired result:

with t1(col) as(
  select 'CASISA#Y'                              from dual union all
  select 'INVOPT#LUMREG;LUMSUM#2000;REGSUM#8000' from dual union all
  select 'CASISA#Y;'                             from dual union all -- extra data
  select 'INVOPT#LUMREG;LUMSUM#2000;#8000'       from dual           -- extra data
)
select col
     , case
         when regexp_like(col, '^(\w+#\w+;)*(\w+#\w+){1}$')
         then 'Y'
         else 'N'
       end  as "Y/N"
  from t1

Result:

COL                                        Y/N
--------------------------------------------------
CASISA#Y                                    Y
INVOPT#LUMREG;LUMSUM#2000;REGSUM#8000       Y
CASISA#Y;                                   N
INVOPT#LUMREG;LUMSUM#2000;#8000             N

SQLFiddle example

edited Jan 22, 2013 at 18:03

answered Jan 22, 2013 at 17:57

Nick Krasnov

27.3k6 gold badges66 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Regex Expression in single SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related