CODEWITHSUNDEEP
sqlsql-serverdatabase
VishnuB
VishnuB

Reputation: 111

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

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.

Upvotes: 8

Views: 1172

Answers (1)

Suraj Kumar
Suraj Kumar

Reputation: 5643

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

Upvotes: 7

Related Questions