SQLANDPLSQL.COM

PosgreSQL to_char is one of the most used formatting function

There are two broad category for to_char in PostgreSQL ,TO_CHAR with numeric data type and TO_CHAR with date/time data type. In this post we will examine TO_CHAR with numeric data type

TO_CHAR with numeric data type

select to_char(2014,’9999′); → ‘2014’, i.e to_char convert integer 2014 to string 2014

select to_char(15,’99’); → ’15’, i.e to_char convert integer 15 to string 15

to_char ( numeric_type, pattern) → text , we can use below Patterns for Numeric Formatting

Pattern	Description
9	digit position (can be dropped if insignificant)
0	digit position (will not be dropped, even if insignificant)
. (period)	decimal point
, (comma)	group (thousands) separator
PR	negative value in angle brackets
S	sign anchored to number (uses locale)
L	currency symbol (uses locale)
D	decimal point (uses locale)
G	group separator (uses locale)
MI	minus sign in specified position (if number < 0)
PL	plus sign in specified position (if number > 0)
SG	plus/minus sign in specified position
RN	Roman numeral (input between 1 and 3999)
TH or th	ordinal number suffix
V	shift specified number of digits (see notes)
EEEE	exponent for scientific notation

Some more examples

select to_char(125, ‘999’);	‘999’
select to_char(125.8::real, ‘999D9’);	125.8
select to_char(-125.8, ‘999D99S’);	125.80-
select to_char(-0.1, ‘99.99’);	-.10
select to_char(11.1, ‘99.99’);	11.10
select to_char(12, ‘0999’);	0012
select to_char(2, ‘0999’);	0002
select to_char(12564, ‘999,99’);	125,64
select to_char(1256, ‘999,99’);	12,56
select to_char(123, ‘9 9 9’);	1 2 3
select to_char(1234, ‘9G999’);	1,234
select to_char(0.1, ‘0.9’);	0.1
select to_char(123.5, ‘FM999.999’);	123.5
select to_char(123, ‘L999’);	$ 123
select to_char(123.5, ‘FM999.990’);	123.500
select to_char(-123, ‘999S’);	123-
select to_char(-123, ‘999MI’);	123-
select to_char(148.5, ‘999D999’);	123.500
to_char(3148.5, ‘9G999D999’);	1,234.500
select to_char(123, ‘PL999’);	+ 123
select to_char(123, ‘SG999’);	+123
select to_char(-123, ‘9SG99’);	1-23
select to_char(-123, ‘999PR’);	<123>
select to_char(123, ‘RN’);	CXXIII
select to_char(123, ‘FMRN’);	CXXIII
select to_char(53, ‘FMRN’);	LIII
select to_char(123, ‘999th’);	123rd
select to_char(585, ‘999th’);	585th
select to_char(585, ‘999TH’);	585TH
select to_char(123, ‘”Good number:”999’);	Good number: 123
select to_char(12, ’99V999′);	12000
select to_char(12, ’99V999′);	12400
select to_char(0.0001234, ‘9.99EEEE’);	1.23e-04
select to_char(12.45, ’99V9′);	125

In another post we will examine to_char with date/time data time