CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008replace
user3540425
user3540425

Reputation: 29

Replace single quote / apostrophe in NTEXT column

I have an NTEXT column in a table, having XML tag data with single quotes. I have to replace strings having XML tag data and quote quote with blank.

I have data in col as below 

<?xml version="1.0" encoding="utf-8" ?> <securesite>   <content>     <formsections>       <version>12</version>       <introduction>         <p>Based on the product </p>         <p>If you are registered for  </p>     <p>                     <![CDATA[         **<p><b>Can't see your form in the list below</b></p>**         <a href="http://www.testcom.aspx" target="_blank" alt="View forms">View forms</a>         ]]>                 </p><.....

I have to remove string 

<p><b>Can't see your form in the list below</b></p>

I have tried using the following query, but it did not worked:

UPDATE Table SET Col = REPLACE( Col, '  <p><b>Can't see your form in the list below</b></p>  ', '')

Also tried single quote replaces as '''' single quote: 

UPDATE Table SET Col = REPLACE( Col, '  <p><b>Can''''t see your form in the list below</b></p>  ', '')

I also tried this: 

UPDATE Table SET Col =REPLACE( CAST( col as varchar(max) ), '  <p><b>Can''''t see your form in the list below</b></p>  ', '')

Also tried as adviced 

DECLARE @YourOriginalText NVARCHAR(MAX)
DECLARE @YourModifiedText NVARCHAR(MAX)

SELECT @YourOriginalText = CAST(Col AS NVARCHAR(MAX))FROM [Table]
SET @YourModifiedText = REPLACE( CONVERT(NVARCHAR(MAX), Col), N'  <p><b>Can''t see     your form in the list below</b></p>  ', '')
SELECT @YourModifiedText

UPDATE [Table] SET Col= CAST(@YourModifiedText AS NTEXT)

but it didn't worked too.

Can you guide me in the right direction and show me how to replace the text?

Upvotes: 2

Views: 1458

Answers (1)

Solomon Rutzky
Solomon Rutzky

Reputation: 48826

An escaped single-quote is two single-quotes, not four. Also, NTEXT does not seem to work with the REPLACE function as you get the following error:

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

So, try this (NTEXT cannot be compared with = so you need to use LIKE):

UPDATE [Table]
SET Col = N''
WHERE Col LIKE N'  <p><b>Can''t see your form in the list below</b></p>  ';

OR, convert the column to NVARCHAR(MAX) so that it does work with the REPLACE function:

UPDATE [Table]
SET Col =
   REPLACE( CONVERT(NVARCHAR(MAX), Col),
            N'  <p><b>Can''t see your form in the list below</b></p>  ',
             '');

(the above query is essentially your 3rd attempt, but has the embedded single-quote properly escaped)

In both cases, since it is an NTEXT field, you should be prefixing string literals with a capital-"N" as well as casting / converting to NVARCHAR rather than VARCHAR.

Upvotes: 1

Related Questions