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

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?

CodePudding user response：

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\test\document.txt'

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

Results

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

CodePudding user response：

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, '\\', '\'), '\', '\\')