TSQL Update Statement To Replace Values and Include Carriage Returns in Long Text Field

I have a table which contains 2 fields; (1) ResourceID and (2) LongText.  The LongText field is a long text field like an NTEXT containing data elements (similar to an XML but not really).  I'm looking to run an UPDATE statement to basically REPLACE  a common string value with a new string value but the catch is the LongText field contains carriage returns.

update tableA
set AML = CAST(REPLACE(CAST(LongText as nvarchar(max)),'old_value_containing_carriage_returns', 'new_value_with_carriage_returns') AS ntext)
where ResourceID = '{123456}'

Open in new window

Example of the LongText value copied into Notepad++ , you'll notice I included the show all characters so you can see the carriage returns listed as CRLF (for visual purposes)
current result
I want to insert 2 additional values very similar in the same format but between (highlighted in blue).  It's not an INSERT because it's just 1 singular LongText field (aka like a note field).  Any ideas on best way to write the UPDATE statement?  Thanks in advance for the help!
expected result
KANEDA 0149Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
hmm, I'm not sure what you're trying to do..

[..] an NTEXT containing data elements [..]
violates 1NF.

I'm looking to run an UPDATE statement to basically REPLACE  a common string value with a new string value but the catch is the LongText field contains carriage returns.
Can you rephrase that? Cause it makes no sense. If you want to replace it, why does the old value matter?
KANEDA 0149Author Commented:
Hi ste5an, yes it does sound strange and I could be saying it incorrectly.  The old value matter because of the format structure of this 1 particular field.  The field itself in the table is 1 long continuous string of code used in an application that references this field.

Visually in the table it's just a long string value but through the application that reference this code, it gives it a GUI value that makes sense.

Through the app I can insert those 2 line items I mentioned (business owner & business description) but I would have to do that for 100+ which is time consuming.  I was hoping by running an UPDATE statement could simplify the process and place those values in the string code just as if I did it manually.

Hope that helps!  Thanks for the response.
PortletPaulEE Topic AdvisorCommented:
You may have difficulties using NTEXT as it is a deprecated data type

Below I have used the REPLACE function which does not support NTEXT
    ([longtext] nvarchar(max))
<!-- /********************************************* -->
<!-- | Task Name : -->
<!-- | Workflow Name : -->'
update table1
set longtext = replace(longtext,'<!-- | Task Name :','<!-- | Business Owner : -->
<!-- | Business Description : -->
<!-- | Task Name :')

from table1

Open in new window

|                                                                                                                                                                                             longtext |
<!-- /********************************************* -->
<!-- | Business Owner : -->
<!-- | Business Description : -->
<!-- | Task Name : -->
<!-- | Workflow Name : --> |

Open in new window

see http://sqlfiddle.com/#!18/40c91/1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KANEDA 0149Author Commented:
Thank you PortletPaul, that worked and I even included CAST to set value to NTEXT!
PortletPaulEE Topic AdvisorCommented:
well done! and thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.