Menu
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.
864
SQL Server REPLACE() FunctionThe REPLACE() function replaces all occurrences of a substring within a string, with a new substring. Note: The search is case-insensitive. Tip: Also look at the STUFF() function.
CHARINDEX function in SQL queries It works reverse to the SUBSTRING function. The substring() returns the string from the starting position however the CHARINDEX returns the substring position. In the below example, we retrieve the position of substring SQLSHACK.COM using the CHARINDEX.
We can remove part of the string using REPLACE() function. We can use this function if we know the exact character of the string to remove. REMOVE(): This function replaces all occurrences of a substring within a new substring.
On the Edit menu, point to Find and Replace, and then click Quick Find to open the dialog box with find options, but without replace options. On the Edit menu, point to Find and Replace, and then click Quick Replace to open the dialog box with both find options and replace options.
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
112
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
