Update column in table where a specific character appears-CodePudding

I'm trying to update a table in my database where a specific character appears but can't get it to work correctly. So where '/' appears add an additional '/'.

I have added my code below.

Here is the table that I want to update.

Table1

FirstName	LastName	Address
Sam	Smith	25 /Long Lane/ Surrey

Desired Output:

FirstName	LastName	Address
Sam	Smith	25 //Long Lane// Surrey

SQL that isn't working:

I have the below update statement which isn't working. It just adds '/' a the start of the column which isn't correct. It is not getting me the desired output.

update table 
set Address = '/'   Address
where Address like '%/%'

CodePudding user response：

Seems simple enough:
(Assuming Microsoft SQL Server)

UPDATE yourtable
SET Address = REPLACE(Address, '/', '//')
WHERE Address Like '%/%';

However, you should consider what happens if you run this against an already-escaped value. Each // will be replaced with ////.

CodePudding user response：

Why not REPLACE() instances of / with //…?

update table 
set Address = REPLACE(Address, '/', '//')