How can I combine two columns into a one LIKE statement?

I have a lastname and a firstname.

I need to be able to search for smith or smith j.

I have this in my where clause:

lastname LIKE '%SMITH%' AND firstname LIKE '%J%'

What am I doing wrong? How can I combine the lastname and the first name for one LIKE for this? The data does have a smith j in it.

Thank you for any help开发者_运维技巧.

I am using Oracle 11g if that matters.

edit: forgot to include quotes. I did have that. thanks.

edit: It looks like I am doing it right so it must be something else.

Try:

UPPER(lastname) LIKE '%SMITH%' AND UPPER(firstname) LIKE '%J%'

Or if you really need it in one statement:

UPPER(lastname||firstname) LIKE '%SMITH%J%'

You need apostrophes around the strings:

lastname LIKE '%smith%' AND firstname LIKE '%J%'

Unless I have misunderstood your question

lastname LIKE '%smith%'

by itself will return both?

how about instead of using like, use a regular expression that matches both?

how about

(
  (lastname LIKE '%SMITH%') AND
  ( (firstname LIKE '%J%') OR (firstname IS NULL) OR (firstname ='') )
)