subtr returning incorrect value

chalie001
chalie001 used Ask the Experts™
on
hi am in oracle 11gR2
when i did  SELECT SUBSTR('6210 - 14 - 242 - 8258', 10, 4) FROM DUAL; am geting -2
 
where emp = substr(emid,1,4))
and empcode= substr(emid,5,2))
and empsr=substr(emid,7,4));

select cii.sin from im_item_bii bii, im_item_cii cii where nsc = '6210' and ncb = '14' and item_no = '2428258' and cii.sin = bii.sin

i what value to separated like this
6210
14
2428258
 
am in 11g
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>when i did  SELECT SUBSTR('6210 - 14 - 242 - 8258', 10, 4) FROM DUAL; am geting -2

Yes.  Column 10 starts at the '-'.

If the data has some spaces and some without and you cannot rely on substr, I would probably use regex_substr:
      regexp_substr(mycol,'[0-9]+',1,1) first_val,
      regexp_substr(mycol,'[0-9]+',1,2) second_val,
      regexp_substr(mycol,'[0-9]+',1,3) || regexp_substr(mycol,'[0-9]+',1,4) third_val
awking00Information Technology Specialist

Commented:
>>when i did  SELECT SUBSTR('6210 - 14 - 242 - 8258', 10, 4) FROM DUAL; am geting -2<<
What I really suspect you are getting is the four-character string "space minus sign space 2" that starts at the tenth character of the string which is the space after the 14. What did you expect to get?
>>i what value to separated like this<<
How? As one column with three rows or as three columns or what?
Column 10 starts at the space before the - sign, so the output is correct.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
what if value is enter like this 6240142956550
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>what if value is enter like this 6240142956550

If that is the format 100% of the time, then substr will work 100% of the time.  Just provide the correct offsets and lengths.

6240 is substr(the_value,1,4)
etc...
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
or just change your select:
select cii.sin from im_item_bii bii, im_item_cii cii where nsc||ncb||item_no = '6210142428258' and cii.sin = bii.sin
awking00Information Technology Specialist

Commented:
You might try select translate('6210 - 14 - 242 - 8258','x -','x') which removes the dashes and spaces if they exist and leaves as is if they don't.

Author

Commented:
i use substr

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial