• Not totaly clear about if you wanted to remove all spaces or just leading?? If you are tataly striping the characters just use a series of nested "REPLACE" statements.

    DECLARE @str1 VARCHAR(100), @str2 VARCHAR(100), @str3 VARCHAR(100)

    SET @str1 = 'A & B Cleaners'

    SET @str2 = 'A and B Cleaners'

    SET @str3 = 'Cleaners A & B'

    SELECT '*'+@str1+'*', '*'+REPLACE(REPLACE(@str1, ' ', ''), '&', '')+'*'

    SELECT '*'+@str2+'*', '*'+REPLACE(REPLACE(@str2, ' ', ''), '&', '')+'*'

    SELECT '*'+@str3+'*', '*'+REPLACE(REPLACE(@str3, ' ', ''), '&', '')+'*'

    String Result

    *A & B Cleaners**ABCleaners*

    *A and B Cleaners**AandBCleaners*

    *Cleaners A & B**CleanersAB*