Alternatives to Replace on a Text or Ntext Datatype

alternatives to REPLACE on a text or ntext datatype

IF your data won't overflow 4000 characters AND you're on SQL Server 2000 or compatibility level of 8 or SQL Server 2000:

UPDATE [CMS_DB_test].[dbo].[cms_HtmlText] 
SET Content = CAST(REPLACE(CAST(Content as NVarchar(4000)),'ABC','DEF') AS NText)
WHERE Content LIKE '%ABC%' 

For SQL Server 2005+:

UPDATE [CMS_DB_test].[dbo].[cms_HtmlText] 
SET Content = CAST(REPLACE(CAST(Content as NVarchar(MAX)),'ABC','DEF') AS NText)
WHERE Content LIKE '%ABC%' 

SQL: Alternative to text and ntext data type?

Using nvarchar(MAX) will allow you to store up to 2 GB of data. The same goes for varchar(MAX) too.

UPDATE error: Argument data type text is invalid for argument 1 of REPLACE

Try this:

UPDATE tableName
SET fieldName = REPLACE (CONVERT(VARCHAR(MAX), fieldName), '<', '<')
WHERE id = 100

REPLACE function for SQL server is not changing the value

You can use STUFF() :

DECLARE @UpdateText VARCHAR(255) = 'xxx You won ticket xxx' -- whatever data type you have
DECLARE @NewText VARCHAR(255) = 'xxx You won semi final ticket xxx' -- whatever data type you have

UPDATE Winner
     SET [Text] = STUFF([Text], PATINDEX('%' + @UpdateText + '%', [Text]), LEN(@UpdateText), @NewText)
WHERE Id = 'xxxxxxx'

replace ntext (more than 4000 characters) in sql server 2000

Working with TEXT/NTEXT is a mess - one of the many reason to get rid of those fields as quickly as possible.

You need to manipulate those with command such as READTEXT, WRITETEXT, UPDATETEXT and other - see some resources for help:

• MSDN docs on READTEXT
• MSDN docs on WRITETEXT
• MSDN docs on UPDATETEXT
• How do I handle REPLACE() within an NTEXT column in SQL Server?
• Update Text, NText Columns using UPDATETEXT Command in MS SQL Server

How to use `modify` method on a `ntext` column without changing column type to `xml` (using CAST or CONVERT or other method)

Unfortunately, you're pretty much stuck doing something similar to what you have when your XML is stored as text.

You can try something like this if you want to get rid of the use of temp tables. You can run this in SSMS.

/* Base table mock-up */
DECLARE @Data TABLE ( [data] NTEXT, [id] INT IDENTITY (1,1) );
INSERT INTO @Data ( [data] ) VALUES 
    ( '<root><values><val>Value 1</val><val>to_be_changed</val></values></root>' ),
    ( '<root><values><val>All is well here.</val><val>All is well here, too.</val></values></root>' ),
    ( '<root><values><val>Another value.</val><val>to_be_changed</val></values></root>' );

/* Find/Replace variables */
DECLARE 
    @find_value VARCHAR(50) = 'to_be_changed',
    @replace_value VARCHAR(50) = 'Value 2';

/* Create a table variable to temporarily house the ntext data as xml so the XML may be modified */
DECLARE @Temp TABLE ( DataXml XML, id INT );

/* Insert [data] into the XML column */
INSERT INTO @Temp ( DataXml, [id] )
SELECT CAST ( [data] AS XML ), [id] FROM @Data WHERE [data] LIKE '%' + @find_value + '%';

/* Show the @Data resultset before modifying */
SELECT * FROM @Data;

