CODEWITHSUNDEEP
mysql
Carolina Vicente
Carolina Vicente

Reputation: 45

Search in Full name column with first and last name

I want to achieve is to search for a full name in a database with only a string with his first and last name.

in database there is the full name:

Manuel Augusto Vieira Conde Fialho

and I want a sql query that can search this full name only with the first and last name :

Manuel Fialho

I've tried this one:

SELECT name FROM partners WHERE name LIKE 'Manuel Fialho'";

but this does not work for me because I don't have any Manuel Fialho in the database only a Manuel Augusto Vieira Conde Fialho and because of that the name im looking for does not show.

thank you

Upvotes: 1

Views: 2055

Answers (5)

Ankit Sompura
Ankit Sompura

Reputation: 763

Try Below Query:

It works fine..!

SELECT * FROM `partners` WHERE 
`name` LIKE replace( 'Manuel Fialho', ' ', '%' ) OR
`name` LIKE replace('%Manuel Fialho%', ' ', '%' ) OR
`name` LIKE replace('%Manuel Fialho', ' ', '' ) OR
`name` LIKE replace('%Manuel Fialho%', ' ', '' );

Upvotes: -1

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

There are 2 possible options to search.

Split search string into multiple words and query with that many LIKE patterns.

select name FROM partners WHERE name LIKE '%Manuel%' or name like '%Fialho%'

If you are sure that the sequence of words in the search strings are to match with those in the column value then replace all spaces with % symbol and use with LIKE. 

select name FROM partners
 WHERE name LIKE concat( '%', replace( 'Manuel Fialho', ' ', '%' ), '%' )

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269873

One method uses wildcards:

SELECT name
FROM partners
WHERE name LIKE REPLACE('Manuel Fialho', ' ', '%');

Upvotes: 1

a45b
a45b

Reputation: 519

SELECT name FROM partners WHERE name LIKE "Manuel%" OR name LIKE "%Fialho";

If You are sure about the last name of the name then you can also use AND 

 SELECT name FROM partners WHERE name LIKE "Manuel%" AND name LIKE "%Fialho";

Upvotes: 0

Badro Niaimi
Badro Niaimi

Reputation: 957

You shoould add %:

SELECT name FROM partners WHERE name LIKE 'Manuel%Fialho';

Upvotes: 1

Related Questions