How to improve LIKE Operator performance

Source : How to improve LIKE Operator performance


I have a ZIPCode list in a temp table (10,000 records) . I need to get all records where the zip code MATCH with the zip codes in the list.
If I do exact match, I get result in 1-2 second ( first query) but if I do a LIKE as in second query it takes more than a minute.

First query use Hash Join where other do Nested Loop.

Any way we can improve the performance of second query?


SELECT count(*) FROM Panelist (NOLOCK)
WHERE   EXISTS (SELECT TOP 1 1 FROM ##ZIPLIST WHERE Panelist.PostalCodeVal = VALUE )
go
SELECT count(*)  FROM Panelist (NOLOCK)
WHERE   EXISTS (SELECT TOP 1 1 FROM ##ZIPLIST WHERE Panelist.PostalCodeVal like VALUE +'%')
go