select substr(doc_no, 1, 6) dob, case when 5000 > to_number(substr(doc_no, 7, 4)) then 'Male' else 'Female' end gender, substr(doc_no, 11) nationality
Not sure of which 0 you were pointing at in the Nationality, but you can for sure modify the last substr to deduce that field.
> DOB : 900507- yyddmm
Are you really planning on storing a DATE without the century part?
Better make it YYYYDDMM or else your code will break sooner or later (remember the 'Y2K'- problem?)
You can meet the requirement if you play a bit with CASE, SUBSTR and INSTR.
All those functions are documented here Oracle Database Online Documentation 11g Release 2 (11.2) and you can find examples if you search this forum.
What datatype are you currently using?
If it is not a DATE, then please adjust the design and make it of DATE datatype, and you won't have to worry about storing in whatever format.
Formats should only matter when you retrieve the dates, and then you simply use TO_CHAR.
Please read: NLS_DATE_FORMAT | Ed Stevens, DBA