return all the occurrences of a substring

658834 · Sep 9, 2008 7:09AM

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

BluShadow · Sep 9, 2008 9:16AM

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>

BluShadow · Sep 9, 2008 7:54AM

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 · Sep 9, 2008 9:16AM

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 · Sep 9, 2008 9:43AM

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 · Sep 9, 2008 10:00AM

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.)

;)

658834 · Sep 9, 2008 10:16AM

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 · Sep 9, 2008 10:32AM

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.

cd_2 · Sep 9, 2008 10:46AM

A slightly shorter version:

select list
     , LTRIM(REGEXP_REPLACE(list, '(^|;)(MTC[[:alnum:]]+)|.', '\1\2'), ';')
    from t
;

C.

Rob van Wijk · Sep 9, 2008 10:50AM

cd, that's excellent, thanks!
I already suspected it could be even more simple.

Regards,
Rob.

cd_2 · Sep 9, 2008 10:52AM

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.

BluShadow · Sep 9, 2008 10:53AM

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.

Forum Stats

Quick Links

Categories

return all the occurrences of a substring

Best Answer

Answers

Data

Databases

Development

Development Tools

Open Source

Programs

Forum Stats

Categories

return all the occurrences of a substring

Best Answer

Answers