6 Replies Latest reply on Jan 19, 2017 4:54 PM by mathguy

    regexp query

    user9974866

      hi ,

       

      i have a table with two records inserted below . i want to retrieve only the highlighted string in the output. there are many other records like this so i need to pull out the string starting with  'select'

      upto first brace i.e ')' .  is there any function or regexp in oracle??

       

      s.no                  type

      1          'COALESCE((SELECT DISTINCT TEAM_CODE from TEAM_FULL_NAME where INPUT_NAME=stage.favorite_team),'NF')'

      2          'CASE WHEN stage.record_source='XYZ' THEN stage.fav_team_cd ELSE (SELECT TEAM_CODE from TEAM_FULL_NAME where INPUT_NAME=stage.fav_team_cd) END'

       

      Expected  output

      s.no                         type

      1.  SELECT DISTINCT TEAM_CODE from TEAM_FULL_NAME where INPUT_NAME=stage.favorite_team

      2.  SELECT TEAM_CODE from TEAM_FULL_NAME where INPUT_NAME=stage.fav_team_cd

       

      thanks in advance.

        • 1. Re: regexp query
          Srinivas Vanahalli

          Try this ....

           

          select regexp_substr('COALESCE((SELECT DISTINCT TEAM_CODE from TEAM_FULL_NAME where INPUT_NAME=stage.favorite_team),''NF'')','SELECT[^)]+',1,1)

          from dual;

          • 2. Re: regexp query
            Solomon Yakobson

            with t as (

                      select Q'[COALESCE((SELECT DISTINCT TEAM_CODE from TEAM_FULL_NAME where INPUT_NAME=stage.favorite_team),'NF')]' str from  dual union all

                      select Q'[CASE WHEN stage.record_source='XYZ' THEN stage.fav_team_cd ELSE (SELECT TEAM_CODE from TEAM_FULL_NAME where INPUT_NAME=stage.fav_team_cd) END]' from  dual

                      )

            select  regexp_substr(

                                  str,

                                  '\(([^()]*)\)',

                                  1,

                                  1,

                                  null,

                                  1

                                ) s

              from  t

            /

             

            S

            -----------------------------------------------------------------------------------

            SELECT DISTINCT TEAM_CODE from TEAM_FULL_NAME where INPUT_NAME=stage.favorite_team

            SELECT TEAM_CODE from TEAM_FULL_NAME where INPUT_NAME=stage.fav_team_cd

             

            SQL>

             

            SY.

            • 3. Re: regexp query
              Billy~Verreynne

              Can brackets be used as SQL statement delimiters, or not?

               

              Can SQL for example have an IN clause, or an inline SQL statement?

               

              Parsing source code is seldom a simplistic job.

              • 4. Re: regexp query
                user9974866

                thank you all.. both the queries are perfectly working fine ..everything is fine but last one is creating problem as braces is required specifically in this record.. out of 3 records 2 are required without braces and 1 with brace..

                 

                 

                s.no                  type

                1          'COALESCE((SELECT DISTINCT TEAM_CODE from TEAM_FULL_NAME where INPUT_NAME=stage.favorite_team),'NF')'

                2          'CASE WHEN stage.record_source='XYZ' THEN stage.fav_team_cd ELSE (SELECT TEAM_CODE from TEAM_FULL_NAME where                                           INPUT_NAME=stage.fav_team_cd) END'

                3          '(SELECT TEAM_CODE from NFL_DW_OWNER.REF_TCS_TEAM_FULL_NAME where INPUT_NAME=TO_NUMBER(stage.team_cd))'

                • 5. Re: regexp query
                  Solomon Yakobson

                  Not possible with just regexp. This is similar to Matching ( in a string posted yesterday:

                   

                  with t as (

                            select Q'[COALESCE((SELECT DISTINCT TEAM_CODE from TEAM_FULL_NAME where INPUT_NAME=stage.favorite_team),'NF')]' str from  dual union all

                            select Q'[CASE WHEN stage.record_source='XYZ' THEN stage.fav_team_cd ELSE (SELECT TEAM_CODE from TEAM_FULL_NAME where INPUT_NAME=stage.fav_team_cd) END]' from  dual union all

                            select Q'[(SELECT TEAM_CODE from NFL_DW_OWNER.REF_TCS_TEAM_FULL_NAME where INPUT_NAME=TO_NUMBER(stage.team_cd))]' from  dual

                            ),

                      r(

                        str,

                        start_pos,

                        end_pos,

                        weight

                        ) as (

                              select  str,

                                      instr(str,'(SELECT') start_pos,

                                      instr(str,'(SELECT') end_pos,

                                      1 weight

                                from  t

                              union all

                              select  str,

                                      start_pos,

                                      end_pos + 1,

                                      weight + case substr(str,end_pos + 1,1)

                                                  when '(' then 1

                                                  when ')' then -1

                                                  else 0

                                                end

                                from  r

                                where weight != 0

                                  and end_pos <= length(str)

                            )

                  select  substr(str,start_pos + 1,end_pos - start_pos - 1) str

                    from  r

                    where weight = 0

                  /

                  STR

                  -----------------------------------------------------------------------------------------------------

                  SELECT TEAM_CODE from TEAM_FULL_NAME where INPUT_NAME=stage.fav_team_cd

                  SELECT DISTINCT TEAM_CODE from TEAM_FULL_NAME where INPUT_NAME=stage.favorite_team

                  SELECT TEAM_CODE from NFL_DW_OWNER.REF_TCS_TEAM_FULL_NAME where INPUT_NAME=TO_NUMBER(stage.team_cd)

                   

                  SQL>

                   

                  SY.

                  • 6. Re: regexp query
                    mathguy

                    It's worse than the topic discussed yesterday. In that topic, all parentheses played the same role. In this topic here (this thread), I assume the required output from

                     

                    case when ...  then (select id from some_table where some_column like '(%' and some_other_condition) else ....

                     

                    is 

                     

                    select id from some_table where some_column like '(%' and some_other_condition

                     

                    The  (  within a string literal will ruin everything we came up with in the other thread. We'll end up collecting too many characters. If instead there was a closing  )  in a string literal, we'd stop too soon.

                     

                    Cheers,   mathguy