This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Sep 9, 2008 9:54 AM by Aketi Jyuuzou RSS

return all the occurrences of a substring

658834 Newbie
Currently Being Moderated
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 Guru Moderator
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    RobvanWijk Oracle ACE
    Currently Being Moderated
    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
    60660 Journeyer
    Currently Being Moderated
    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
    RobvanWijk Oracle ACE
    Currently Being Moderated
    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
    60660 Journeyer
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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
    60660 Journeyer
    Currently Being Moderated
    Come on, you're the Master of PL/SQL exceptions, so there ... ;-)

    C.
  • 13. Re: return all the occurrences of a substring
    RobvanWijk Oracle ACE
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points