Regexp_substr

[sandy_soft]

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

[ebrian]

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

[hrishy]

Hi Ebrian


Can you please explain your regular expression

[ebrian]

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

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

'[^.]+'  --> 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.

[hrishy]

Hi

You rock ebrian

[PAVB]

Nice! Thank you ebrian.

[sandy_soft]

Thanks, ebrian.


Its Rocking