please suggest how to update column by replacing multiple '/' in single '/' in string in 10g

Question

IMAGE_PATH(its column name) Such type of value in column given below

 sph/images///////30_Fairhall_Court.jpeg
 sph/images///8_Furnival_Court.jpeg
 sph//images/9_Pennethorne_House.jpeg
 rbkc/images/TAVISTOCK_CRESCENT.jpeg
 haringey///images///399932thumb.jpg
 urbanchoice//images//18190862.jpg
 westminster/images//7_Glarus_Court.jpeg

I want to update all row of column where have more then one '/' like as

sph/images/30_Fairhall_Court.jpeg
 sph/images/8_Furnival_Court.jpeg
 sph/images/9_Pennethorne_House.jpeg
 rbkc/images/TAVISTOCK_CRESCENT.jpeg
 haringey/images/399932thumb.jpg
 urbanchoice/images/18190862.jpg
 westminster/images/7_Glarus_Court.jpeg

please suggest how to update column by replacing multiple '/' in single '/'

Answer 1

You can use regexp_replace for this.

select regexp_replace( 'sph/images///////30_Fairhall_Court.jpeg', '(/){2,}','\1' )
  from dual

sph/images/30_Fairhall_Court.jpeg

So your update statement would be something like the following:

update yourtable
set filename_column = regexp_replace( filename_column, '(/){2,}','\1' )
where instr(filename_column,'//')>0