SUNAND CH SUNAND CH - 2 months ago 8
SQL Question

How to update a string in middle of it in sql which was repeated twice

If we have a string like below:

//aaaaa/bbbb/ccccc//ddddd/e/fgh.docx


I want to remove the second occurrence of '
//
'.

Please suggest me a sql statement to update this existing string by removing second occurrence of this '//'.

Assume the above string is there in column
file_path
, and having unique id associated with it in a row like

doc_id file_path
1 //aaaaa/bbbb/ccccc//ddddd/e/fgh.docx

Answer

Use REPLACE function.

Something like:

SELECT '/' + REPLACE(file_path, '//', '/') FROM <table>

It would work in SQL Server. Check for similar function in your RDBMS.