Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.2.1 |  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_NUMBER Function

Description

The TO_NUMBER function interprets a character string based on the number format given as an argument, converts it to a NUMERIC type value, and returns it.

When the format argument is assigned, the string is interpreted according to the specified language. The language used is the language specified in the intl_number_lang system parameter. When the intl_number_lang is not set, the language specified in the CUBRID_LANG environment variable is used. For example, when the language is one of the European languages, such as "de_DE" and "fr_FR", "." is interpreted as the cipher identifier and "," as the decimal point symbol. When the format parameter specified does not correspond to the given string, an error is returned.

If the format argument is omitted, string is interpreted according to default output format set by intl_date_lang or CUBRID_LANG (see Default Output of Number for Each Language of TO_CHAR Function (number).

Syntax

TO_NUMBER(string [, format ])

 

string :

character strings

NULL

 

format :

character strings

NULL

  • string: Specifies an expression that returns character string. If the value is NULL, NULL is returned.
  • format: Specifies a format of return value to be converted as NUMBER type. See the "Number Format" table of TO_CHAR Function (number). If the value is NULL, an error is returned.
Example

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

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

SELECT TO_NUMBER('-1234');

 to_number('-1234')

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

  -1234

 

 

SELECT TO_NUMBER('12345','999999');

 to_number('12345', '999999')

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

  12345

 

 

SELECT TO_NUMBER('$12,345.67','C99,999.999');

 to_number('$12,345.67', 'C99,999.999')

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

  12345.670

 

 

SELECT TO_NUMBER('12345.67','99999.999');

 to_number('12345.67', '99999.999')

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

  12345.670

The following example shows command execution on the database 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"

 

SELECT TO_NUMBER('12.345,67','99.999,999');

   to_number('12.345,67', '99.999,999')

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

  12345.670