Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

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'