Home » SQL & PL/SQL » SQL & PL/SQL » to_char function
to_char function [message #297330] Thu, 31 January 2008 02:53 Go to next message
santoshi.sahoo
Messages: 9
Registered: January 2008
Location: bangalore
Junior Member
hi,

when i am querying to_char(some number let it('98896775757'),9)


its giving me ##
so can any one tell me whts 9 is doing.

Regards,
Santoshi
Re: to_char function [message #297331 is a reply to message #297330] Thu, 31 January 2008 03:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
TO_CHAR

Regards
Michel

[Mod-edit: Frank changed destination of hyperlink; was referring to local file]

[Updated on: Thu, 31 January 2008 03:21] by Moderator

Report message to a moderator

Re: to_char function [message #297335 is a reply to message #297330] Thu, 31 January 2008 03:06 Go to previous messageGo to next message
santoshi.sahoo
Messages: 9
Registered: January 2008
Location: bangalore
Junior Member
hi,
I think u didnt get my question.........
when i m querying i.e select TO_CHAR('9878966',9) from dual its giving the result ## and agian select TO_CHAR('9878966',99) from dual its giving ###....like that


but for select TO_CHAR('9878966',1 or 2 or any number) from dual its giving invalid identifier........

so i m asking what is significant of 9 here...


Regards,

Santoshi
Re: to_char function [message #297338 is a reply to message #297335] Thu, 31 January 2008 03:13 Go to previous messageGo to next message
Serious Sam
Messages: 21
Registered: September 2007
Junior Member

SQL> select TO_CHAR('9878966',9) from dual
  2  /

TO
--
##

SQL> select TO_CHAR('9878966',9999999) from dual
  2  /

TO_CHAR(
--------
 9878966

Re: to_char function [message #297342 is a reply to message #297338] Thu, 31 January 2008 03:21 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I changed Michel's link.
Try it again.
Re: to_char function [message #297346 is a reply to message #297342] Thu, 31 January 2008 03:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks Frank, I pick up the wrong window. Confused

Regards
Michel
Re: to_char function [message #297352 is a reply to message #297330] Thu, 31 January 2008 04:01 Go to previous messageGo to next message
santoshi.sahoo
Messages: 9
Registered: January 2008
Location: bangalore
Junior Member
Hi,


I got it thank you............but again i want that:

when both are of same digit at that time its giving me the same number otherwise its giving #....why is it so?????????
i.e to_char(9888,9) ->##
but to_char(8,9) ->8



Regards,
Santoshi
Re: to_char function [message #297355 is a reply to message #297352] Thu, 31 January 2008 04:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First the second parameter is a STRING, not a number.
Then tell me how you can display a 4 digits number with only 1 character.

Regards
Michel

[Updated on: Thu, 31 January 2008 04:08]

Report message to a moderator

Re: to_char function [message #297357 is a reply to message #297352] Thu, 31 January 2008 04:12 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
santoshi.sahoo wrote on Thu, 31 January 2008 11:01
why is it so?????????

OracleŽ Database SQL Reference 10g Release 2 (10.2), 2 Basic Elements of Oracle SQL, Format Models
If a value has more significant digits to the left of the decimal place than are specified in the format, then pound signs (#) replace the value.

MHE
Re: to_char function [message #297358 is a reply to message #297330] Thu, 31 January 2008 04:12 Go to previous messageGo to next message
santoshi.sahoo
Messages: 9
Registered: January 2008
Location: bangalore
Junior Member
hi,

Thank you...........

but why #...wht is the significance of #




Regrads,
santoshi
Re: to_char function [message #297360 is a reply to message #297358] Thu, 31 January 2008 04:16 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Because Oracle decided so. No special meaning AFAIK, just a convention.

MHE
Re: to_char function [message #297363 is a reply to message #297358] Thu, 31 January 2008 04:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why is it # in Excel or Open Office or...?

Regards
Michel
Re: to_char function [message #297365 is a reply to message #297330] Thu, 31 January 2008 04:25 Go to previous message
santoshi.sahoo
Messages: 9
Registered: January 2008
Location: bangalore
Junior Member
hi,






ok i got it...............thank you so much...........



Regards,
Santoshi
Previous Topic: text files with column names
Next Topic: Inserting BLOB (image file) in database through SQL Query
Goto Forum:
  


Current Time: Mon Dec 05 06:33:55 CST 2016

Total time taken to generate the page: 0.05146 seconds