SQL find same value on multiple filelds with like operator

Question

I have this records from my users table:

user_id first_name  last_name   gender  email
******* **********  *********   ******  *****
229     Natalie     Fern        F   [email protected]

and I want to search same First Name & Last Name from first_name OR last_name.

I have created sql query but not getting record.

SELECT *
FROM user_detail
WHERE first_name LIKE '%Natalie Fern%'
OR last_name LIKE '%Natalie Fern%'
LIMIT 0 , 30

You notice that I am passing first & last name from both fields.

Any Idea why I am not getting records?

Thanks.

Answer 1

You are using the LIKE operator for first_name and last_name as follows:

LIKE '%Natalie Fern%'

This will only match strings which contain anything followed by 'Natalie Fern' followed by anything. But since the first and last name columns only contain (surprise) the first and last names, your query isn't matching any records. You can use CONCAT to try to match the combination of first and last names:

SELECT *
FROM user_detail
WHERE CONCAT(first_name, ' ', last_name)
LIKE '%Natalie Fern%'
LIMIT 0, 30

If you want to check for people whose first or last names could be 'Natalie' or 'Fern', then you could use this query:

SELECT *
FROM user_detail
WHERE first_name LIKE '%Natalie%'
OR first_name LIKE '%Fern%'
OR last_name LIKE '%Natalie%'
OR last_name LIKE '%Fern%'
LIMIT 0, 30

Answer 2

Try this

Sql Fiddle

Function

CREATE  FUNCTION [dbo].[fn_Split](@text varchar(8000), @delimiter varchar(20))
RETURNS @Strings TABLE
(   
  position int IDENTITY PRIMARY KEY,
  value varchar(8000)  
)
AS
BEGIN

DECLARE @index int
SET @index = -1

WHILE (LEN(@text) > 0)
  BEGIN 
    SET @index = CHARINDEX(@delimiter , @text) 
    IF (@index = 0) AND (LEN(@text) > 0) 
      BEGIN  
        INSERT INTO @Strings VALUES (@text)
          BREAK 
      END 
    IF (@index > 1) 
      BEGIN  
        INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))  
        SET @text = RIGHT(@text, (LEN(@text) - @index)) 
      END 
    ELSE
      SET @text = RIGHT(@text, (LEN(@text) - @index))
    END
  RETURN
END

Query

SELECT * FROM user_detail inner join 
(select value from fn_split('Natalie Fern',' ')) as split_table 
on (user_detail.first_name like '%'+split_table.value+'%' 
or user_detail.last_name like '%'+split_table.value+'%' ;

Answer 3

You get no records because no column contains somethink like "Natalie Fern".

You have to concat both columns and you should get the correct result.

But concatination of columns is not a standard. So you have to look in the documantation of your DBMS.

Here some examples:

• MySQL: CONCAT( )
• Oracle: CONCAT( ), ||
• SQL Server: +

Answer 4

instead of LIKE you can use LOCATE to see if the first name is in a string

SELECT *
FROM user_detail
WHERE LOCATE(first_name,'Natalie Fern') > 0
OR LOCATE(last_name ,'Natalie Fern') > 0 
LIMIT 0 , 30

Answer 5

If you need to perform this search with one search input field:

SELECT * 
FROM user_detail 
WHERE last_name LIKE '%###VALUE###%' 
OR first_name LIKE '%###VALUE###%' 
OR concat(last_name, ' ', first_name) LIKE '%###VALUE###%' 
OR concat(first_name, ' ', last_name) LIKE '%###VALUE###%';