[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 388
  • Last Modified:

Substr Query Question in oracle sql

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
JDay2
Asked:
JDay2
  • 2
1 Solution
 
sdstuberCommented:
if using 10g try...


regexp_substr(NVL (historical_separate.work_done, active_separate.work_done),'[^,]+')
0
 
sdstuberCommented:
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
 
JDay2Author Commented:
Thank you for the quick response
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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