UPDATE and REPLACE part of a string

Question

I've got a table with two columns, ID and Value. I want to change a part of some strings in the second column.

Example of Table:

ID            Value
---------------------------------
1             c:\temp\123\abc\111
2             c:\temp\123\abc\222
3             c:\temp\123\abc\333
4             c:\temp\123\abc\444

Now the 123\ in the Value string is not needed. I tried UPDATE and REPLACE:

UPDATE dbo.xxx
SET Value = REPLACE(Value, '%123%', '')
WHERE ID <= 4

When I execute the script SQL Server does not report an error, but it does not update anything either. Why is that?

It does not replace anything because the wildcards are not treated as wildcards but rather as literals. — stuhpa
– stuhpa, Commented Oct 24, 2016 at 10:36

Mauricio Gracia Gutierrez · Accepted Answer · 2021-01-20 23:27:34Z

1000

You don't need wildcards in the REPLACE - it just finds the string you enter for the second argument, so the following should work:

UPDATE dbo.xxx
SET Value = REPLACE(Value, '123', '')
WHERE ID <=4

If the column to replace is type text or ntext you need to cast it to nvarchar

UPDATE dbo.xxx
SET Value = REPLACE(CAST(Value as nVarchar(4000)), '123', '')
WHERE ID <=4

edited Jan 20, 2021 at 23:27

Mauricio Gracia Gutierrez

11k7 gold badges81 silver badges115 bronze badges

answered Jun 28, 2013 at 12:57

Jon Egerton

41.8k11 gold badges100 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Owidat Over a year ago

It work grate, but not with ntext type :( ...>> Msg 8116, Level 16, State 1, Line 21 - Argument data type ntext is invalid for argument 1 of replace function.

Owidat Over a year ago

I just found the solution :) ===> stackoverflow.com/questions/4341613/…

Meekohi Over a year ago

Wait, what is with the `? isn't that escaping the '` character and making this incorrect?

Stanley Okpala Nwosa Over a year ago

This was helpful

Adil H. Raza Over a year ago

This will not work if your Column type is Text or NText, see this answer stackoverflow.com/questions/4341613/…

|

Robert · Accepted Answer · 2013-06-28 12:56:36Z

79

Try to remove % chars as below

UPDATE dbo.xxx
SET Value = REPLACE(Value, '123', '')
WHERE ID <=4

answered Jun 28, 2013 at 12:56

Robert

25.8k8 gold badges70 silver badges86 bronze badges

Comments

xinux · Accepted Answer · 2016-10-12 09:03:39Z

70

To make the query run faster in big tables where not every line needs to be updated, you can also choose to only update rows that will be modified:

UPDATE dbo.xxx
SET Value = REPLACE(Value, '123', '')
WHERE ID <= 4
AND Value LIKE '%123%'

edited Oct 12, 2016 at 9:03

answered Feb 6, 2014 at 16:08

xinux

1,03710 silver badges14 bronze badges

Comments

Parfait · Accepted Answer · 2017-07-23 00:35:19Z

65

query:

UPDATE tablename 
SET field_name = REPLACE(field_name , 'oldstring', 'newstring') 
WHERE field_name LIKE ('oldstring%');

edited Jul 23, 2017 at 0:35

Parfait

108k19 gold badges103 silver badges138 bronze badges

answered Jul 22, 2017 at 11:49

maneesh

6595 silver badges2 bronze badges

Comments

RASKOLNIKOV · Accepted Answer · 2014-02-20 07:52:11Z

10

You have one table where you have date Code which is seven character something like

"32-1000"

Now you want to replace all

"32-"

With

"14-"

The SQL query you have to run is

Update Products Set Code = replace(Code, '32-', '14-') Where ...(Put your where statement in here)

answered Feb 20, 2014 at 7:52

RASKOLNIKOV

7682 gold badges9 silver badges22 bronze badges

Comments

ManhNguyen · Accepted Answer · 2018-10-24 06:36:23Z

8

For anyone want to replace your script.

update dbo.[TABLE_NAME] set COLUMN_NAME= replace(COLUMN_NAME, 'old_value', 'new_value') where COLUMN_NAME like %CONDITION%

edited Oct 24, 2018 at 6:36

answered May 18, 2017 at 8:40

ManhNguyen

1211 silver badge5 bronze badges

2 Comments

RBT Over a year ago

From review queue: May I request you to please add some context around your source-code. Code-only answers are difficult to understand. It will help the asker and future readers both if you can add more information in your post.

Tim Kretschmer Over a year ago

@RBT this is why reddit has the mad memes about SO

Mike Clark · Accepted Answer · 2015-02-03 11:39:31Z

1

CREATE TABLE tbl_PersonalDetail
(ID INT IDENTITY ,[Date] nvarchar(20), Name nvarchar(20), GenderID int);

INSERT INTO Tbl_PersonalDetail VALUES(N'18-4-2015', N'Monay', 2),
                                     (N'31-3-2015', N'Monay', 2),
                                     (N'28-12-2015', N'Monay', 2),
                                     (N'19-4-2015', N'Monay', 2)

DECLARE @Date Nvarchar(200)

SET @Date = (SELECT [Date] FROM Tbl_PersonalDetail WHERE ID = 2)

Update Tbl_PersonalDetail SET [Date] = (REPLACE(@Date , '-','/')) WHERE ID = 2

edited Feb 3, 2015 at 11:39

answered Feb 3, 2015 at 11:26

Mike Clark

1,88015 silver badges22 bronze badges

1 Comment

Aziz Over a year ago

Please explain how your answer solves the problem, it will help everyone understand your solution with more clarity and for future reference.

Vasyl Senko · Accepted Answer · 2017-03-30 13:26:55Z

1

you should use the below update query

UPDATE dbo.xxx SET Value=REPLACE(Value,'123\','') WHERE Id IN(1, 2, 3, 4)

UPDATE dbo.xxx SET Value=REPLACE(Value,'123\','') WHERE Id <= 4

Either of the above queries should work.

edited Mar 30, 2017 at 13:26

Vasyl Senko

1,81923 silver badges35 bronze badges

answered Jan 6, 2017 at 11:56

DotnetCoder

131 silver badge2 bronze badges

Comments

mirazimi · Accepted Answer · 2019-10-21 11:10:20Z

0

replace for persian word

UPDATE dbo.TblNews
SET keyWords = REPLACE(keyWords, '-', N'،')

help: dbo.TblNews -- table name

keyWords -- fild name

edited Oct 21, 2019 at 11:10

answered Oct 10, 2019 at 11:49

mirazimi

90611 silver badges11 bronze badges

Collectives™ on Stack Overflow

UPDATE and REPLACE part of a string

9 Answers 9

6 Comments

Comments

Comments

Comments

Comments

2 Comments

1 Comment

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

9 Answers 9

6 Comments

Comments

Comments

Comments

Comments

2 Comments

1 Comment

Comments

Comments

Linked

Related