我觉得所有不同版本的
test
like 'test'
不工作。
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 |
+-------------------------------+-------------------------------+
<>