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

Answer 1

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

Answer 2

Use like this,

@SEARCHKEYWORD='%Smith%'

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

Answer 3

Put all of them together

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

Answer 4

Why not just use Full Text Search

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

Answer 5

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 Resi*g* and *G*irija 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.

Answer 6

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