[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Construct a SELECT query

Posted on 2008-06-10
6
Medium Priority
?
751 Views
Last Modified: 2008-06-17
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
Comment
Question by:jramos74
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 1

Expert Comment

by:bolt81
ID: 21753347
SELECT * FROM table1 WHERE columnname LIKE '%apple%'

tutorial linked below
http://www.techonthenet.com/sql/like.php 
0
 

Author Comment

by:jramos74
ID: 21753367
Does the case format matter?  Will this query pull records with 'APPLE' too?
0
 
LVL 1

Expert Comment

by:bolt81
ID: 21753449
which version are you using?

you can use ILIKE if you are using version 10g
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:jramos74
ID: 21753743
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
 
LVL 1

Accepted Solution

by:
bolt81 earned 750 total points
ID: 21753837
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
 
LVL 20

Assisted Solution

by:gatorvip
gatorvip earned 750 total points
ID: 21754092
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question