UPDATE SET REPLACE function SQL

Question

I keep getting errors when using the REPLACE function in SQL and i don't know why?

I execute this query:

UPDATE [ShellPlus].[dbo].[Views]
SET [ShellPlus].[dbo].[Views].SerializedProperties = REPLACE(SerializedProperties,'|EKZ PSG met verkort EEG','|EKZ PSG met verkort EEG|EEG kort op EEG3')  
WHERE [ShellPlus].[dbo].[Views].InternalViewID = '3F4C1E8E-DA0C-4829-B447-F6BDAD9CD505'

And I keep getting this message:

Msg 8116, Level 16, State 1, Line 6 Argument data type ntext is invalid for argument 1 of replace function.

At UPDATE I give the correct table
At SET I give the correct column
At REPLACE I give the: (column name, 'old string', 'new string')

What am I doing wrong?

Error is telling you problem. Why are you still using `ntext` anyway? It's been deprecated for almost 15 years now. — Thom A, Nov 22 '19 at 12:56
Also, referencing column names using 3+ part naming (4 part in your case) is also deprecated and should be avoided. Your code will stop working one day. — Thom A, Nov 22 '19 at 12:56
@GarethD while this is a clear duplicate, None of the answers in the post you've linked to gives a solution or explains the problem - the best you get is a workaround. This is why I've revoked your duplicate vote. — Zohar Peled, Nov 22 '19 at 13:14

score 1 · Answer 1 · answered Nov 22 '19 at 13:30

1

The real solution is fix the data type:

USE ShellPlus;

ALTER TABLE [dbo].[Views] ALTER COLUMN SerializedProperties nvarchar(MAX);

Then your existing query will work. But you should also normalise that data.

answered Nov 22 '19 at 13:30

Thom A

88,727
11
45
75

score 0 · Answer 2 · answered Nov 22 '19 at 12:56

0

Try this:

UPDATE [ShellPlus].[dbo].[Views]
SET [ShellPlus].[dbo].[Views].SerializedProperties = CAST(REPLACE(CAST(SerializedPropertiesas NVarchar(MAX)),'|EKZ PSG met verkort EEG','|EKZ PSG met verkort EEG|EEG kort op EEG3') ) AS NText)
WHERE [ShellPlus].[dbo].[Views].InternalViewID = '3F4C1E8E-DA0C-4829-B447-F6BDAD9CD505'

answered Nov 22 '19 at 12:56

Red Devil

2,343
2
21
41

You should really get rid of that 4 part column naming. – Thom A Nov 22 '19 at 12:57
2

While this workaround will probably help the OP get rid of the error messages, it is by no means a solution to the problem - The errors the OP is getting are merely a symptom of the actual problem, which is the usage of the `Text` data type. – Zohar Peled Nov 22 '19 at 13:06

Zohar Peled · Answer 3 · 2019-11-22T13:10:14.070

Your doing at least three things wrong:

It seems like you're storing delimited data in your column - which is a mistake. For more information, read Is storing a delimited list in a database column really that bad?, where you will see a lot of reasons why the answer to this question is Absolutely yes!
You're using the Text data type, which is deprecated since SQL Server 2008 introduced varchar(max) to replace it. Given the fact that we're in 2019 and the 2008 version just ended it's extended support this July, its high time to change that Text data type to varchar(max) (and if you're using the 2008 or 2008 r2 version, upgrade your SQL Server).
You're using four-parts identifiers for your column names (Thanks @Larnu for pointing that out in the comments). Best practice is to use two-parts identifiers for column names. Read my answer here for a details explanation.

The solution to your problem involves refactoring the database structure - normalize what needs to be normalized, and replace of all Text, NText and Image with their modern replacement data types: varchar(max), nvarchar(max) and varbinary(max). Also, this would be a good time to figure out if you really need to support values longer than 8000 chars (4000 for unicode values) and if not, use a more appropriate value (max length columns have poor performance compared to "regular" length columns).

UPDATE SET REPLACE function SQL

3 Answers3