SQL Server: update to match and replace only exact words

Question

I want to match an exact word and replace it with another word.

Here is the SQL Server query that I am using:

UPDATE BODYCONTENT 
SET BODY = CAST(REPLACE(CAST(BODY AS NVARCHAR(MAX)), 'Test' , 'prod') AS NTEXT) 
WHERE BODY COLLATE Latin1_General_CI_AS LIKE '%Test%' COLLATE Latin1_General_CI_AS;

What this query is doing:

'Test','test','TEST' is updated into 'prod' -- This is expected.

'Test2','TestTest', 'Fastest' is updated into 'prod' - I want to avoid this behavior.

Please help.

Other queries I tried but didn't work:

UPDATE BODYCONTENT 
SET BODY = CAST(REPLACE(CAST(BODY AS NVARCHAR(MAX)), 'Test' , 'prod') AS NTEXT) 
WHERE BODY COLLATE Latin1_General_CI_AS LIKE '%[^A-Za-z0-9]Test[^A-Za-z0-9]%' COLLATE Latin1_General_CI_AS;

And when I do a select for 'Test' using the below query:

SELECT * 
FROM dbo.BODYCONTENT 
WHERE CONVERT(NVARCHAR(MAX), BODY) = N'Test';

It is not returning anything. But I could get results using the below queries:

  SELECT BODY 
  FROM dbo.BODYCONTENT 
  WHERE BODY LIKE '%Test%';

  SELECT BODY 
  FROM dbo.BODYCONTENT 
  WHERE BODY COLLATE Latin1_General_CI_AS like '%TEST%' COLLATE Latin1_General_CI_AS;

Here is the column value:

Test testtest Test1 Test TEST

Expected result:

prod testtest Test1 prod prod

Current result:

prod prodprod prod1 prod prod

`ntext`, `text`, and `image` data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use `nvarchar(max)`, `varchar(max)`, and `varbinary(max)` instead. [See details here](http://msdn.microsoft.com/en-us/library/ms187993.aspx) — marc_s, Jun 21 '18 at 18:00
I think if you just use `LIKE 'test'` instead of `LIKE '%test%'` it will match the string "test" only, regardless of upper/lower case. The % characters are acting as wildcards and will match anything. — JohnRC, Jun 21 '18 at 18:05
The ntext datatype in here is just adding a level of complication that is beyond unnecessary. That datatype has been deprecated for more than a decade in favor of nvarchar(max). Despite that I think the problem is that you want to change only whole words within a larger string. To accomplish that you are going to need to split your string into words. For that you need a string splitter. Depending on the version it may or may not have STRING_SPLIT. Then you would be able to replace only those with the entire word matching. And finally you would have to shove the words back together again. — Sean Lange, Jun 21 '18 at 18:37
I would like add reference to this post here:https://dba.stackexchange.com/questions/151550/sql-server-ntext-columns-and-string-manipulation — Ilak, Jun 21 '18 at 20:08
Sean and marc, Thank you for your comments. Yes, nText is adding complexity. — Ilak, Jun 21 '18 at 20:11

iamdave · Accepted Answer · 2018-06-22T09:30:33.167

2

I am getting the impression that all the different versions of test are within the same row value, which is why you are stating that the suggested like 'test' is not working.

Based on this, the below is rather ugly but functional per your requirements:

declare @t table(s ntext);
insert into @t values('Test testtest Test1 Test TEST');

select s as Original
        ,ltrim(rtrim(replace(
                            replace(
                                    replace(N' ' + cast(s as nvarchar(max)) + N' '  -- Add a single space before and after value,
                                            ,' ','<>'                               -- then replace all spaces with any two characters.
                                            )
                                    ,'>test<','>prod<'      -- Use these two characters to identify single instances of 'test'
                                    )
                            ,'<>',' '       -- Then replace the delimiting characters with spaces and trim the value.
                            )
                    )
            ) as Updated
from @t;

Output:

+-------------------------------+-------------------------------+
|           Original            |            Updated            |
+-------------------------------+-------------------------------+
| Test testtest Test1 Test TEST | prod testtest Test1 prod prod |
+-------------------------------+-------------------------------+

The use of <> in place of spaces is due to SQL Server's default behaviour to ignore trailing spaces in string comparisons. These can be any two characters, but I find these to be aesthetically pleasing.

edited Jun 22 '18 at 09:30

answered Jun 22 '18 at 09:25

iamdave

12,023
3
24
53

Thank you very much! This is what I wanted. I appreciate your help. – Ilak Jun 22 '18 at 15:40
No worries, glad you got it sorted :) – iamdave Jun 22 '18 at 15:41
I do have one more question. I have this collation set in my db - Latin1_General_CS_AS. This is making the searches case sensitive and How do I search for my search string case insensitive? – Ilak Jun 22 '18 at 16:10
Clue: The below sql works for me: UPDATE BODYCONTENT SET BODY = CAST(REPLACE(CAST(BODY as nvarchar(max)), 'test' , 'prod') as ntext) WHERE BODY COLLATE Latin1_General_CI_AS like '%test%' COLLATE Latin1_General_CI_AS; I think i gotta use the where clause. – Ilak Jun 22 '18 at 16:11
Child Question - https://stackoverflow.com/questions/50992508/sql-server-to-search-case-insensitive-where-the-collate-latin1-general-cs-as-i – Ilak Jun 22 '18 at 17:07
1

This is a neat solution +1 - Just a suggestion: use substring instead of ltrim and rtrim like this: `SUBSTRING( REPLACE( REPLACE( REPLACE ( '>' + CAST( @txt AS Varchar(MAX) ) + '<' , ' ', '<>' ), '>test<', '>prod<' ), '<>', ' ' ), 2, LEN(@txt))` – JohnRC Jun 22 '18 at 22:20
Thank you JohnRC and iamdave. This query is not replacing the following strings Test --- > test with a lot of white spaces in the beginning, %test% - test with special characters. Please suggest me a suitable solution. I appreciate it – Ilak Jun 27 '18 at 19:14
Related question - https://stackoverflow.com/questions/50994199/ntext-and-string-manipulation-sql-server. Please feel free to answer this. – Ilak Jun 27 '18 at 19:16
@iamdave, your answer is very helpful but query is not replacing the following strings: Test --- > test with a lot of white spaces in the beginning, %test% - test with special characters. Please advice when you find time. – Ilak Jun 27 '18 at 20:34

SQL Server: update to match and replace only exact words

1 Answers1

Linked