Home » SQL & PL/SQL » SQL & PL/SQL » int to varchar problem ? (Oracle 10g)
int to varchar problem ? [message #407499] Wed, 10 June 2009 06:10 Go to next message
rangan.s
Messages: 75
Registered: February 2008
Location: chennai
Member
create table test_a3(c numeric(32,8),d numeric(32,8),e datetime default getdate());

insert into test_a3(c,d) values(00.141,1.1245);
insert into test_a3(c,d) values(00.142,1.1246);
insert into test_a3(c,d) values(00.143,1.1247);

 

create table test_a4(c varchar(255),d varchar(255),e varchar(255));

insert into test_a4 select * from test_a3;

select * from test_a3;

select * from test_a4;



the tables in the test_a4 is ignore the '0' can anyone help how to achieve this ?
Re: int to varchar problem ? [message #407502 is a reply to message #407499] Wed, 10 June 2009 06:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A number has no leading 0.
Use TO_CHAR to format.

Regards
Michel
Re: int to varchar problem ? [message #408072 is a reply to message #407502] Sat, 13 June 2009 10:25 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
as Michel says

the zero has not been ignored. It was never stored. Those zeroes are called "insignificant digits". Read up on it if you do not know what it means.

.123
0.123
00.123
00.12300

In the above examples, for most situtations, all zeroes are insignificant. They are not stored when the number is saved as a number.

Secondly, unless you supply a format string when converting a number to a character, you will not get any significant digits.

select to_char(00.12300) string1,to_char(.123,'00.00000') string2 from dual;

SQL> select to_char(00.12300) string1,to_char(.123,'00.00000') string2 from dual;

STRI STRING2
---- ---------
.123  00.12300

1 row selected.


Good luck, Kevin
Previous Topic: EXECUTE IMMEDIATE STORED proc having IN and OUT parameters - 10g
Next Topic: Monthly Running Totals (merged)
Goto Forum:
  


Current Time: Thu Dec 08 12:37:54 CST 2016

Total time taken to generate the page: 0.05259 seconds