Update substring of a column

Question

I have a table within a SQL Server 2008 database called Meter. This table has a column called Name.

Each entry within the column Name has a prefix of the following ZAA\. I'd like to change this prefix to ZAA_ without affecting the rest of the text within the column.

Answer 1

UPDATE Meter SET Name = 'ZAA_' + SUBSTRING(Name, 4, LEN(Name)) WHERE SUBSTRING(Name, 1, 4) = 'ZAA\' 

Edit:

Or as @Damien_The_Unbliever states, to use an index:

UPDATE Meter SET Name = 'ZAA_' + SUBSTRING(Name, 4, LEN(Name)) WHERE Name LIKE 'ZAA\%' 

EDIT

From your comment, try this statement to fix the additional \:

UPDATE Meter SET Name = 'ZAA_' + SUBSTRING(Name, 5, LEN(Name)) WHERE Name LIKE 'ZAA_\%' 

Answer 2

Here is the SQLFiddel Demo

Below is the Query which you can try

CREATE TABLE Meter     ([Name] varchar(7)) ;  INSERT INTO Meter     ([Name]) VALUES     ('ZAA\001') ;   select * from Meter;  Update Meter    set Name = stuff(Name,4,1,'_')  Where SUBSTRING(Name, 1,4) ='ZAA' + Char(92);  select * from Meter;