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

jramos74Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
bolt81Connect With a Mentor Commented:
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
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
bolt81Commented:
which version are you using?

you can use ILIKE if you are using version 10g
0
 
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
 
gatorvipConnect With a Mentor Commented:
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
All Courses

From novice to tech pro — start learning today.