Searching a person's Name with the First and Last Name joined together

The title may not be very clear, but let us see the problem scenario. Let us consider a table Customers with the following definition

CREATE TABLE #Customers
(CustID int, CustFName varchar(20), CustLName varchar(20), CustAge int)

INSERT INTO #Customers VALUES(1, 'Jack', 'blicak',26)
INSERT INTO #Customers VALUES(2, 'Henry', 'Snipper',36)
INSERT INTO #Customers VALUES(3, 'Jack', '',55)
INSERT INTO #Customers VALUES(4, 'Joseph', 'Befre',34)
INSERT INTO #Customers VALUES(5, 'Jill', 'blicak',26)
INSERT INTO #Customers VALUES(6, 'Jack', 'Matter',29)

Problem Statement : Assume we have to search for Jack Bilack, the string can be searched in two forms: The user passes in Jack as the first name and Bilack as the Last Name. All's fine till here. But what if the user puts 'Jack Bilack' in the first name field and passes a empty last name. Let us see how to deal with it using COALESCE.

DECLARE @fname as varchar(20)
DECLARE @lname as varchar(20)
SET @fname = 'Jack'
SET @lname = 'Blicak'

SELECT CustID, CustAge from #Customers
WHERE CustFName + coalesce(' ' + CustLName , '') = @fname + coalesce(' ' + @lname, '')

Now search the same name using the following :

SET @fname = 'Jack Blicak'
SET @lname = ''
SELECT CustID, CustAge from #CustomersWHERE CustFName + coalesce(' ' + CustLName , '') = @fname + coalesce(' ' + @lname, '')

The results are the same and as desired.

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook