From: Ibo on
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.










From: Ibo on

Found it :

REGEXP_SUBSTR('owner.obj.part', '[^.]+', 1, <fieldno>)