Regexp_substr

Hi,
i got object or schema name in following format:

owner[.object_name[.partition_name]]

This naming allows to define schemans, objects and partitions.

I have a sp_crackname procedure to extract fields form this name but i
think it is running slow when processign thousands of names.

I need to use REGEXP_SUBSTR to extract indivvidual fields.

Ex -1 :
objname = 'busdep'
REGEXP_SUBSTR (... , 1) should return 1. field which is 'busdep'

REGEXP_SUBSTR (... , 2) and REGEXP_SUBSTR (... , 3) should return
NULL.

Ex -1 :
objname = 'busdep.orders.part_1'
REGEXP_SUBSTR (... , 1) should return 'busdep'

REGEXP_SUBSTR (... , 2) should return 'orders' and REGEXP_SUBSTR
(... , 3) should return 'part_1'

Please let me know whats the fastest way to do this.

Regards
Sandeep

Code:

---

SQL> select objname,
  2    regexp_substr(objname, '[^.]*') owner,
  3    regexp_substr(objname, '[^.]*',1,3) obj_name,
  4    regexp_substr(objname, '[^.]*',1,5) part_name
  5  from obj;

OBJNAME                        OWNER        OBJ_NAME    PART_NAME
------------------------------ ------------ ------------ ------------
busdep                        busdep
busdep.orders                  busdep      orders
busdep.orders.part_1          busdep      orders      part_1

---

Hi Ebrian


Can you please explain your regular expression

In the above example I used '*', but actually for the OP's purposes, it probably would have been more appropriate to use '+' as follows:

Code:

---

SQL> select objname,
  2      regexp_substr(objname, '[^.]+') owner,
  3      regexp_substr(objname, '[^.]+',1,2) obj_name,
  4      regexp_substr(objname, '[^.]+',1,3) part_name
  5  from obj;

OBJNAME              OWNER        OBJ_NAME    PART_NAME
-------------------- ------------ ------------ ------------
busdep              busdep
busdep.orders        busdep      orders
busdep.orders.part_1 busdep      orders      part_1

---

This may be more understandable than the first regexp I posted. Each regex [^.]+ is simply looking for one or more characters that do NOT include a '.'

Code:

---

'[^.]+'  --> By default, SUBSTR will start at the first character and
                look for the first occurrence (group) of character(s),
                therefore I did not need include a 1,1 after this
                expression
'[^.]+',1,2  --> Starting at the first character of the string, look
                for the second occurrence (group) of character(s) that
                do NOT include a '.'
'[^.]+',1,3  --> Starting at the first character of the string, look
                for the third occurrence (group) of character(s) that
                do NOT include a '.'

---

In the original regex I posted, since I used a '*' (which indicates zero or more), the counts were different since it was including the '.' in the data as a hit.

Hope that helps.

Hi

You rock ebrian

Nice! Thank you ebrian.

Thanks, ebrian.


Its Rocking