Trying to find substring in string on sql server

Question

I have read in many articles and including here on stackoverflow that to find a substring the following should be used:

IF CHARINDEX('mywoRD', @words) > 0
BEGIN
    -- DO SOMETHING
END

I am trying the following but it's not working it always returns wrong:

Say i have a string 'Basketball & Soccer', I am trying to write a script that checks for the ampersand and encodes it to & so it becomes 'Basketball & Soccer' My problem is that there may be another one like this in the database with 'Basketball & Soccer'.

So when I run my script, for the second one it becomes:

'Basketball & Soccer'

I am trying the following to clean it:

DECLARE @cleanparam as varchar(500)   

IF CHARINDEX('&',@myparameter) > 0 
BEGIN 
   -- if & is in the string skip and start with quotes
   SELECT @cleanparam = REPLACE(@myparameter,'"','"')   
END
ELSE
 BEGIN
      -- if not clean &'s and quotes and then continue with others
      SELECT @cleanparam = REPLACE(@myparameter,'&','&')   
      SELECT @cleanparam = REPLACE(@cleanparam,'"','"')   
 END

      SELECT @cleanparam = REPLACE(@cleanparam, '©', '©')
      SELECT @cleanparam = REPLACE(@cleanparam, '«', '«') 
       -- OTHERS HERE

Answer 1

Try this

DECLARE @myparameter as varchar(500) 
set @myparameter = 'Basketball & Soccer & Volleyball'


DECLARE @cleanparam as varchar(500)
SELECT @cleanparam = REPLACE(@myparameter,'&amp;','&') --<----put some trick here :)
SELECT @cleanparam = REPLACE(@cleanparam,'&','&amp;')   
SELECT @cleanparam = REPLACE(@cleanparam,'"','&quot;') 
SELECT @cleanparam = REPLACE(@cleanparam, '©', '&copy;')
SELECT @cleanparam = REPLACE(@cleanparam, '«', '&laquo;') 

SELECT @cleanparam