Solved

Substr Query Question in oracle sql

Posted on 2011-03-04
3
344 Views
Last Modified: 2012-06-27
I have the following select statement with substring

SUBSTR (NVL (historical_separate.work_done, active_separate.work_done),1,INSTR (NVL(historical_separate.work_done, active_separate.work_done),',',1,1)- 1)AS "Repair Code"

say I have data in the field of M21,17,5

The above statement gives me M21

but if I have data of only M21

it gives me blank

I need to have it show M21 not blank.  How do I accomplish this?
0
Comment
Question by:JDay2
  • 2
3 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
ID: 35037785
if using 10g try...


regexp_substr(NVL (historical_separate.work_done, active_separate.work_done),'[^,]+')
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35037808
if you are using an older version that doesn't support  regular expressions try


CASE
           WHEN INSTR(NVL(historical_separate.work_done, active_separate.work_done), ',') > 0
           THEN
               SUBSTR(
                   NVL(historical_separate.work_done, active_separate.work_done),
                   1,
                   INSTR(NVL(historical_separate.work_done, active_separate.work_done), ',', 1, 1)
                   - 1)
           ELSE
               NVL(historical_separate.work_done, active_separate.work_done)
       END
0
 

Author Closing Comment

by:JDay2
ID: 35037847
Thank you for the quick response
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to base a filter depending on fields contents? 15 57
sql query 8 50
Common Records between Sub Queries 4 26
SQL Server - Set Value of Multiple Fields in One Query 10 25
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

777 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