alternatives to REPLACE on a text or ntext datatype

by

IF your data won’t overflow 4000 characters AND you’re on SQL Server 2000 or compatibility level of 8 or SQL Server 2000:

UPDATE [CMS_DB_test].[dbo].[cms_HtmlText] 
SET Content = CAST(REPLACE(CAST(Content as NVarchar(4000)),'ABC','DEF') AS NText)
WHERE Content LIKE '%ABC%'

For SQL Server 2005+:

UPDATE [CMS_DB_test].[dbo].[cms_HtmlText] 
SET Content = CAST(REPLACE(CAST(Content as NVarchar(MAX)),'ABC','DEF') AS NText)
WHERE Content LIKE '%ABC%'

More Related Contents:

  1. DateTime2 vs DateTime in SQL Server
  2. How to generate a range of numbers between two numbers?
  3. How to select all records from one table that do not exist in another table?
  4. SET versus SELECT when assigning variables?
  5. T-SQL dynamic pivot
  6. Avoid duplicates in INSERT INTO SELECT query in SQL Server
  7. How do you list the primary key of a SQL Server table?
  8. Generate Dates between date ranges
  9. Select group of rows that match all items in a list
  10. Calculating distance between two points (Latitude, Longitude)
  11. SQL Server check case-sensitivity?
  12. How to print VARCHAR(MAX) using Print Statement?
  13. How to update Identity Column in SQL Server?
  14. Concatenate values based on ID
  15. Query error with ambiguous column name in SQL
  16. Why (and how) to split column using master..spt_values?
  17. Splitting delimited values in a SQL column into multiple rows
  18. What does =* mean?
  19. Why and when a LEFT JOIN with condition in WHERE clause is not equivalent to the same LEFT JOIN in ON? [duplicate]
  20. Scope of temporary tables in SQL Server
  21. SQL Select Upcoming Birthdays
  22. How can I include null values in a MIN or MAX?
  23. Can an INNER JOIN offer better performance than EXISTS
  24. Find the real column name of an alias used in a view?
  25. How to get the identity of an inserted row?
  26. Get the last day of the month in SQL
  27. The most elegant way to generate permutations in SQL server
  28. Cumulative Total in MS Sql server [duplicate]
  29. Why is RAND() not producing random numbers?
  30. Should every SQL Server foreign key have a matching index? [closed]

Leave a Comment