Replace character if not preceeded by same character-CodePudding

I'm trying to escape some backslash's by replacing a single \ with \\:

UPDATE Table
SET Column = REPLACE(Column , '\', '\\')

But I would like to do the replace only if the backslash is not precedent by another backslash, so that input test\ should replace to test\\ but test\\ should stay test\\.

If more than two backslash are present it shouldn't replace either.

CodePudding user response：

T-SQL doesn't support pattern replacements, so you'll need to use a few nested REPLACEs here; the first to replace the double slashes (\\) to something that doesn't appear in your data (I use a pipe (|)), the second to replace the single slashes, and then finally a 3rd to replace the pipes back to double slashes.

UPDATE dbo.[Table]
SET [Column] = REPLACE(REPLACE(REPLACE([Column], '\\', '|'),'\','\\'),'|','\\');

If you have a value like 'test\\\' this will end up as 'test\\\\', as the first 2 \ characters would not be replaced, but the latter would.

As I am a "glutton for punishment", if you do need to avoid the above scenario of '\\\' being replaced to '\\\\' and (because you don't mention) could have multiple instances of single slashes in your values, you would have to do something like this.

CREATE TABLE dbo.[Table] (ID int IDENTITY, --As I assume you have some kind of ID column
                         [Column] varchar(100));
GO
INSERT INTO dbo.[Table] ([Column])
VALUES ('test\'),
       ('sample\\'),
       ('example\\\'),
       ('Another\example'),
       ('A\harder\\example\'),
       ('A\far\\more\complex\\example\\\than\is\\needed\');
GO
SELECT *
FROM dbo.[Table];
GO
WITH rCTE AS(
    SELECT T.ID,
           T.[Column],
           PI.I,
           STUFF(T.[Column],PI.I 1,0,'\') AS NewColumn,
           1 AS Iteration
    FROM dbo.[Table] T
         CROSS APPLY (VALUES(PATINDEX('%[^\]\[^\]%',[Column])))PI(I)
    UNION ALL
    SELECT r.ID,
           r.[Column],
           PI.I,
           LEFT(r.NewColumn,r.I 1)   STUFF(S.StuffColumn,PI.I 1,0,'\') AS NewColumn,
           r.Iteration 1 AS Iteration
    FROM rCTE r
         CROSS APPLY (VALUES(STUFF(r.NewColumn,1,r.I 1,'')))S(StuffColumn)
         CROSS APPLY (VALUES(PATINDEX('%[^\]\[^\]%',StuffColumn)))PI(I)
    WHERE PI.I > 0)
UPDATE T
SET [Column] = CASE WHEN r.NewColumn LIKE '%[^\]\' THEN r.NewColumn   '\' ELSE r.NewColumn END
FROM dbo.[table] T
     CROSS APPLY (SELECT TOP (1)
                         CASE WHEN r.NewColumn LIKE '%[^\]\' THEN r.NewColumn   '\' ELSE r.NewColumn END AS NewColumn
                  FROM rCTE r
                  WHERE r.ID = T.ID
                  ORDER BY r.Iteration DESC) r;
GO

SELECT *
FROM dbo.[Table];

Here we have to use a recursive CTE to iterate through the value, and each time it find a slash (\) that doesn't have another slash adjacent to it it injects another into the string. Then, as that doesn't work at the end of the string, you would need to use a CASE expression to add the value the extra slash at the end. You'd also need to do this for the start (but I haven't done that here).

db<>fiddle