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, Oct 24 '16 at 10:36

score 938 · Accepted Answer · edited Jan 20 '21 at 23:27

938

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 '21 at 23:27

Mauricio Gracia Gutierrez

10,288
6
68
99

answered Jun 28 '13 at 12:57

Jon Egerton

40,401
11
97
129

1

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 Dec 10 '14 at 02:24
8

I just found the solution :) ===> http://stackoverflow.com/questions/4341613/alternatives-to-replace-on-a-text-or-ntext-datatype – Owidat Dec 10 '14 at 02:26
3

Wait, what is with the `\`? isn't that escaping the `'` character and making this incorrect? – Meekohi Jan 25 '17 at 19:45
This was helpful – Stanley Okpala Nwosa Sep 20 '18 at 17:10
This will not work if your Column type is Text or NText, see this answer https://stackoverflow.com/questions/4341613/alternatives-to-replace-on-a-text-or-ntext-datatype – Adil H. Raza Jan 03 '19 at 14:23

score 78 · Answer 2 · answered Jun 28 '13 at 12:56

78

Try to remove % chars as below

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

answered Jun 28 '13 at 12:56

Robert

25,425
8
67
81

xinux · Answer 3 · 2016-10-12T09:03:39.903

67

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 '16 at 09:03

answered Feb 06 '14 at 16:08

xinux

967
8
14

score 57 · Answer 4 · edited Jul 23 '17 at 00:35

57

query:

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

edited Jul 23 '17 at 00:35

Parfait

104,375
17
94
125

answered Jul 22 '17 at 11:49

maneesh

579
4
2

score 10 · Answer 5 · answered Feb 20 '14 at 07:52

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)

ManhNguyen · Answer 6 · 2018-10-24T06:36:23.433

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 '18 at 06:36

answered May 18 '17 at 08:40

ManhNguyen

121
1
5

4

**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. – RBT May 19 '17 at 02:55
@RBT this is why reddit has the mad memes about SO – Tim Kretschmer Jun 02 '21 at 23:53

Mike Clark · Answer 7 · 2015-02-03T11:39:31.843

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 03 '15 at 11:39

answered Feb 03 '15 at 11:26

Mike Clark

1,860
14
21

2

Please explain how your answer solves the problem, it will help everyone understand your solution with more clarity and for future reference. – Aziz Apr 06 '16 at 02:09

score 1 · Answer 8 · edited Mar 30 '17 at 13:26

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 '17 at 13:26

Vasyl Senko

1,779
20
33

answered Jan 06 '17 at 11:56

DotnetCoder

13
1
2

mirazimi · Answer 9 · 2019-10-21T11:10:20.990

0

replace for persian word

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

help: dbo.TblNews -- table name

keyWords -- fild name

edited Oct 21 '19 at 11:10

answered Oct 10 '19 at 11:49

mirazimi

814
10
11

UPDATE and REPLACE part of a string

9 Answers9

Linked

Related