UPDATE SET REPLACE function SQL

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?

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.

UPDATE and REPLACE part of a string, This article covers using the REPLACE function to selectively replace text inside a string in SQL Server. The REPLACE function is easy to use� 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?

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'

Using REPLACE in an UPDATE statement, The REPLACE() function replaces all occurrences of a substring within a string, with a new substring. Note: The search is case-insensitive. Tip: Also look at the� string functions ascii char_length character_length concat concat_ws field find_in_set format insert instr lcase left length locate lower lpad ltrim mid position repeat replace reverse right rpad rtrim space strcmp substr substring substring_index trim ucase upper numeric functions abs acos asin atan atan2 avg ceil ceiling cos cot count degrees

Your doing at least three things wrong:

  1. 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!

  2. 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).

  3. 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).

SQL Server REPLACE() Function, For example, the following statement updates the email of employees from sqltutorial.org to acme.com for the employee id 100: UPDATE employees SET email =� UPDATE sales.customers SET phone = REPLACE (phone, ' (916)', ' (917)') WHERE phone IS NOT NULL ; Notice that you should back up the table before performing replacements. In this tutorial, you have learned how to use the SQL Server REPLACE () function to replace all occurrences of a substring, within a string, with a new substring.

SQL REPLACE Function: Search and Replace String in Database, Here is the result set. Here is an example to update using the SQL REPLACE function. In this case, GRP column with the GRP CODE, run the� REPLACE ( string, old_substring, new_substring); The REPLACE function will search for all occurrences of the old_substring and replace it with the new_string. The following statement replaces all the occurrences of bar with foo so the result is bar bar bar. SELECT REPLACE ( 'foo foo bar', 'foo', 'bar' ); -- bar bar bar.

Overview of the SQL REPLACE function, UPDATE products SET productDescription = REPLACE(productDescription, ' abuot', 'about');. The query finds all occurrences of a spelling mistake abuot and� The below SQL REPLACE function undergoes an execution of 3 iterations to get the desired result. The first, input pattern ‘A’ is evaluated and if found, 5 are replaced. The second, B is evaluated. If found the numeric value 9 is replaced.

MySQL REPLACE String Function: Find & Replace Text, This tutorial shows you how to use SQLite REPLACE() function to replace all The REPLACE() function is useful for updating character data in a table e.g., update the dead UPDATE contacts SET phone = REPLACE(phone,'410','+1- 410');. The SQL UPDATE Statement The UPDATE statement is used to modify the existing records in a table.

Comments
  • Error is telling you problem. Why are you still using ntext anyway? It's been deprecated for almost 15 years now.
  • 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.
  • @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.
  • You should really get rid of that 4 part column naming.
  • 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.