Menu
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.
492
You can use the SUBSTR operator to get a substring from a field. For example, to get bytes 3-7 of a field called MYNAME, use the expression SUBSTR(MYNAME,3,5). You can update part of a character field by concatenating the parts you want to stay intact with the new value.
Update Part of a String in SQL Using the REPLACE() Function We could have done this simpler by using the REPLACE() function. It replaces all occurrences of a specified string value with another string value. The REPLACE function returns a string where it replaces a substring with another substring.
UPDATE table SET col = new_value WHERE col = old_value AND other_col = some_other_value; UPDATE table SET col = new_value WHERE col = old_value OR other_col = some_other_value; As you can see, you can expand the WHERE clause as much as you'd like in order to filter down the rows for updating to what you need.
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_\%' 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; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
