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

Oracle Query Problem

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
daugh016
Asked:
daugh016
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
sujith80Commented:

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
 
daugh016Author Commented:
It said "didn't expect 'instr' after the SELECT column list
0
 
sathyagiriCommented:
SELECT R.COMPONENT, decode(instr(R.COMPONENT,' '),0,R.COMPONENT,substr(R.COMPONENT,1,instr(R.COMPONENT,' ')-1))
FROM CPI.NAI_RESULTS R
0
 
daugh016Author Commented:
That was it. Thanks sathyagiri
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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