Replace part of the information in NTEXT, microsoft sql

so i have about 100 lines in an sql table that i need to modify part of the data in the Notepad1 field, the problem is i need to keep everything before the "**********  DO NOT SPEAK.  *********" part of the entry as it is different per row, the code listed below sees the correct amount of rows, but does not update the Notepad1.

the column Notepad1 is ntext, and is basically a text field that also contains RTF code and im not 100% that everything  from the "**********  DO NOT SPEAK.  *********" is universal on every account.

what can i do to replace the text including / after "**********  DO NOT SPEAK.  *********" without modifying the data contained before it?

UPDATE [CDISERV1].[SUBSCRIBER].dbo.[Subscriber Data] 
SET Notepad1 = CAST(REPLACE(CAST(Notepad1 as NVarchar(MAX)),'**********  DO NOT SPEAK.  *********%', '**********  DO NOT SPEAK.  *********  LISTEN IN FOR 30 SECONDS WITHOUT SPEAKING.\b0  \cf4\fs16 GO TO \fs20 (1)\cf3   \fs16 
\par \cf0 
\par \cf5\b (1)    data and text\cf5\b 
\par \cf0\b0                data and text\cf6\b  \cf4 (2)\b0 
\par \cf0                data and text \cf4\b (3)
\par \cf5 (2)   data and text \cf0\b0 
\par            \b     data and text  
\par                data and text \cf4 < #99 >\cf0   data and text. 
\par                   \cf7\b0 data and text 
\par                   data and text (*).\cf0 
\par \cf5\b (3)   data and text, \cf0\b0 
\par               \cf3  \b WAIT 30 SECONDS, THEN SAY:\i  
\par \cf0                \cf8\ul\fs18 "data and text''s name". Tdata and text"\fs16 
\par \cf0\ulnone\b0\i0                  \cf9 data and text\cf10 
\par \cf0                 data and text \cf4\b (4)\cf0\b0  
\par                  \cf3\b data and text,\cf0  PRESS \cf5 < #99 >\cf11 , \cf0 AND NOTIFY POLICE\b0 .
\par  \cf9                    \cf7 data and text.  
\par                     IF WE DISPATCH, data and text (*).\cf0 
\par \cf5\b 4)  data and text\cf0\b0 
\par                PRESS \cf4\b < #99 >\cf0\b0   TO DISCONNECT, THEN CALL THE SUBSCRIBER AND ASK AGAIN\i  
\par                \cf8\ul\b\fs18 "data and text''s name". data and text?".\fs16 
\par \cf0\ulnone\b0\i0              \b   data and textPOSSIBLE DOMESTIC VIOLENCE INCIDENT
\par \b0                \cf3\b data and text, \cf0 data and text\b0 
\par             \cf9   \cf1     data and text
\par                   \cf7 IF data and text (*)\cf9 
\par \cf0                 
\par \cf7\f1 (*) \b NOTE:\b0  IF WE DISPATCH POLICE, data and text\cf0\f2\fs22 
\par \f0\fs16 
\par \ul data and text.  \b FRIDAY 3.30 PM - SUNDAY 7.30 AM\b0 , data and text.   
\par 
\par 3-6-2018 APPROVE BY data and text\cf1\b\i 
\par 
\par }') AS NText) 
WHERE Notepad1 LIKE '%**********  DO NOT SPEAK.  *********%'; AND AcctlineCode = 'RSV' 

Open in new window

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.

