?
Solved

Oracle Query Problem

Posted on 2006-07-24
5
Medium Priority
?
994 Views
Last Modified: 2008-03-17
Here is my query:

SELECT R.COMPONENT, substr(R.COMPONENT,1,instr(R.COMPONENT,' ')-1)
FROM CPI.NAI_RESULTS R

It is looking a row and seeing if R.COMPONENT has a space in it, then it will get all the information before the first space.

That is correct.

My issue:

I want to return the word if there is no space at all.  Here is some examples:

R.COMPONENT
-----------------
one two three    <--       should return one (this would work currently)
one two            <--       should return one (this would work currently)
one                  <--       should return one (this doesn't work right now.  It returns nothing.)

Thanks
0
Comment
Question by:daugh016
[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
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17167606
SELECT R.COMPONENT, CASE WHEN instr(R.COMPONENT,' ') = 0 THEN r.COMPONENT ELSE substr(R.COMPONENT,1,instr(R.COMPONENT,' ')-1) END as Component
FROM CPI.NAI_RESULTS R
0
 
LVL 27

Expert Comment

by:sujith80
ID: 17167931

SELECT R.COMPONENT, substr(R.COMPONENT,1, decode(instr(R.COMPONENT,' '), 0, length(R.COMPONENT), instr(R.COMPONENT,' ')-1))
FROM CPI.NAI_RESULTS R
0
 

Author Comment

by:daugh016
ID: 17167939
It said "didn't expect 'instr' after the SELECT column list
0
 
LVL 14

Accepted Solution

by:
sathyagiri earned 500 total points
ID: 17168126
SELECT R.COMPONENT, decode(instr(R.COMPONENT,' '),0,R.COMPONENT,substr(R.COMPONENT,1,instr(R.COMPONENT,' ')-1))
FROM CPI.NAI_RESULTS R
0
 

Author Comment

by:daugh016
ID: 17168168
That was it. Thanks sathyagiri
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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
Suggested Courses

752 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