current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.4 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote -2 down vote favorite

I have a database table "person" with two columns firstName and lastName. Now I want to select all the persons whose "firstName lastName" contains a given name. How can i do it with a SQL query?

Example:

Person x with firstName: A B and lastName: C

Person y with firstName: A and lastName: B C

Person z with firstName: A B and lastName: C D

All of these persons should be shown in output if the query is with a criteria that the fullName (firstName lastName) contain B C

The table was designed that it does not have fullName column.

share|improve this question
    
What have you tried so far? Please post your SQL query. – Marc K Jun 12 '15 at 20:54
    
Please show at least ERD ,OR Table structure Please Read How To ask question – Sharif Jun 12 '15 at 20:57
    
What i did is i select all of person from database and created a filter in source code. The class Person is mapped to Table Person and i created a method to get the full name and did followed checking: if(person.getFullName().toLowerCase().contains(name.toLowerC‌​ase())) { foundPersons.add(person); } it works also but i think it is not the effective solution. – Quang Nguyen Jun 12 '15 at 21:34
up vote 2 down vote accepted

I think the following would do what you want:

where concat(firstname, ' ', lastname) like concat('%', replace($search, ' ', '%'), '%')

This replaces the spaces in the search term with wildcards, so it could conceivably match a few other situations.

share|improve this answer
    
Thanks! It works like a charm! – Quang Nguyen Jun 12 '15 at 21:23
up vote 0 down vote

I Think that is what you asked

SELECT First_Name+' '+Last_Name As Full_Name
From Table_xyz where
First_Name in(a,ab, b)
And Last_Name in (b,c,bc,cd)
share|improve this answer
up vote 0 down vote

Not sure that I followed 100% but it seems like you want to select every person whose name contains both B and C. If that's the case I'd go with something like this:

SELECT (firstName, lastName) as FullName from person where 
(firstName like '%B%' OR lastName like '%B%') and 
(firstName like'%C%' or lastName like '%C%');

for a more general solution involving a single string such as 'AL' go with

select * from person where (firstName like 
'%$stringVariable%' OR lastName like '%$stringVariable');
share|improve this answer
    
It was just a example and you don't know how long the given name is. What i need is a general solution. – Quang Nguyen Jun 12 '15 at 21:07
    
alright, if you only need to look for one string at a time for instance any name containing "AL" you could still use the solution I gave, you'd simply switch the B and C to a variable. I'll edit my answer to reflect it. – JoshGivens Jun 12 '15 at 21:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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