SQLTeam.com | Weblogs | Forums

Search where string has multiple words




I was wondering if there is a easy way to search by the phrase in either order ...

For example :
Select FullName
Where FullName = 'John Doe'

the actual value in the table is 'Doe John'

I wish to be able to search by using either 'John Doe' or 'Doe John'


Select FullName
Where FullName = 'John Doe'
or FullName = 'Doe John'


Thanks, but i need it to be dynamic,

Full name is a search field so it won't always be John doe


Then help us help you. Provide a CREATE TABLE statement and some readily consumable data in the form of INSERT/SELECT, INSERT/VALUES, or INSERT/SELECT UNION ALL and we can show you how to do this.


Thank you for your replies, i did it a bit differently, using first and last name columns insted of full name.:

        CONCAT(first_name, (CONCAT(' ' , last_name))) COLLATE Latin1_General_CI_AS LIKE '%Search input%'
	OR concat(last_name,(concat(' ',first_name))) COLLATE Latin1_General_CI_AS like '%Search input%'

FYI collate here is only used because i had to. It will work for most without it.


That's going to be horribly slow because the functions you use on the columns will prevent index seeks (Non SARGable code).

It would be MUCH better if you would split the "Search Input" into two pieces and search for each piece avoiding any function-wrapping of columns in the table. At least then you stand a chance of getting a high performance index seek.


Thank you Jeff, will try that ! :slight_smile:


You bet. Thank you for the feedback.