How to replace a specific word in a sentence without replacing in substring in SQL Server

user_10903864 0
Viewed 705

I want to replace a specific word in a sentence but not in a substring.

Like 

DECLARE @i VARCHAR(250)
SET @i = 'MR JOHN NAMR is working from 3 days.'

PRINT REPLACE(@i, 'MR ', '')

Output : 

'JOHN NAis working from 3 days.'

Like in this I want to replace 'MR ' with an empty string. But if you see it is replacing 'MR ' from 'NAMR ' as well.

Kindly suggest a solution.

sql-server sql database
1 Answers

You can try the following query. Here at the start and end a space has been inserted first and then removed that space.

DECLARE @i VARCHAR(250)
SET @i = 'MR JOHN NAMR is working from 3 days.'

SELECT rtrim(ltrim(Replace(replace(' '+@i+' ',' MR ','  '),
' MR ','  ')))

The output is as shown below

JOHN NAMR is working from 3 days.

You can find the live demo Demo Replace Word

user_105325000
Related