find and replace a long string containing 's

i am trying to replace a paragraph of rtf text of a string, i have the following sql
UPDATE [CDISERV2].[SUBSCRIBER].dbo.[Subscriber Data] 
SET Notepad1 = REPLACE(Notepad1, '\par WHEN 
**************** (all sorts of info including ''s /etc)***************
\par 
\par }', '\par WHEN 
**************** (all sorts new info including ''s /etc)***************
\par 
\par }') 
WHERE Notepad1 LIKE '%\par WHEN 
**************** (all sorts of info including ''s /etc)***************
\par 
\par }%';

Open in new window


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

is there a different way for me to find and replace a string
LVL 1
csePixelatedAsked:
Who is Participating?

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

x
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.

Bill PrewIT / Software Engineering ConsultantCommented:
Based on the error message, it sounds like Notepad1 is declared as data type ntext, yes?  And if so, it appears that the Replace() function won't work against ntext data types.

You could try converting or CASTing it to a character data type that replace supports, not sure if that will work or not...

sshot-482.png
sshot-483.png

»bp
Bill PrewIT / Software Engineering ConsultantCommented:
csePixelatedAuthor Commented:
would it be possible to get an example with syntax and the information i have provided?
I assume i'm ok in using '' to treat ' as text?
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

csePixelatedAuthor Commented:
UPDATE [CDISERV3].[SUBSCRIBER].dbo.[Subscriber Data] 
SET Notepad1 = CAST(REPLACE(CAST(Notepad1 as NVarchar(MAX)),'1st' '2nd') AS NText) 
WHERE Notepad1 LIKE '%1st%';

Open in new window


however it changed nothing, i have a feeling the rtf formatting is causing problems
csePixelatedAuthor Commented:
i have a unique section of text [**********  DO NOT SPEAK.  *********] would it be possible to simply set all text after this text?
Bill PrewIT / Software Engineering ConsultantCommented:
Does this return anything?

SELECT * FROM [CDISERV3].[SUBSCRIBER].dbo.[Subscriber Data] WHERE Notepad1 LIKE '%1st%';


»bp

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
csePixelatedAuthor Commented:
0 rows
csePixelatedAuthor Commented:
SELECT * FROM [CDISERV3].[SUBSCRIBER].dbo.[Subscriber Data] WHERE Notepad1 LIKE '%**********  DO NOT SPEAK.  *********%';

Open in new window

has 72 rows , this is correct
Bill PrewIT / Software Engineering ConsultantCommented:
So that's why the UPDATE you tried didn't change anything, it didn't select any rows to process.

Are you sure the text characters 1st are in the ntext column?


»bp
Bill PrewIT / Software Engineering ConsultantCommented:
You started with wanting to replace some data, but this asks about seeing data.  Can you take a step back and talk about what you are trying to accomplish, and what the data is like in that column?


»bp
csePixelatedAuthor Commented:
there should be at least 1 row - as i copied the text from one i needed to change
Bill PrewIT / Software Engineering ConsultantCommented:
I suspect the RTF storage of what displays as "1st" may actually be something like below, so the "st" is super scripted.  You probably need to dump the RTF data from that column to see what you really need to search for.

1\super st\nosupersub


»bp
csePixelatedAuthor Commented:
I am trying to replace everything from the **********  DO NOT SPEAK.  ********* part till the end of the data

so if Row |Notepad1 was
row5 |blobs are bad **********  DO NOT SPEAK.  ********* unless you are 200cm tall and that's my tv
row7 |blobs are sad **********  DO NOT SPEAK.  ********* unless you are 194cm tall and that's my vcr
would change to
row5 |blobs are bad **********  DO NOT SPEAK.  ********* that's my stuff unless you're bigger than me
row7 |blobs are sad **********  DO NOT SPEAK.  ********* that's my stuff unless you're bigger than me

mind you the contents of Notepad1 is about 2 paragraphs long
csePixelatedAuthor Commented:
UPDATE [CDISERV1].[SUBSCRIBER].dbo.[Subscriber Data] 
SET Notepad1 = CAST(REPLACE(CAST(Notepad1 as NVarchar(MAX)),'**********  DO NOT SPEAK.  *********%', '**********  DO NOT SPEAK.  *******************  DO NOT SPEAK.  ********* that's my stuff unless you're bigger than me ') AS NText) 
WHERE Notepad1 LIKE '%**********  DO NOT SPEAK.  *********%';

Open in new window

csePixelatedAuthor Commented:
Bill is correct he has resolved the issue i asked about- ill ask another question to resolve the new problem.
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.