SQL Server:更新以仅匹配和替换完全相同的单词 [英] SQL Server: update to match and replace only exact words

问题描述

我想匹配一个确切的单词，然后用另一个单词替换它.

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

这是我正在使用的SQL Server查询:

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;

此查询的作用:

'Test'，'test'，'TEST'被更新为'prod'-这是预期的.

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

'Test2'，'TestTest'，'Fastest'更新为'prod'-我想避免这种情况.

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

请帮助.

我尝试过但没有用的其他查询:

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;

这是列值:

Test testtest Test1 Test TEST

预期结果:

prod testtest Test1 prod prod

当前结果:

prod prodprod prod1 prod prod

推荐答案

我给人的印象是test的所有不同版本都在同一行值内，这就是为什么您指出建议的like 'test'无法正常工作.

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;

输出:

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

---

使用<>代替空格是由于SQL Server的默认行为，即忽略字符串比较中的尾随空格.这些可以是任何两个字符，但我觉得它们在美学上令人愉悦.

---

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.

这篇关于SQL Server:更新以仅匹配和替换完全相同的单词的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！