1 2 Previous Next 17 Replies Latest reply: Sep 9, 2008 11:54 AM by Aketi Jyuuzou RSS

    return all the occurrences of a substring

    658834
      hi,
      I have a problem with returning data from a list.
      i can contain on the field values like (going after MTC):

      * MOCS13;MTCIN13;MTCIN14;100IN14;111IN14;123IN14
      * MTCIN13;MTCIN14;100IN14;111IN14;123IN14
      * MOCS13;MTCIN13;MTCIN14
      * MOCS13;M100IN14;111IN14;123IN14
      * ...


      * MTCXXX can have different size and can be anywhere in the field.
      * The information cames from a list, so it is ordered, if there is more then one MTC it will came in the front.
      * "MTC" exists always;


      first i only need to check if the information existed, so i made this:
      DECODE(REGEXP_SUBSTR(UPPER(CONCAT(';',List)),UPPER(';mtc^;+')),null,'FALSE','TRUE') AS MTC

      and it was OK

      then it was asked to me to return the complete value and i made this:
      NVL(SUBSTR(REGEXP_SUBSTR(UPPER(CONCAT(';',List)),UPPER(';mtc;+')),LENGTH(';mtc')-2,LENGTH(REGEXP_SUBSTR(CONCAT(';',List),';mtc;+'))),' ') AS MTC

      now that i made this i was told that there can be more than one occurrence and that i must return all the values!
      i ordered the list to make this easy but now I'm not being able to get the right expression to get the complete set of values!
      can someone help me?

      thank you,
      Ricardo Tomás
        • 1. Re: return all the occurrences of a substring
          BluShadow
          Here's a starter...
          SQL> ed
          Wrote file afiedt.buf
          
            1  with t as (select 'MOCS13;MTCIN13;MTCIN14;100IN14;111IN14;123IN14' as list from dual)
            2  select ltrim(REGEXP_SUBSTR(';'||list,';[[:alnum:]]*',1,rn),';') as MTCs
            3  from t, (select rownum rn from dual connect by rownum <= (select length(regexp_replace(list,'[^;]'))+1 from t))
            4* where ltrim(REGEXP_SUBSTR(';'||list,';[[:alnum:]]*',1,rn),';') like 'MTC%'
          SQL> /
          
          MTCS
          -----------------------------------------------
          MTCIN13
          MTCIN14
          
          SQL>
          • 2. Re: return all the occurrences of a substring
            BluShadow
            And if you're looking to get the same sort of output as input...
            SQL> ed
            Wrote file afiedt.buf
            
              1  with t as (select 'MOCS13;MTCIN13;MTCIN14;100IN14;111IN14;123IN14' as list from dual union all
              2             select 'BOB13;MTCIN23;123IN14' from dual union all
              3             select 'MTCIN11;123IN14;MTC123X' from dual
              4            )
              5      ,t2 as (select list, row_number() over (partition by list order by 1) as pos
              6              from t, (select rownum from dual connect by rownum <= 100)) -- assumes never more than 100 entries in list
              7  select list, ltrim(sys_connect_by_path(mtcs,';'),';') as mtcs
              8  from (
              9    select list, ltrim(REGEXP_SUBSTR(';'||list,';[[:alnum:]]*',1,pos),';') as MTCs, row_number() over (partition by list order by 1) rn
             10    from t2
             11    where ltrim(REGEXP_SUBSTR(';'||list,';[[:alnum:]]*',1,pos),';') like 'MTC%'
             12    )
             13  where connect_by_isleaf = 1
             14  connect by list = prior list and rn = prior rn+1
             15* start with rn = 1
            SQL> /
            
            LIST                                           MTCS
            ---------------------------------------------- ------------------------------
            BOB13;MTCIN23;123IN14                          MTCIN23
            MOCS13;MTCIN13;MTCIN14;100IN14;111IN14;123IN14 MTCIN13;MTCIN14
            MTCIN11;123IN14;MTC123X                        MTCIN11;MTC123X
            
            SQL>
            • 3. Re: return all the occurrences of a substring
              658834
              hi,
              thank you for the answers, but i have a problem.
              I'm not getting the same results. In fact I'm not getting any results :S

              Can it be because of oracle versions?
              my oracle database is 10.2.0.4.

              I'm suspecting of a problem with [:alnum:] because i was running some examples from oracle webpage with ispunct and it was completely different from the expected result.

              thanks,
              Ricardo Tomás
              • 4. Re: return all the occurrences of a substring
                BluShadow
                rctomas wrote:
                hi,
                thank you for the answers, but i have a problem.
                I'm not getting the same results. In fact I'm not getting any results :S

                Can it be because of oracle versions?
                my oracle database is 10.2.0.4.
                Not likely, I'm on 10.2.0.1

                >
                I'm suspecting of a problem with [:alnum:] because i was running some examples from oracle webpage with ispunct and it was completely different from the expected result.
                Don't see why alnum should be a problem.

                Post the code you are actually running here and we can see what you're doing differently... (use {noformat}
                {noformat} tags or {noformat}[code] and [/code]{noformat} tags around your code to keep the formatting.)
                
                ;)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
                • 5. Re: return all the occurrences of a substring
                  658834
                  that was the problem!
                  if i use like you it doesn't work and if i place:
                   
                      with t as (select 'MOCS13;MTCIN13;MTCIN14;100IN14;111IN14;123IN14' as list from dual union all
                                 select 'BOB13;MTCIN23;123IN14' from dual union all
                               select 'MTCIN11;123IN14;MTC123X' from dual
                              )
                      --with t as (select IN_SERVICE_NAMES as list from hls_out)
                       ,t2 as (select list, row_number() over (partition by list order by 1) as pos
                                  from t, (select rownum from dual connect by rownum <= 100)) -- assumes never more than 100 entries in list
                      select list, ltrim(sys_connect_by_path(mtcs,';'),';') as mtcs
                      from (
                        select list, ltrim(REGEXP_SUBSTR(';'||list,';[a-zA-Z0-9]*',1,pos),';') as MTCs, row_number() over (partition by list order by 1) rn
                       from t2
                       where ltrim(REGEXP_SUBSTR(';'||list,';[a-zA-Z0-9]*',1,pos),';') like 'MTC%'
                       )
                     where connect_by_isleaf = 1
                     connect by list = prior list and rn = prior rn+1
                  start with rn = 1
                  so the only difference is really that one, i replaced :
                   
                  ';[:alnum:]*' by ';[a-zA-Z0-9]*'
                  thanks for the help.
                  By myself i had to spend a few days to get a solutions!
                  I will have to read a little to understand completely what you did!
                  i had never seen the connect clause!


                  best regards,
                  Ricardo Tomás
                  • 6. Re: return all the occurrences of a substring
                    Rob van Wijk
                    Or simpler:
                    SQL> create table mytable (list)
                      2  as
                      3  select 'MOCS13;MTCIN13;MTCIN14;100IN14;111IN14;123IN14' from dual union all
                      4  select 'MTCIN13;MTCIN14;100IN14;111IN14;123IN14' from dual union all
                      5  select 'MOCS13;MTCIN13;MTCIN14' from dual union all
                      6  select 'MOCS13;M100IN14;111IN14;123IN14' from dual union all
                      7  select 'MOCS13;MTCIN13;MTCIN14;100IN14;111IN14;123IN14' as list from dual union all
                      8  select 'BOB13;MTCIN23;123IN14' from dual union all
                      9  select 'MTCIN11;123IN14;MTC123X' from dual
                     10  /
                    
                    Table created.
                    
                    SQL> select list
                      2       , trim(';' from regexp_replace(';' || list || ';', ';([^M]|M[^T]|MT[^C])[^;]+')) mtc
                      3    from mytable
                      4  /
                    
                    LIST                                           MTC
                    ---------------------------------------------- ------------------------------------------------
                    MOCS13;MTCIN13;MTCIN14;100IN14;111IN14;123IN14 MTCIN13;MTCIN14
                    MTCIN13;MTCIN14;100IN14;111IN14;123IN14        MTCIN13;MTCIN14
                    MOCS13;MTCIN13;MTCIN14                         MTCIN13;MTCIN14
                    MOCS13;M100IN14;111IN14;123IN14
                    MOCS13;MTCIN13;MTCIN14;100IN14;111IN14;123IN14 MTCIN13;MTCIN14
                    BOB13;MTCIN23;123IN14                          MTCIN23
                    MTCIN11;123IN14;MTC123X                        MTCIN11;MTC123X
                    
                    7 rows selected.
                    Regards,
                    Rob.
                    • 7. Re: return all the occurrences of a substring
                      cd_2
                      A slightly shorter version:
                      select list
                           , LTRIM(REGEXP_REPLACE(list, '(^|;)(MTC[[:alnum:]]+)|.', '\1\2'), ';')
                          from t
                      ;
                      C.
                      • 8. Re: return all the occurrences of a substring
                        Rob van Wijk
                        cd, that's excellent, thanks!
                        I already suspected it could be even more simple.

                        Regards,
                        Rob.
                        • 9. Re: return all the occurrences of a substring
                          cd_2
                          Your's not bad either. I only wanted to find one, where the "logic" is a bit more "visible".

                          @rctomas: you did notice that you have to write
                          [[:alnum:]]
                          , right?

                          C.
                          • 10. Re: return all the occurrences of a substring
                            BluShadow
                            Rob van Wijk wrote:
                            Or simpler:
                            Damn it Rob, I was trying for ages to get them flippin' reg exp replace to do that just like that, but it wasn't working for me.

                            I guess that method is ok as long as you haven't got, say, a 25 character string you're checking, just think of all the individual little checks you would require. Eeek!


                            Nice one though.

                            ;)
                            • 11. Re: return all the occurrences of a substring
                              BluShadow
                              cd/ wrote:
                              A slightly shorter version:
                              select list
                              , LTRIM(REGEXP_REPLACE(list, '(^|;)(MTC[[:alnum:]]+)|.', '\1\2'), ';')
                              from t
                              ;
                              C.
                              Sheez, I'll get my coat.

                              :(
                              • 12. Re: return all the occurrences of a substring
                                cd_2
                                Come on, you're the Master of PL/SQL exceptions, so there ... ;-)

                                C.
                                • 13. Re: return all the occurrences of a substring
                                  Rob van Wijk
                                  Fortunately, with the new rating system, your solution will forever be noted as correct, and not our buggy one-liners ;-)
                                  • 14. Re: return all the occurrences of a substring
                                    BluShadow
                                    cd/ wrote:
                                    Come on, you're the Master of PL/SQL exceptions, so there ... ;-)

                                    C.
                                    ;)

                                    ... but I thought I'd cracked these damned regular expressions.
                                    1 2 Previous Next