Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 770
  • Last Modified:

Construct a SELECT query

How do I construct a SELECT query with a WHERE CLAUSE?
For example:
I want to find all records with columnname that has an apple word in it.
I know this query is wrong.
SELECT * FROM TABLE1 WHERE COLUMNNAME is in ('APPLE TART', 'apple', 'apple pie', 'Candy Apple');

  • 3
  • 2
2 Solutions
SELECT * FROM table1 WHERE columnname LIKE '%apple%'

tutorial linked below 
jramos74Author Commented:
Does the case format matter?  Will this query pull records with 'APPLE' too?
which version are you using?

you can use ILIKE if you are using version 10g
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

jramos74Author Commented:
Can you give me a select statement using ilike?  I tried it and it did not work.  We have 10g.  My query is SELECT * FROM TABLE1 where columnname ilike '%apple%' and it did not work.
SELECT * FROM table1 WHERE UPPER(columnname) LIKE ('%APPLE%')

the upper makes everything upper case so it will find apple and APPLE the % are wild cards so it will find apple in the middle of results as well e.g it will find "red apple" as well as "apple pie".

if you use _ this is a wild card for a single character eg 'APPLE_' will find apples but not bapples or red apple or apple pie

hope this helps
ILIKE is a PostgreSQL keyword, not Oracle

You can also use  INSTR:

select *
from table1
where INSTR(lower(columnname), 'apple')>0

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now