How about:

SQL> col value format a12
SQL> col sub1 format a8
SQL> col sub2 format a6
SQL> col result format a12
SQL> define test='999 -99'
SQL>
SQL> select '"'||'&test'||'"' value,
2 '"'||substr('&test',1,instr('&test','-')-1)||'"' sub1,
3 '"'||substr('&test',instr('&test','-')+1)||'"' sub2,
4
'"'||substr(to_char(to_number(substr('&test',1,instr('&test','-')-1)),'0
00009'),2)||
5 '-'||
6
substr(to_char(to_number(substr('&test',instr('&test','-')+1)),'009'),2)
||'"' result
7 from dual
8 /

VALUE SUB1 SUB2 RESULT
~~~~~~~~~~~~ ~~~~~~~~ ~~~~~~ ~~~~~~~~~~~~
"999 -99" "999 " "99" "000999-099"

-----Original Message-----
From: krishnakumar
[mailto:oracledba-ezmlmshield-x61674537.[Email address protected]
Sent: Friday, May 25, 2007 3:05 AM
To: LazyDBA Discussion
Subject: SQL Help


Hi Folks,
Values format to One of table column should be like this 999999-999.

But if the incoming file comes with 999 -99 then it should be formated
to 000999-099.
How I do achieve this.? Help me out from this issue.

Thanks
Krishna



The information contained in this electronic message and any attachments
to this message are intended for the exclusive use of the addressee(s)
and may contain proprietary, confidential or privileged information. If
you are not the intended recipient, you should not disseminate,
distribute or copy this e-mail. Please notify the sender immediately and
destroy all copies of this message and any attachments.

WARNING: Computer viruses can be transmitted via email. The recipient
should check this email and any attachments for the presence of viruses.
The company accepts no liability for any damage caused by any virus
transmitted by this email.

www.wipro.com


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To post a
dba job: http://jobs.lazydba.com To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

Oracle LazyDBA home page