File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and the fly likes Wildcards in Oracle to_char format string Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Wildcards in Oracle to_char format string" Watch "Wildcards in Oracle to_char format string" New topic

Wildcards in Oracle to_char format string

Tim McGuire
Ranch Hand

Joined: Apr 30, 2003
Posts: 820

This is an Oracle database and I'm doing this:

If I use that statement and there is a credit limit over $1,000,000.00, I will get hash marks in the result. I know I can add another "9" to the format, but is there a way to give a wildcard in the format string so that it can handle as large a number as I throw at it?

chris webster

Joined: Mar 01, 2009
Posts: 1617

Easiest thing is simply to make the format string long enough to cover any value, because Oracle will only use the format elements it needs for a given value. For example:

This returns $1,234,567.89 for banker_sal, and $12,345.00 for programmer_sal.

But remember that you can also use a format like "L999G999G999G999D99" to allow Oracle to use local language settings for currency (L), group separator (G) and decimal separator(D):

This returns £1,234,567.89 for banker_sal, and £12,345.00 for programmer_sal on my system, because I'm using UK settings.

See Oracle 11g docs on TO_CHAR for more format examples.

No more Blub for me, thank you, Vicar.
It is sorta covered in the JavaRanch Style Guide.
subject: Wildcards in Oracle to_char format string
Similar Threads
Cast number to varchar in select
inserting a sequence value to the table
DB2 Date format to Oracle Date format?
Ordering the retrieved dates from Database
Problem in using triggers and sequences