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
am in 11g
Who is Participating?
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

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?
Column 10 starts at the space before the - sign, so the output is correct.
Get Blueprints for Increased Customer Retention

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

chalie001Author Commented:
what if value is enter like this 6240142956550
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)
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
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.
chalie001Author Commented:
i use substr
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

From novice to tech pro — start learning today.