I want fetch substring from in oracle table between last '/' and before '.' from last in images table

Question

I want to fetch substring from string in column between last '/' and last '.' .

Here is sample date for IMAGE_PATH column name:

 sph/images/30_Fairhall_Court.jpeg
 sph/images/9_Pennethorne_House.jpeg
 rbkc/images/TAVISTOCK_CRESCENT.jpeg
 haringey/images/399932thumb.jpg
 urbanchoice/images/18190862.jpg
 wandle/images/f13c10d2-2692-457d-a208-8bb9e10b27dc.png
 housingmoves/images/No14_Asterid Heights_DS37620.jpg
 wandle/images/f13c10d2-2692-457d-a208-8bb9e10b27dc.png

So the required output is like

 30_Fairhall_Court
 9_Pennethorne_House
 TAVISTOCK_CRESCENT
 399932thumb
 18190862
 f13c10d2-2692-457d-a208-8bb9e10b27dc
 No14_Asterid Heights_DS37620
 f13c10d2-2692-457d-a208-8bb9e10b27dc

Please suggest how to fetch. I need to update another blank column in table with this value. The table has around 10 lacks records.

APC · Answer 1 · 2015-04-01T18:22:54.673

This regex works with the sample data you provided:

select regexp_substr(image_path
            , '(/)([a-z0-9_ \-]+)(\.)([a-z]+)$'
            , 1
            , 1
            , 'i'
            , 2) 
from t23
/

We have to include all the optional parameters after pattern so we can use the subexpr parameter to select just the filename element. Find out more.

As far as the updating goes, a million row table isn't that big. Given that you have to update all the rows there's not much you can do to tune it. Just issue the UPDATE statement and let it rip.

"its not working"

Hmmm, here's a SQL Fiddle which proves it does work. You've probably introduced a typo.

"The regexp looks unnecessary complex. Why not simply"

Perhaps it is too complicated. However your simplified version doesn't produce the correct result if there's more than one dot in the IMAGE_PATH. If that's never going to happen then your solution works just fine.

The regexp looks unnecessary complex. Why not simply: `([^/]+)\..+$` — user272735, Apr 01 '15 at 08:40
@AnujKumarKushwaha - "It's not working" isn't very informative. — APC, Apr 01 '15 at 18:17

score 0 · Answer 2 · answered Apr 01 '15 at 09:52

One of possible solutions is to use functions substr() and instr() with negative third parameter:

select image_path, 
    substr(image_path, 
           instr(image_path, '/', -1) + 1, 
           instr(image_path, '.', -1)-instr(image_path, '/', -1) - 1) img
  from test

SQL Fiddle

Results:

IMAGE_PATH                                                IMG
--------------------------------------------------------  -------------------------------------
sph/images/30_Fairhall_Court.jpeg                         30_Fairhall_Court
sph/images/9_Pennethorne_House.jpeg                       9_Pennethorne_House
rbkc/images/TAVISTOCK_CRESCENT.jpeg                       TAVISTOCK_CRESCENT
haringey/images/399932thumb.jpg                           399932thumb
urbanchoice/images/18190862.jpg                           18190862
wandle/images/f13c10d2-2692-457d-a208-8bb9e10b27dc.png    f13c10d2-2692-457d-a208-8bb9e10b27dc
housingmoves/images/No14_Asterid Heights_DS37620.jpg      No14_Asterid Heights_DS37620
wandle/ima.ges/f13c10d2-2692-457d-a208-8bb9e10b27dc.png   f13c10d2-2692-457d-a208-8bb9e10b27dc

I want fetch substring from in oracle table between last '/' and before '.' from last in images table

2 Answers2