chalie001
asked on
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
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
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Column 10 starts at the space before the - sign, so the output is correct.
ASKER
what if value is enter like this 6240142956550
>>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...
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...
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
select cii.sin from im_item_bii bii, im_item_cii cii where nsc||ncb||item_no = '6210142428258' and cii.sin = bii.sin
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.
ASKER
i use substr
Oracle Database
Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.
81K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
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?