/* The WHILE is to make sure every node that requires updating gets updated */
/* Modify each instance matching the @find_value criteria */
WHILE EXISTS ( SELECT * FROM @Temp WHERE DataXml.exist( '//root/values/val/text()[.=sql:variable("@find_value")]' ) = 1 )
UPDATE @Temp
SET
    DataXml.modify ('
        replace value of (/root/values/val/text()[.=sql:variable("@find_value")])[1]
        with sql:variable("@replace_value")
    ');

/* Update the results back to the ntext column */
UPDATE @Data
SET
    [data] = CAST ( t.DataXml AS NVARCHAR(MAX) )
FROM @Data d
INNER JOIN @Temp t
    ON d.id = t.id;

/* Show the updated @Data resultset */
SELECT * FROM @Data;

The initial select of @Data:

/* Show the @Data resultset before modifying */
SELECT * FROM @Data;

Returns

+---------------------------------------------------------------------------------------------+----+
|                                            data                                             | id |
+---------------------------------------------------------------------------------------------+----+
| <root><values><val>Value 1</val><val>to_be_changed</val></values></root>                    |  1 |
| <root><values><val>All is well here.</val><val>All is well here, too.</val></values></root> |  2 |
| <root><values><val>Another value.</val><val>to_be_changed</val></values></root>             |  3 |
+---------------------------------------------------------------------------------------------+----+

And the final resultset of @Data:

/* Show the updated @Data resultset */
SELECT * FROM @Data;

Returns

+---------------------------------------------------------------------------------------------+----+
|                                            data                                             | id |
+---------------------------------------------------------------------------------------------+----+
| <root><values><val>Value 1</val><val>Value 2</val></values></root>                          |  1 |
| <root><values><val>All is well here.</val><val>All is well here, too.</val></values></root> |  2 |
| <root><values><val>Another value.</val><val>Value 2</val></values></root>                   |  3 |
+---------------------------------------------------------------------------------------------+----+

Possible alternative method:
Perhaps a simple REPLACE on your text.

UPDATE @Data
SET
    [data] = REPLACE ( CAST ( [data] AS NVARCHAR(MAX) ), @find_value, @replace_value )
FROM @Data d
WHERE
    d.[data] LIKE '%' + @find_value + '%';

UPDATE:

I should have been more clear by saying "I don't want to use any kind of intermediary tables"

/* For-each find/replace instance found... */
WHILE EXISTS ( SELECT * FROM @Data WHERE CAST ( [data] AS XML ).exist( '//root/values/val/text()[.=sql:variable("@find_value")]' ) = 1 )
BEGIN

    DECLARE @id INT, @xml XML;
    SELECT TOP 1
        @id = id,
        @xml = CAST ( [data] AS XML )
    FROM @Data
    WHERE CAST ( [data] AS XML ).exist( '//root/values/val/text()[.=sql:variable("@find_value")]' ) = 1;

    -- Modify the XML --
    SET @xml.modify('
        replace value of (/root/values/val/text()[.=sql:variable("@find_value")])[1]
        with sql:variable("@replace_value")
    ');

    -- Update the modified XML --
    UPDATE @Data
    SET
        [data] = CAST ( @xml AS NVARCHAR(MAX) )
    WHERE id = @id;

END

/* Show the updated resultset */
SELECT * FROM @Data ORDER BY id;

Updated Resultset:

+---------------------------------------------------------------------------------------------+----+
|                                            data                                             | id |
+---------------------------------------------------------------------------------------------+----+
| <root><values><val>Value 1</val><val>Value 2</val><val>Value 2</val></values></root>        |  1 |
| <root><values><val>All is well here.</val><val>All is well here, too.</val></values></root> |  2 |
| <root><values><val>Another value.</val><val>Value 2</val></values></root>                   |  3 |
+---------------------------------------------------------------------------------------------+----+

UPDATE BY OP

Thanks for your last solution, I ended up doing this, no need for a while loop

DECLARE @CURRENT_EXAM_CODE NVARCHAR(10) = 'BXC_14B'
DECLARE @NEW_EXAM_NAME NVARCHAR(10) = 'BCC'
DECLARE @CODE_DESC NVARCHAR(50)
DECLARE @XML_DATA XML


-- convert existing NTEXT data into XML
SELECT @XML_DATA =
    CAST([data] as xml)
    FROM [dbo].[CodeSystemCodes_data]   
    WHERE [data] like '%' + @CURRENT_EXAM_CODE + '%'

-- update the xml data
SET @XML_DATA.modify('replace value of (/Utilities.CodeSystems.CodeSystemCodes/@Description)[1] with sql:variable("@NEW_EXAM_NAME")')

SET @CODE_DESC = @CURRENT_EXAM_CODE + ' - ' + @NEW_EXAM_NAME

SET @XML_DATA.modify('replace value of (/Utilities.CodeSystems.CodeSystemCodes/@CodeAndDescription)[1] with sql:variable("@CODE_DESC")')

-- convert xml data back to ntext type
UPDATE [dbo].[CodeSystemCodes_data] 
    SET 
        [data] = CAST(CAST(@XML_DATA as nvarchar(max)) as ntext)
    WHERE [data] like '%' + @CURRENT_EXAM_CODE + '%'

Getting an error when trying to remove comma from a freetext column in SQL

You cannot run a REPLACE on a text data type. Convert it to [n]varchar(max) first.

USE DBname
UPDATE notestable
SET NotesColumn= REPLACE(CONVERT(nvarchar(max), NotesColumn),',','') 
WHERE NotesColumn LIKE '%,%'

UPDATE and REPLACE part of a string

You don't need wildcards in the REPLACE - it just finds the string you enter for the second argument, so the following should work:

UPDATE dbo.xxx
SET Value = REPLACE(Value, '123', '')
WHERE ID <=4

If the column to replace is type text or ntext you need to cast it to nvarchar

UPDATE dbo.xxx
SET Value = REPLACE(CAST(Value as nVarchar(4000)), '123', '')
WHERE ID <=4

---

Related Topics