REPLACE not removing the correct characters

Here is the query : SELECT ','+ REPLACE(wo_note, ' ', ''), basically I want to remove the characters ‘amp;’ but it does remove it when I am entering it, SELECT ','+ REPLACE(wo_note, 'amp; ', '')

I want to change this 'R&R # 10 tire' to this 'R&R # 10 tire'.

Answer

Try below,

select replace ('R&R # 10 tire','amp;','')

R&R # 10 tire

Check the dbfiddle link for the solution https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=3c78ae4653b7278bdda877c520c3742a