I am going to have a search field that just says 'Name'
The user can type in any value in the search field whether it be a full name, first, or last.
How can I create a optimal query for the value inputed.
Case 1 - If they type in John then I can say firstname like 'John%' or lastname like 'John%'
Case 2 - If they type in John Adams then I can return both values between spaces as separate values i guess firstname like 'john%' and lastname like 'adams%'
Case 3 - If they type in Adams, John I guess I can look for a comma and the presume the value before the comma to be the last name and the value after to be the lastname.
These are pretty much all of the cases I could think of.
Also would it be worth it to have the wildcard before the value as well like '%John%' instead of 'John%' ?
Please let me know has any ideas or if they have done something similar.