Improve company productivity with a Business Account.Sign Up

x
  • 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');

0
jramos74
Asked:
jramos74
  • 3
  • 2
2 Solutions
 
bolt81Commented:
SELECT * FROM table1 WHERE columnname LIKE '%apple%'

tutorial linked below
http://www.techonthenet.com/sql/like.php 
0
 
jramos74Author Commented:
Does the case format matter?  Will this query pull records with 'APPLE' too?
0
 
bolt81Commented:
which version are you using?

you can use ILIKE if you are using version 10g
0
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.
0
 
bolt81Commented:
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
0
 
gatorvipCommented:
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

0
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