CODEWITHSUNDEEP
oracle-databasesql-like
Ana Mar
Ana Mar

Reputation: 31

How to use two LIKE conditions?

I need to find students with A and M in their first name but that do not have an a and m in their last name. This is my query but for some reason it keeps displaying last names that have M's... What am I doing wrong? 

SELECT (firstname || ' ' || lastname) AS "FullName"
  FROM A5
 WHERE Lower(FirstName) LIKE '%a%m%' 
   AND Lower(LastName) NOT LIKE '%a%m%'
ORDER BY LastName,
         FirstName

Upvotes: 0

Views: 90

Answers (2)

JavaHopper
JavaHopper

Reputation: 5607

SELECT (firstname || ' ' || lastname) AS "FullName"
FROM A5
WHERE (lower(firstname) like '%a%' and lower(firstname) like '%m%') and ((lower(lastname) like '%a%' and lower(lastname) not like '%m%') or (lower(lastname) not like '%a%' and lower(lastname) like '%m%') )
ORDER BY LastName,FirstName

Try this.

Upvotes: 1

bluemax
bluemax

Reputation: 51

WHERE clause should be:

WHERE (Lower(FirstName) LIKE '%a%m%'
OR Lower(FirstName) LIKE '%m%a%')
AND Lower(LastName) NOT LIKE '%a%'
AND Lower(LastName) NOT LIKE '%m%'

You were only including cases where FirstName had an a followed by an m. Similarly, you were only excluding the cases where LastName had both an a AND an m, where the a comes before the m.

Upvotes: 1

Related Questions