Chris LuttrellSenior Database ArchitectCommented:
I believe your problem is as simple as you left the % wildcard character on your string pattern to search for:
 -- CAST(REPLACE(CAST(Notepad1 as NVarchar(MAX)),'**********  DO NOT SPEAK.  *********%', '**********  DO NOT SPEAK.  *********  LISTEN ...

at least when I tested it with the % removed it changed the NTEXT field in my test table.
0
csePixelatedAuthor Commented:
did it change everything after the **********  DO NOT SPEAK.  ********* as well?
i left the wildcard in thinking i needed it so it would replace all the entry after and including the **********  DO NOT SPEAK.  *********
perhaps i should delete everything after the **********  DO NOT SPEAK.  ********* then replace it?
0
Chris LuttrellSenior Database ArchitectCommented:
ah, ok, your right, let me look up another command you need instead of replace
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

csePixelatedAuthor Commented:
Thankyou Chris.
0
Chris LuttrellSenior Database ArchitectCommented:
ok, the real thing you are supposed to use with NTEXT is UPDATETEXT, https://docs.microsoft.com/en-us/sql/t-sql/queries/updatetext-transact-sql?view=sql-server-2017, but that has to be set up and used on a 1 record at a time basis, so I experimented with something else similar to what you were doing.

Try this using SUBSTRING and CHARINDEX to see if it works for you:


UPDATE dbo.[Subscriber Data] 
SET Notepad1 = CONCAT(SUBSTRING(CAST([Subscriber Data].Notepad1 AS NVARCHAR(MAX)),1, CHARINDEX('**********  DO NOT SPEAK.  *********', CAST([Subscriber Data].Notepad1 AS NVARCHAR(MAX)), 1)-1), '**********  DO NOT SPEAK.  ********* New Text')
WHERE Notepad1 LIKE '%**********  DO NOT SPEAK.  *********%'
AND AcctlineCode = 'RSV' ;

Open in new window

My results: query results
0
csePixelatedAuthor Commented:
i'm running sql server 2008r2, i don't know if that makes a difference, i ran the above and got

Msg 195, Level 15, State 10, Line 2
'CONCAT' is not a recognized built-in function name.
0
Chris LuttrellSenior Database ArchitectCommented:
Yep, it makes a difference. :)
Just use the old fashioned + sign, it is just not as friendly if you run into NULLs but I don't think you will have that problem here.
UPDATE dbo.[Subscriber Data] 
SET Notepad1 = SUBSTRING(CAST([Subscriber Data].Notepad1 AS NVARCHAR(MAX)),1, CHARINDEX('**********  DO NOT SPEAK.  *********', CAST([Subscriber Data].Notepad1 AS NVARCHAR(MAX)), 1)-1) + '**********  DO NOT SPEAK.  ********* New Text'
WHERE Notepad1 LIKE '%**********  DO NOT SPEAK.  *********%'
AND AcctlineCode = 'RSV';

Open in new window

gave me the same results
0

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:
I tried the above on a backup server and it worked however when i tried on my active server it gave me the error

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "Subscriber Data.Notepad1" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "Subscriber Data.Notepad1" could not be bound.

since i have linked servers on the active server i tried to be more specific to see if that helped. this is what i got.

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "CDISERV1.SUBSCRIBER.dbo.Subscriber Data.Notepad1" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "CDISERV1.SUBSCRIBER.dbo.Subscriber Data.Notepad1" could not be bound.
0
Chris LuttrellSenior Database ArchitectCommented:
that is often a case of confused table aliases, can you post the entire query?
0
csePixelatedAuthor Commented:
i can, however i will always have to remove most of the text im adding as it is confidential. one min
0
Chris LuttrellSenior Database ArchitectCommented:
sure, no problem, it is the table and column names that are important
0
csePixelatedAuthor Commented:
UPDATE [CDISERV1].[SUBSCRIBER].dbo.[Subscriber Data] 
SET Notepad1 = SUBSTRING(CAST([Subscriber Data].Notepad1 AS NVARCHAR(MAX)),1, CHARINDEX('**********  DO NOT SPEAK.  *********', CAST([Subscriber Data].Notepad1 AS NVARCHAR(MAX)), 1)-1) + '**********  DO NOT SPEAK.  ********* new text 
\par }'
WHERE Notepad1 LIKE '%**********  DO NOT SPEAK.  *********%'
AND AcctlineCode = 'RSV';

Open in new window

0
csePixelatedAuthor Commented:
i tryed replacing all instances of
[Subscriber Data].Notepad1

Open in new window

with
[CDISERV1].[SUBSCRIBER].dbo.[Subscriber Data].Notepad1

Open in new window

i got the same thing
0
Chris LuttrellSenior Database ArchitectCommented:
checking to see if it is the fact that you are using linked servers is the issue, having to set up some test environments...
0
csePixelatedAuthor Commented:
very much appreciated Chris.
0
Chris LuttrellSenior Database ArchitectCommented:
while I am doing that, can you test this out, it is a way to use table aliases on the UPDATE statement

UPDATE SD
SET Notepad1 = SUBSTRING(CAST(SD.Notepad1 AS NVARCHAR(MAX)),1, CHARINDEX('**********  DO NOT SPEAK.  *********', CAST(SD.Notepad1 AS NVARCHAR(MAX)), 1)-1) + '**********  DO NOT SPEAK.  ********* New Text'
FROM [CDISERV1].[SUBSCRIBER].dbo.[Subscriber Data] AS SD
0
csePixelatedAuthor Commented:
Msg 537, Level 16, State 5, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.
The statement has been terminated.

SIDENOTE: there was once a button on Experts Exchange so i could ask a question related to another question, I cant find that now, i miss that functionality.
0
Chris LuttrellSenior Database ArchitectCommented:
yeah, not sure what happened to that button.
so Invalid Length Parameter comes from it not finding the search pattern and returning a 0, is that the case, I did not account for that?
or you forgot the WHERE that would limit the query to those that will match? query resultsAt least it seems to be reading data now!
1
csePixelatedAuthor Commented:
i did forget the where!
0
Chris LuttrellSenior Database ArchitectCommented:
:) Glad you got it working! I enjoyed helping you find a solution.  Best of luck, Chris
0
csePixelatedAuthor Commented:
I really do appreciate it Chris, you really solved 2 problems, so thanks.
0
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.