subtr returning incorrect value

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
chalie001Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) 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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Information Technology SpecialistCommented:
>>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?
0
pcelbaCommented:
Column 10 starts at the space before the - sign, so the output is correct.
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

chalie001Author Commented:
what if value is enter like this 6240142956550
0
slightwv (䄆 Netminder) 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...
0
slightwv (䄆 Netminder) 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
0
awking00Information Technology SpecialistCommented:
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.
0
chalie001Author Commented:
i use substr
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.