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


i want fetch substring string in column between last '/' , last '.' .

here sample date 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 required output

 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 fetch. need update blank column in table value. table has around 10 lacks records.

one of possible solutions use functions substr() , instr() negative third parameter:

select image_path,      substr(image_path,             instr(image_path, '/', -1) + 1,             instr(image_path, '.', -1)-instr(image_path, '/', -1) - 1) img   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 

Comments

Popular posts from this blog

Payment information shows nothing in one page checkout page magento -

tcpdump - How to check if server received packet (acknowledged) -