Re: How to use REGEXP_SUBSTR to extract owner/obj/part from full obj name ?

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

Well, I'll preface this by saying I've not yet found a need to use RE in Oracle. In some languages, like perl, I use them quite a lot, but have not yet found a need in Oracle. I'm not saying they are not needed, only that I've personally not come across a situation where I couldn't get the outcome with much simpler string manipulation functions such as substr et al.

In general, if you can do what you want with other techniques that don't use backtracking etc, they will be faster than REs. Therefore, I would be tempted to try just using basic string search and substring commands rather than a RE in this case. You have a well defined token separator i.e. '.', so you should be able to extract the components very easily using instr and substr functions.

If you really want to use REs, one of the most important thing to do is make sure that the RE is well anchored as this will reduce the amount of backtracking that will occur. to anchor the RE, you need to tie it to some non-variaant part. this could be the start or end of the string or some sequence that is always there and always in the same position in the strings of interest.

The only other comment I have is to ask why have all three components in one string in the first place. If you need to do large amount of operations where you need individual components, maybe you should use something like an Oracle object type. You could store the individual components in their own separate variables within the object and could even define methods to extract each component and the full specification etc. Even simpler, just a table with the components as distinct fields.

While not e\meaining to criticise you specifically, in general, I find people often have a tendency to define fields that are really composites of other fields and then find they have processing issues because they need to manipulate the individual components separately more often than they need to manipulate the composite object. The way you select your data structures and design your data model should reflect how you need to manipulate the data objects rather than what looks neat or comfortable for humans to read.

Of course, sometimes, you don't have the luxury of doing things they way you want due to other restrictions outside your control. In such situations, you an only do wht you can

Tim

-- 
tcross (at) rapttech dot com dot au