I have a stored procedure that I am passing in a general string variable called @SearchText as a varchar. This variable contains names, either part of a name, or a full name. I need to do a select on a table based on this variable, using wildcards. The inbound variable could be anything like (for the name 'john smith'):
'j', 'joh', 'john', 'sm', 'smith', 'john s', john smith'... you get the point.
So, the blunt approach I took is
select x from TableA
where FirstName like '%' + @SearchText + '%'
OR LastName like '%' + @SearchText + '%'
I might suggest something like this:
where FirstName + ' ' + Lastname like '%' + replace(@Searchtest, ' ', '%') + '%' or LastName + ' ' + Firstname like '%' + replace(@Searchtest, ' ', '%') + '%'
However, if you are trying to do such full text searches, you might consider using a full text index. That generally provides the right level of functionality for these types of queries.