-
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.
-
-
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Refexp_substr
Thanks, ebrian.
Its Rocking
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|