How to replace a backslash with a double backslash without replacing existing double backslashes in SQL

Question

I have been provided a path from an external source I have no control over and need to store the file path in my SQL Server database.

The file path will appear similar to the below;

C:\\Users\\Temp\filepath\test\document.txt

I need to store these with all double backslashes as such

C:\\Users\\Temp\\filepath\\test\\document.txt

What is the correct way to replace \ with \\ without turning the string into this

C:\\\\Users\\\\Temp\\filepath\\test\\document.txt

with a REPLACE call?

Answer 1

You can convert double to single and then single to double.

DECLARE @Path VARCHAR(100) = 'C:\\Users\\Temp\filepath\test\document.txt'
SELECT Replace(Replace(@Path, '\\', '\'), '\', '\\')