Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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
add comment

closed as off topic by Mike Sherrill 'Cat Recall', JNK Apr 15 '13 at 12:28

Questions on Database Administrators Stack Exchange are expected to relate to database administration within the scope defined by the community. Consider editing the question or leaving comments for improvement if you believe the question can be reworded to fit within the scope. Read more about reopening questions here.If this question can be reworded to fit the rules in the help center, please edit the question.

2 Answers

up vote 0 down vote accepted
SELECT * 
FROM nbaplayermaster
WHERE   "Hamed Haddadi" LIKE Concat(Concat('%',firstname),'%') 
OR "Hamed Haddadi" LIKE  Concat(Concat('%',lastname),'%')

sqlfiddle

share|improve this answer
add comment

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');
share|improve this answer
add comment

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