question

ftwomey avatar image
ftwomey asked

Find and replace string

I have a title field and a description field. I want to search the description field for the title and remove it from description if found. The description field is type text.
updatereplacesearch
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

seanlange avatar image seanlange commented ·
You really should not use the text datatype. It has been deprecated for more than a decade in favor of varchar(max). The text datatype is a serious pain to work with because you have to constantly cast it to varchar(max) and you can't index it. Ugh!!!
1 Like 1 ·
JohnM avatar image JohnM commented ·
What have you tried thus far?
0 Likes 0 ·

1 Answer

·
Button avatar image
Button answered
If "Title" is of type varchar and "Description" is of type text then this should show you the result and you can go by that to create your update statement. SELECT REPLACE(CAST(Description AS NVARCHAR(MAX)), Title, '') FROM Table1 WHERE Description LIKE '%' + Title + '%'; Please note that ntext , text, and image data types will deprecated in future versions of SQL. So switch the column to type NVARCHAR(MAX) as the new recommended standard to replace TEXT. Data loss should not be an issue changing from TEXT to NVARCHAR(MAX), but testing and backups are always recommended before applying to any production environment. Please see [Source of information][1] for more information. [1]: https://msdn.microsoft.com/en-za/library/ms187993.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.