Filter rows on the basis of "First Name" + "Last Name" in SQL

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开发者_C百科 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'

Put all of them together

SELECT * FROM @Test WHERE (FName like '%' + @SEARCHKEYWORD + '%') OR (LName like '%' + @SEARCHKEYWORD + '%') OR (FName + ' ' + LName like '%' + @SEARCHKEYWORD + '%')

Ok, this query passes all but one of your desired results:

SELECT * FROM @Test WHERE FNAME +' '+ LNAME LIKE '%' + @SEARCHKEYWORD  + '%' 
OR FNAME LIKE '%' + @SEARCHKEYWORD  + '%' OR LNAME LIKE '%' + @SEARCHKEYWORD  + '%'

The one scenario which fails is

--WHEN @SEARCHKEYWORD='g' --Desired OUTPUT: the row which contains 'Devendra', 'Gujel' and 'Arjit', 'Gupta'

You also get John Resig and Girija Acharya for the highlisghted reason. My suggestion is allow the user to select their wildcards as part of the search. Therefore allowing them to limit "g at the beginning of the name" by using something like "g*" as their search term.

Why not just use Full Text Search

http://msdn.microsoft.com/en-us/library/ms142571.aspx

Use like this,

@SEARCHKEYWORD='%Smith%'

SELECT * FROM @Test WHERE FNAME LIKE @SEARCHKEYWORD OR LNAME LIKE @SEARCHKEYWORD

When you use like you have to include wildcards as well as the search term:

select * from names where forename like 'John%'

will find all names that start with "John"

select * from names where forename like '%Smith'

will find all names that end with "Smith"

select * from names where forename like '%g%'

will find all names that have a lower case "g" in them.

So if you are passing the search string as a parameter you'll need to build up the like statement:

select * from names where forename like @SEARCHKEYWORD + '%' or
                          surname like  @SEARCHKEYWORD + '%'

will find names where the forename or surname start with the search keyword

There's more than one way to skin a cat but this is one of them.

The idea goes like this

• Create another variable @LIKE_SEARCHKEYWORD to contain a massaged input string. In the example, spaces are removed from @SEARCHKEYWORD.
• Create an inner join retrieving all possible combinations of firstname-lastname.
• Let the LIKE statement only worry about the results of all combinations.

Performance wise, this might not be the best solution but it does keep things pretty clean. If your input table is not to big, the performance hit will be negligable.

SQL Statement

DECLARE @LIKE_SEARCHKEYWORD NVARCHAR(100)
SET @LIKE_SEARCHKEYWORD = '%' + REPLACE(@SEARCHKEYWORD, ' ', '') + '%'

SELECT  DISTINCT t.*
FROM    @Test t
        INNER JOIN (
          SELECT ID, Name = FName FROM @Test 
          UNION ALL SELECT ID, LName FROM @Test
          UNION ALL SELECT ID, FName + LName FROM @Test
          UNION ALL SELECT ID, LName + FName FROM @Test
        ) n ON n.ID = t.ID
WHERE   n.Name LIKE @LIKE_SEARCHKEYWORD