Filter rows on the basis of "First Name" + "Last Name" in SQL
- by Raghav Khunger
Hi,
I have a user table in my database which contains two columns FirstName and LastName.
Now in my front end there is a textbox to filter out the users from this table. Let's suppose I am taking that input from the front end in the form of a input parameter "@SEARCHKEYWORD". I have created a sample below:
DECLARE @Test TABLE
([ID] INT IDENTITY,
[FNAME] NVARCHAR(100),
[LNAME] NVARCHAR(100)
)
INSERT INTO @Test( FNAME, LNAME )
SELECT 'John','Resig' UNION ALL
SELECT 'Dave','Ward' UNION ALL
SELECT 'Peter','Smith' UNION ALL
SELECT 'Dave','Smith' UNION ALL
SELECT 'Girija','Acharya' UNION ALL
SELECT 'Devendra', 'Gujel' UNION ALL
SELECT 'Arjit', 'Gupta'
DECLARE @SEARCHKEYWORD NVARCHAR(100)
SELECT * FROM @Test WHERE FNAME +' '+ LNAME LIKE @SEARCHKEYWORD
i.e. so far I have thought of this query to filter out the rows but it is not giving the desired results:
SELECT * FROM @Test WHERE FNAME +' '+ LNAME LIKE @SEARCHKEYWORD
Here are the desired outputs which I needed for the inputs mentioned below:
--WHEN @SEARCHKEYWORD='John Resig'
--Desired OUTPUT: the row which contains 'John','Resig'
--WHEN @SEARCHKEYWORD='Ac'
--Desired OUTPUT: the row which contains 'Girija','Acharya'
--WHEN @SEARCHKEYWORD='Smith'
--Desired OUTPUT: the row which contains 'Peter','Smith' and 'Dave','Smith'
--WHEN @SEARCHKEYWORD='g'
--Desired OUTPUT: the row which contains 'Devendra', 'Gujel' and 'Arjit', 'Gupta'
--WHEN @SEARCHKEYWORD='Smith'
--Desired OUTPUT: the row which contains 'Peter','Smith' and 'Dave','Smith'
