Forum Stats

  • 3,733,252 Users
  • 2,246,738 Discussions
  • 7,856,634 Comments

Discussions

return all the occurrences of a substring

658834
658834 Member Posts: 6
edited September 2008 in SQL & PL/SQL
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

Best Answer

  • BluShadow
    BluShadow Member, Moderator Posts: 40,983 Red Diamond
    Accepted Answer
    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>

Answers

  • BluShadow
    BluShadow Member, Moderator Posts: 40,983 Red Diamond
    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>
    BluShadow
  • BluShadow
    BluShadow Member, Moderator Posts: 40,983 Red Diamond
    Accepted Answer
    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>
  • 658834
    658834 Member Posts: 6
    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
  • BluShadow
    BluShadow Member, Moderator Posts: 40,983 Red Diamond
    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.)
    
    ;)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
    BluShadow
  • 658834
    658834 Member Posts: 6
    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
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    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.
    Rob van Wijk
  • cd_2
    cd_2 Member Posts: 5,021
    A slightly shorter version:
    select list
         , LTRIM(REGEXP_REPLACE(list, '(^|;)(MTC[[:alnum:]]+)|.', '\1\2'), ';')
        from t
    ;
    C.
    cd_2
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    cd, that's excellent, thanks!
    I already suspected it could be even more simple.

    Regards,
    Rob.
  • cd_2
    cd_2 Member Posts: 5,021
    edited September 2008
    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.
    cd_2
  • BluShadow
    BluShadow Member, Moderator Posts: 40,983 Red Diamond
    edited September 2008
    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.

    ;)
  • BluShadow
    BluShadow Member, Moderator Posts: 40,983 Red Diamond
    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.

    :(
  • cd_2
    cd_2 Member Posts: 5,021
    edited September 2008
    Come on, you're the Master of PL/SQL exceptions, so there ... ;-)

    C.
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    Fortunately, with the new rating system, your solution will forever be noted as correct, and not our buggy one-liners ;-)
  • BluShadow
    BluShadow Member, Moderator Posts: 40,983 Red Diamond
    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.
  • cd_2
    cd_2 Member Posts: 5,021
    So did I. ;-) Still I'm learning something new every day (even regarding regular expressions).

    C.
  • 658834
    658834 Member Posts: 6
    thank you all!
    e learned a lot of things today with you.
    I never imagined it could be done with just one line!


    thanks,
    Ricardo Tomás
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    similar usage of regex.
    700680
This discussion has been closed.