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	est\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?

user_1570000 · Answer

Here is a simple technique Gordon Linoff demonstrated some time ago. (can't recall the original post)

It will handle any number of repeating characters. In this case \

In short, it expands, eliminates and finally normalizes.

Example

Declare @S varchar(150) = 'C:\Users\Temp\filepath	est\document.txt'

Select replace(replace(replace(@S,'\','†‡'),'‡†',''),'†‡','\')

Results

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

user_12637193 · Answer

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

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