optionsix optionsix - 7 months ago 36
SQL Question

performing sql select against a full name using wildcards

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 + '%'

Obviously when a space is encountered it screws up the result set. Can someone please help me understand how to tweak this so it can match on any "amount" of the full name?

If this has already been answered, I couldn't find it... a hotlink to an existing solution would be just as appreciated here.


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.