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?
 
Chris LuttrellConnect With a Mentor Senior 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
 
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Chris LuttrellSenior Database ArchitectCommented:
ah, ok, your right, let me look up another command you need instead of replace
0
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.