Well, as I mentioned, "YACHT AUSTRALIA [III]" is finding a match on "US". This is because you are using this bit of code: when patindex ('%US%',Term)>0
then 'US'
The "%" is a wild card, so it is saying to find the phrase "US" anywhere in the expression being searched. Remove the leading "%", and it will now find a match only on the expressions what start with "US".
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes