Soundex search with like operator is not working in SQL Server

Soundex search with like operator is not working in SQL Server

DECLARE @FirstNameToBeSearch VARCHAR(50) = 'Ronald'

select *
from table
where Country = 'US' + ' and (Firstname like ''' + @FirstNameToBeSearch + '%'' or DIFFERENCE (Firstname,''' + @FirstNameToBeSearch + ''') in (4))';

Answer

This line

where Country = 'US' + ' and (Firstname like ''' + @FirstNameToBeSearch + '%'' or DIFFERENCE (Firstname,''' + @FirstNameToBeSearch + ''') in (4))';

Is wrong, it checks if Country equals the 75 char long string : “US and (Firstname like ‘Ronald%’ or DIFFERENCE (Firstname,’Ronald’) in (4))”

What you want is something like

select *
from table
where Country = 'US' and 
(Firstname like @FirstNameToBeSearch + '%'
or DIFFERENCE (Firstname, @FirstNameToBeSearch) in (4));