3

I want to search a name 'Hamed Haddadi'. 'Hamed' is first name and 'Haddadi' is the last name of the user. When I search individually in the first name and last name columns it shows a record.

My query is:

SELECT * 
FROM nbaplayermaster 
WHERE firstname LIKE "%Hamed%" OR 
      lastname LIKE "%Haddadi%";

But whenever I search his name in two columns, it shows an empty result set in MySQL.

My query is:

SELECT * 
FROM nbaplayermaster 
WHERE firstname LIKE "%Hamed Haddadi%" OR 
      lastname LIKE "%Hamed Haddadi%";

I want to search his full name in two columns.

4
  • 1
    Please present data from your table – Shlomi Noach Apr 15 '13 at 6:14
  • 1
    You can use sqlfiddle (sqlfiddle.com) for this and share the link afterwards. As you've stated that the firstname is "Hamed" and "Haddadi" his last name, I do not understand why you expect to find someone whose first name is "Hamed Haddadi" or his last name is "Hamed Haddadi". @moderators: should this question be migrated to stackoverflow? – KookieMonster Apr 15 '13 at 6:34
  • @ShlomiNoach data of nbaplayermaster. Please click this link imgur.com/lVN3WLm – UdayKiran Pulipati Apr 15 '13 at 6:59
  • Please do not ask very basic SQL questions here. I realize you can't ask questions on SO, that doesn't mean it's OK to ask questions that are off-topic on our site. – JNK Apr 15 '13 at 12:29
1
SELECT * 
FROM nbaplayermaster
WHERE   "Hamed Haddadi" LIKE Concat(Concat('%',firstname),'%') 
OR "Hamed Haddadi" LIKE  Concat(Concat('%',lastname),'%')

sqlfiddle

8

I suspect that Hamed is the first name and Haddadi is the last name, you can do it in 3 ways;

Solution 1:

Split the query in name and last name:

SELECT * 
FROM nbaplayermaster 
WHERE firstname LIKE "%Hamed%" OR 
      lastname LIKE "%Hamed%";

Solution 2:

Use the wildcard % in between the names(spaces)

SELECT * 
FROM nbaplayermaster 
WHERE firstname LIKE "%Hamed%Haddadi%" OR 
      lastname LIKE "%Hamed%Haddadi%";

Solution 3:(recommended)

Add a fulltext index on this 2 columns:

ALTER TABLE nbaplayermaster ADD FULLTEXT KEY `full_name` (`firstname`,`lastname`);

and then search using the fulltext index

SELECT * FROM nbaplayermaster WHERE MATCH(firstname,lastname) AGAINST ('Hamed Haddadi');
1
  • Solution 2 worked for me – kscius Sep 11 '19 at 21:35

Not the answer you're looking for? Browse other questions tagged or ask your own question.