TO_CHAR Function (number)

Description

The TO_CHAR function converts a Number Format or numeric data type to a character string according to the number format and returns it. The type of the return value is VARCHAR. If the number format has not been specified as an argument, all significant figures are converted to a character string according to the default format (see the table Default Output of Number for Each Language).

Syntax

TO_CHAR(number[, format[, number_lang_string_literal ] ])

 

number :

• numeric(decimal)

• integer

• smallint

• bigint

• float(real)

• double

• NULL

 

format :

• character strings (see the table, Number Format)

• NULL

 

number_lang_string_literal :

• 'en_US'

• 'de_DE'

• 'es_ES'

• 'fr_FR'

• 'it_IT'

• 'ja_JP'

• 'km_KH'

• 'ko_KR'

• 'tr_TR'

• 'vi_VN'

• 'zh_CN'

• number: Specifies an expression that returns numeric data type string. If the input value is NULL, NULL is returned. If the input value is character type, the character itself is returned.
• format: Specifies a format of return value. If format is not specified, all significant figures are returned as character string by default. If the value is NULL, NULL is returned.
• number_lang_string_literal: Specifies the language to be applied to the input value.

Number Format

Format Element	Example	Description
9	9999	The number of 9's represents the number of significant figures to be returned. If the number of significant figures specified in the format is not sufficient, only the decimal part is rounded. If it is less than the number of digits in an integer, # is outputted. If the number of significant figures specified in the format is sufficient, the part preceding the integer part is filled with space characters and the decimal part is filled with 0.
0	0999	If the number of significant figures specified in the format is sufficient, the part preceding the integer part is filled with 0, not space characers before the value is returned.
S	S9999	Outputs the negative/positive sign in the specified position. These signs can be used only at the beginning of character string.
C	C9999	Returns the ISO currency code at the specified position.
,(comma)	9,999	Returns a comma (",") at the specified position. Multiple commas are allowed in the format.
.(percimal point)	9.999	Returns a percimal point (".") which distinguishes between a decimal and an at the specified position. Only one percimal point is allowed in the format (see the table, "Default Output of Number for Each Language".
EEEE	9.99EEEE	Returns a scientific notation number.

Default Output of Number for Each Language

Language	Locale	Number of Digits	Decimal Symbol	Example of Number Usage
Englisth	en_US	,(comma)	.(period)	123,456,789.012
German	de_DE	.(period)	,(comma)	123.456.789.012
Spanish	es_ES	.(period)	,(comma)	123.456.789.012
French	fr_FR	.(period)	,(comma)	123.456.789.012
Italian	it_IT	.(period)	,(comma)	123.456.789.012
Japanese	ja_JP	,(comma)	.(period)	123,456,789.012
Cambodian	km_KH	.(period)	,(comma)	123.456.789.012
Korean	ko_KR	,(comma)	.(period)	123,456,789.012
Turkish	tr_TR	.(period)	,(comma)	123.456.789.012
Vietnamese	vi_VN	.(period)	,(comma)	123.456.789.012
Chinese	zh_CN	,(comma)	.(period)	123,456,789.012

Example

The following example shows execution of the database by setting the environment variable CUBRID_LANG to "en_US.utf8".

--selecting a string casted from a number in the specified format

 

SELECT TO_CHAR(12345,'S999999'), TO_CHAR(12345,'S099999');

============================================

  ' +12345'             '+012345'

 

 

SELECT TO_CHAR(1234567,'C9,999,999,999');

   to_char(1234567, 'C9,999,999,999')

======================

  '    $1,234,567'

 

SELECT TO_CHAR(1234567,'C9.999.999.999');

   to_char(1234567, 'C9.999.999.999')

======================

  '    $1.234.567'

 

SELECT TO_CHAR(123.4567,'99'), TO_CHAR(123.4567,'999.99999'), TO_CHAR(123.4567,'99999.999');

   to_char(123.4567, '99')   to_char(123.4567, '999.99999')   to_char(123.4567, '99999.999')

==================================================================

  '##'                  '123.45670'           '  123.457'

The following example shows command execution by setting the value of the intl_number_lang system parameter to "de_DE". In the number output format of most European countries such as Germany and France, "." is the cipher identifier and "," is the decimal point symbol.

csql> ;se intl_number_lang="de_DE"

 

intl_number_lang="de_DE"

 

--selecting a string casted from a number in the specified format

SELECT TO_CHAR(12345,'S999999'), TO_CHAR(12345,'S099999');

 

============================================

  ' +12345'             '+012345'

 

 

SELECT TO_CHAR(1234567,'C9,999,999,999');

======================

  '##############'

 

 

SELECT TO_CHAR(1234567,'C9.999.999.999');

======================

  '    EUR1.234.567'

 

SELECT TO_CHAR(123.4567,'99'), TO_CHAR(123.4567,'999,99999'), TO_CHAR(123.4567,'99999,999');

 

to_char(123.4567, '99')   to_char(123.4567, '999,99999')   to_char(123.4567, '99999,999')

==================================================================

  '##'                  '123,45670'           '  123,457'

 

SELECT TO_CHAR(123.4567,'99','en_US'), TO_CHAR(123.4567,'999.99999','en_US'), TO_CHAR(123.4567,'99999.999','en_US');

 to_char(123.4567, '99', 'en_US')   to_char(123.4567, '999.99999', 'en_US')   to_char(123.4567, '99999.999', 'en_US')

==========================================================

  '##'                  '123.45670'           '  123.457'

 

SELECT TO_CHAR(1.234567,'99.999EEEE','en_US'), TO_CHAR(1.234567,'99,999EEEE','de_DE'), to_char(123.4567);

 

   to_char(1.234567, '99.999EEEE', 'en_US')   to_char(1.234567, '99,999EEEE', 'de_DE')   to_char(123.4567)

==================================================================

  '1.235E+00'           '1,235E+00'           '123,4567'