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

Post a Comment

Popular posts from this blog

javascript - AngularJS custom datepicker directive -

i make custom datepicker directive custom template. but have no idea how start building it... how include date data directive? i appreciate guide or give me advice work on more precisely. it might you! follow below steps html code sample: <label>birth date</label> <input type="text" ng-model="birthdate" date-options="dateoptions" custom-datepicker/> <hr/> <pre>birthdate = {{birthdate}}</pre> <script type="text/ng-template" id="custom-datepicker.html"> <div class="enhanced-datepicker"> <div class="proxied-field-wrap"> <input type="text" ui-date-format="yy-mm-dd" ng-model="ngmodel" ui-date="dateoptions"/> </div> <label> <button class="btn" type="button"><i class="icon-calendar"></i>
Read more

javascript - jQuery date picker - Disable dates after the selection from the first date picker -

<!doctype html> <html lang="en"> <head> <meta charset="utf-8"> <title>jquery ui datepicker - default functionality</title> <link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css"> <script src="//code.jquery.com/jquery-1.10.2.js"></script> <script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script> <link rel="stylesheet" href="/resources/demos/style.css"> <script> $(function () { $("#datepicker1").datepicker(); }); </script> <script> $(function () { $("#datepicker2").datepicker(); }); </script> </head> <body> <p>date 1: <input type="text" id="datepicker1"></p> <p>date 2: <input type="t
Read more