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.

Did you like this post?

	subscribe via rss		subscribe via e-mail
	print this post		follow me on twitter

Feedback: