Regexp_substr
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Regexp_substr

  1. #1
    Join Date
    May 2010
    Posts
    12

    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

  2. #2
    Join Date
    Apr 2006
    Posts
    377
    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

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Ebrian


    Can you please explain your regular expression

  4. #4
    Join Date
    Apr 2006
    Posts
    377
    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.

  5. #5
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    You rock ebrian

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Nice! Thank you ebrian.
    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.

  7. #7
    Join Date
    May 2010
    Posts
    12

    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
  •  



Click Here to Expand Forum to Full Width