Hi guys,
I've this "conversion" issue, I hope you might help.
I've this table test
> create table text (tex varchar2(20))
> insert into text values ('010 AAA') ; " 8 empty spaces between 010 and AAA"
>insert into text values ('010001 ABCD');
>insert into text values ('010001 FGHJ')
| tex |
| 010 AAA |
| 010001 ABCD |
| 010001 FGHJ |
I need to get this result to insert another table where col 3 muts be converted into integer
| col1 | col2 | col3 | col4 |
| 01 | 0 | | AAA |
| 01 | 0 | 001 | ABCD |
| 01 | 0 | 001 | FGHJ |
So, when I run this quesry :
select
substr(tex,1,2) as col1,
substr(tex,3,1) as col2,
cast(substr(tex,4,3) as col 3,
substr(tex,7,6) as col 4
from text
I get this message : ORA 01722 invalid number
I understand why, because first line have empty spaces,
My Quastion, how shal I proceed ?
Thank you if you can help.
Carl