• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 32
  • Last Modified:

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
0
KANEDA 0149
Asked:
KANEDA 0149
  • 2
  • 2
1 Solution
 
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?
0
 
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.
0
 
PortletPaulfreelancerCommented:
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
CREATE TABLE Table1
    ([longtext] nvarchar(max))
;
    
INSERT INTO Table1
    ([longtext])
VALUES
('<REGION NAME="TASK NOTES" />
<!-- /********************************************* -->
<!-- | Task Name : -->
<!-- | Workflow Name : -->'
 )
;
update table1
set longtext = replace(longtext,'<!-- | Task Name :','<!-- | Business Owner : -->
<!-- | Business Description : -->
<!-- | Task Name :')


select
*
from table1
;

Open in new window

|                                                                                                                                                                                             longtext |
|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| <REGION NAME="TASK NOTES" />
<!-- /********************************************* -->
<!-- | Business Owner : -->
<!-- | Business Description : -->
<!-- | Task Name : -->
<!-- | Workflow Name : --> |

Open in new window


see http://sqlfiddle.com/#!18/40c91/1
0
 
KANEDA 0149Author Commented:
Thank you PortletPaul, that worked and I even included CAST to set value to NTEXT!
0
 
PortletPaulfreelancerCommented:
well done! and 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.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now