26

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.

Darren
  • 68,902
  • 24
  • 138
  • 144
user2547340
  • 325
  • 3
  • 6
  • 13

3 Answers3

30
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_\%'
Darren
  • 68,902
  • 24
  • 138
  • 144
  • 1
    `Name LIKE 'ZAA\%'` would at least allow an index (if one exists) to find rows to update. – Damien_The_Unbeliever Sep 02 '13 at 10:34
  • Didn't really work as expected. The results are now: ZAA_\. I'd like to just display ZAA_. Thanks – user2547340 Sep 02 '13 at 10:39
  • @user2547340 - looks like an off by 1 error. I've updated my answer to include the query you need. – Darren Sep 02 '13 at 10:43
  • 1
    Thanks very much for your help. Worked a treat – user2547340 Sep 02 '13 at 10:50
  • 1
    This is what worked for me. For MySQL change, `LEN` to `LENGTH`, and the second parameter of `SUBSTRING` is the starting offset, so it should be one more than the number of characters to strip. So, to remove 4 characters, this value would be 5. – 5hahiL Apr 16 '14 at 06:46
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;
Romesh
  • 2,291
  • 3
  • 24
  • 47
2

MYSQL:

UPDATE Meter
SET Name = CONCAT('ZAA' ,'_', SUBSTRING(Name, 4, LENGTH(Name)))
WHERE Name LIKE 'ZAA\%'
Linga
  • 10,379
  • 10
  • 52
  • 104
user2823361
  • 101
  • 5