Conversion Functions

Conversion Functions#

Conversion functions convert an input value from one data type to another.

ASCIISTR#

Syntax#

ASCIISTR(expr)

Description#

ASCIISTR converts a string in any character set to an ASCII string and returns it. Non-ASCII characters in expr are expressed as '\xxxx', that is, in UTF-16 code. The return type is VARCHAR.

To convert a string to the national character set, use UNISTR.

Example#

<Query> Convert the input strings to ASCII strings.

SELECT ASCIISTR('ABÄCDE') FROM DUAL;

ASCIISTR('
----------
AB\00C4CDE
1 row selected.

SELECT ASCIISTR('abcåñö') FROM DUAL;

ASCIISTR('ABCÅÑÖ')
------------------
abc\00E5\00F1\00F6
1 row selected.

BIN_TO_NUM#

Syntax#

BIN_TO_NUM (expr)

Description#

This function converts expr into a decimal number. expr can be a binary number or a string that consists exclusively of ones (1s) and zeros (0s) and is a maximum of 32 characters long. The return type is INTEGER.

Note

If any arithmetic operations are performed in expr, the numbers are handled as decimal numbers. The resultant decimal number has to comprise only ones and zeros in order to be implicitly converted to BIN before being converted back to a decimal number.

Example#

<Query> Convert the given binary number to a decimal number.

SELECT BIN_TO_NUM ('1010') FROM DUAL;

BIN_TO_NUM ('1010') 
----------------------
10          
1 row selected.

CONVERT#

Syntax#

CONVERT(expr, dest_char_set [, source_char_set] )

Description#

This function converts expr from source_char_set to dest_char_set. Any character sets that are supported by Altibase can be specified in dest_char_set and source_char_set. If expr contains any characters that are not supported by either source_char_set or dest_char_set, question marks ("?") will be returned. If source_char_set is not specified, the default database character set is taken as source_char_set.

Example#

<Query> Convert the characters "ABC" from the UTF8 character set to the US7ASCII character set.

SELECT CONVERT( 'ABC', 'US7ASCII', 'UTF8') FROM DUAL;

CONVER
------
ABC

DATE_TO_UNIX#

Syntax#

DATE_TO_UNIX (expr)

Description#

This function converts a DATE type expr to a value in the unit of seconds based on 1970-01-01 00:00:00 (UTC +00:00 time zone) and returns it. expr can take the following range of values.

[1970-01-01 00:00:00 - 3999-12-31 23:59:59]

If expr is smaller than the minimum value of the above range, the minimum value is converted and returned; if expr is larger than the maximum value of the above range, the maximum value is converted and returned.

Example#

SELECT DATE_TO_UNIX( TO_DATE( '2009-04-07 04:48:30', 'YYYY-MM-DD HH:MI:SS' )) date_to_unix
  FROM DUAL;

DATE_TO_UNIX
---------------------------------------------
1239079710
1 row selected.

HEX_DECODE#

Syntax#

HEX_DECODE(expr)

Description#

This function converts and returns a hexadecimal character string into an ASCII character string. The return type is VARCHAR.

Example#

<Query> Convert the input hexadecimal character string into an ASCII character string.

SELECT HEX_DECODE('414241434445') FROM DUAL;

HEX_DECODE('414241434445')
------------------------------
ABACDE
1 row selected.

HEX_ENCODE#

Syntax#

HEX_ENCODE(expr)

Description#

This function converts and returns characters of an ASCII character string into a corresponding hexadecimal character string. The return type is VARCHAR.

Example#

<Query> Convert the input ASCII character string into a hexadecimal character string.

SELECT HEX_ENCODE('ABACDE') FROM DUAL;

HEX_ENCODE('ABACDE')
------------------------
414241434445
1 row selected.

HEX_TO_NUM#

Syntax#

HEX_TO_NUM (expr)

Description#

HEX_TO_NUM converts expr into a decimal number. expr must be a string that consists exclusively of the numeric characters from 0 to 9 and the alphabet characters from A to F, and can be a maximum of 8 characters long

The return type is INTEGER.

Example#

<Query> Convert the given hexadecimal number to a decimal number.

SELECT HEX_TO_NUM ('1A') FROM DUAL;

HEX_TO_NUM ('1A') 
--------------------
26          
1 row selected.

OCT_TO_NUM#

Syntax#

OCT_TO_NUM (expr)

Description#

OCT_TO_NUM converts expr into a decimal number. expr can be an octal number or a string that consists exclusively of the numeric characters from 0 to 7 and is a maximum of 11 characters long.

The return type is INTEGER.

Note

If any arithmetic operations are performed in expr, the numbers are handled as decimal numbers. The resultant decimal number can comprise only the digits 0 through 7 in order to be implicitly converted to OCT before being converted back to a decimal number.

Example#

<Query> Convert the given octal number to a decimal number.

SELECT OCT_TO_NUM ('71') FROM DUAL;

OCT_TO_NUM ('71') 
--------------------
57          
1 row selected.

RAW_TO_INTEGER#

Syntax#

RAW_TO_INTEGER (expr)

Description#

The RAW_TO_INTEGER function employs the TO_RAW function in order to return the VARBYTE value by re-converting into INTEGER data type again.

Example#

<Query> Retrieve VARBYTE type value '01000000' with the INTEGER data type.

SELECT RAW_TO_INTEGER(VARBYTE'01000000') FROM DUAL;

RAW_TO_INTEGER(VARBYTE'01000000') 
------------------------------------
1   
1 row selected.

RAW_TO_NUMERIC, RAW_TO_FLOAT#

Syntax#

RAW_TO_NUMERIC (expr)
RAW_TO_FLOAT (expr)

Description#

The RAW_TO_NUMERIC and RAW_TO_FLAOT functions employ the TO_RAW function to return the values, which were converted into the VARBYTE data type, by converting into NUMERIC or FLOAT data type. The return type is VARCHAR.

Example#

<Query> Retrieve the VARBYTE type value '02C101' with the NUMERIC data type.

SELECT RAW_TO_NUMERIC(VARBYTE'02C101') FROM DUAL;

RAW_TO_NUMERIC(VARBYTE'02C101') 
----------------------------------
1   
1 row selected.

RAW_TO_VARCHAR#

Syntax#

RAW_TO_VARCHAR (n)

Description#

The RAW_TO_VARCHAR is a function converting the value of VARBYTE type into VARCHAR type again. The VARCHAR type value had been already converted into the VARBYTE type by TO_RAW function and it is re-converted into VARCHAR again.

Example#

<Query> Convert VARBYTE format value '050068656C6C6F' created by TO_RAW function into VARCHER data type.

SELECT RAW_TO_VARCHAR('050068656C6C6F') FROM DUAL;

RAW_TO_VARCHAR('050068656C6C6F')  
------------------------------------
hello
1 row selected.

TO_BIN#

Syntax#

TO_BIN (n)

Description#

TO_BIN converts n into a binary number. n can be a decimal integer or a string consisting of the numeric characters from 0 to 9.

The range of possible input values is from -2147483647 to 2147483647, that is, from -(2³¹ -1) to (2³¹ -1). When a negative value is entered, all bits are flipped, and 1 is added to the output.

The output is signed. Because leading zeros are not output, the sign value does not appear for positive numbers.

Example#

<Query> Convert the given value into a binary number.

SELECT TO_BIN(1000) FROM DUAL;

TO_BIN(1000)                      
------------------------------------
1111101000                        
1 row selected.

TO_CHAR(datetime)#

Syntax#

TO_CHAR (date [, 'fmt'])

Description#

TO_CHAR(datetime) converts date, which is a date type value, to a VARCHAR type string having the datetime format specified using fmt. If fmt is omitted, the output will be in the format specified using the DEFAULT_DATE_FORMAT property in the altibase.properties file. The default value for the DEFAULT_DATE_FORMAT property is DD-MON-RRRR. For more information about the datetime format, which is used when converting date type data, please refer to the General Reference.

Example#

<Query> Retrieve the start date of all employees and output it in the form YYYY-MM-DD HH:MI:SS.

SELECT e_firstname
     , e_lastname
     , TO_CHAR(join_date, 'YYYY-MM-DD HH:MI:SS') join_date
  FROM employees;

E_FIRSTNAME           E_LASTNAME            JOIN_DATE
---------------------------------------------------------------------------
.
.
.
Farhad                Ghorbani              2009-12-20 00:00:00
Ryu                   Momoi                 2010-09-09 00:00:00
Gottlieb              Fleischer             2004-01-24 00:00:00
Xiong                 Wang                  2009-11-29 00:00:00
.
.
.
20 rows selected.

TO_CHAR (number)#

Syntax#

TO_CHAR (n [,'format'])

Description#

TO_CHAR (number) converts input numeric type data to VARCHAR type data and outputs the result. It is possible to specify the format in which the result is output.

If a string is input, it is implicitly converted to a decimal number so that numeric operations can be performed on it before it is converted back into character type data.

For more information about the numeric format, please refer to the General Reference.

Examples#

<Query> Perform implicit conversion on the character string in order to interpret it as a number, perform the requested calculation, and return the result.

SELECT TO_CHAR('01110' + 1) FROM DUAL;

TO_CHAR('01110' + 1)                                
------------------------------------------------
1111                                                
1 row selected.

<Query> Output the input numeric data as a string having the specified format.

SELECT TO_CHAR (123, '99999') FROM DUAL;

TO_CHAR (123, '99999')  
--------------------------
   123           
1 row selected.

SELECT TO_CHAR (123.4567, '999999') FROM DUAL;

TO_CHAR (123.4567, '999999')  
--------------------------------
    123          
1 row selected.

SELECT TO_CHAR (1234.578, '9999.99') FROM DUAL;

TO_CHAR (1234.578, '9999.99')  
---------------------------------
 1234.58         
1 row selected.

SELECT TO_CHAR (1234.578, '999.99999') FROM DUAL;

TO_CHAR (1234.578, '999.99999')  
-----------------------------------
########### 
1 row selected.

TO_DATE#

Syntax#

TO_DATE (expr [, 'fmt' ])

Description#

TO_DATE converts CHAR or VARCHAR type data into a date type value. fmt is used to indicate the date format of expr. If fmt is omitted, expr must have the form set in the ALTIBASE_DATE_FORMAT environment variable or the DEFAULT_DATE_FORMAT property (the former takes precedence).

If the year or month of the input date is not specified in expr, the current year or month at the time of execution of TO_DATE will be returned as the year or month. For example, if TO_DATE (TO_CHAR (sysdate,'YYYY'),'YYYY') is executed at 17:32:34 on the date 2011/08/24, the result of execution would be '2011/08/01 00:00:00'.

If the date (i.e. day of the month) is not specified in expr, the current month at the time of execution of TO_DATE, together with the initial values for date and time, that is, 00 hours, 00 minutes, and 00 seconds on the first of the month, will be returned.

Example#

<Query> Add a record for a newly hired employee, including the employee number, name, and gender. Set the hiring date to November 19, 2011.

INSERT INTO employees(eno, e_lastname, e_firstname, sex, join_date) 
VALUES(22, 'Jones', 'Mary', 'F', TO_DATE('2011-11-19 00:00:00', 'YYYY-MM-DD HH:MI:SS'));

<Query> Execute the TO_DATE function without outputting the month or the date.

SELECT TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY'), 'YYYY'), 'YYYYMMDD HH24:MI:SS')
  FROM DUAL;

TO_CHAR(TO_DATE(TO_CHAR(SYSDATE,'YYYY'),
-------------------------------------------- 
20080501 00:00:00
1 row selected.

(SYSDATE = 20080502 13:17:20)

TO_HEX#

Syntax#

TO_HEX (n)

Description#

TO_HEX converts n into a hexadecimal number. n can be a decimal integer or a string consisting of the numeric characters from 0 to 9.

TO_HEX returns VARCHAR type data.

Example#

<Query> Convert the input decimal number into a hexadecimal number.

SELECT TO_HEX(1000) FROM DUAL;

TO_HEX(1000)  
----------------
3E8       
1 row selected.

TO_INTERVAL (NUMTODSINTERVAL)#

Syntax#

TO_INTERVAL(n, 'interval_unit')

Description#

TO_INTERVAL(NUMTODSINTERVAL) function converts 'n' into interval_unit, then returns. The n can input numeric type or any type which can be converted into the numeric type, and the value can be input within the range of -9e18 ~ 9e18 in seconds.

The following chart represents units which can be input into interval_unit, and it does not classify capital and small letter

INTERVAL	Description
YEAR \| YY	Year
MONTH \| MM	Month
DAY \| DD	Day
HOUR \| HH	Time
MINUTE \| MI	Minute
SECOND \| SS	Second

Operating yearly and monthly unit might cause an error depending on the accumulation of repetitive error by the system call.

Example#

SELECT TO_INTERVAL( 1, 'day') days FROM DUAL;

DAYS 
-----------------------
1 
1 row selected.

SELECT SYSDATE + TO_INTERVAL( 24 * 60 * 60, 'second') tomorrow
  FROM DUAL;

TOMORROW 
---------------
16-APR-2015 
1 row selected.

SELECT TO_DATE('2015-02-01', 'YYYY-MM-DD') + TO_INTERVAL( 1, 'month' ) next_month
  FROM DUAL;

NEXT_MONTH   
---------------
03-MAR-2015
1 row selected.

TO_NCHAR(character)#

Syntax#

TO_NCHAR (expr)

Description#

This function converts character type data from the database character set to the national character set. The return type is NVARCHAR.

This functions is similar to the CONVERT function.

Example#

<Query> Convert the Korean syllable "안" to the national character set UTF-16 and output information about it. (Note that the code point corresponding to the syllable "안" is U+C548.)

SELECT DUMP( TO_NCHAR('안'), 16 ) FROM DUAL;

DUMP( TO_NCHAR('안'), 16 )
------------------------------------------------------------------------------
Type=NVARCHAR(UTF16) Length=4: 2,0,c5,48

TO_NCHAR(datetime)#

Syntax#

TO_NCHAR (datetime [,'fmt'])

Description#

TO_NCHAR(datetime) converts date type data from the database character set to the national character set.

Example#

<Query> >Retrieve the date on which each employee was hired and convert it to the national character set.

SELECT e_lastname
     , e_firstname
     , TO_NCHAR(join_date, 'YYYY-MM-DD HH:MI:SS') join_date
  FROM employees;

E_LASTNAME            E_FIRSTNAME           JOIN_DATE
---------------------------------------------------------------------------
.
.
.
Momoi                 Ryu                   2010-09-09 00:00:00
Fleischer             Gottlieb              2004-01-24 00:00:00
Wang                  Xiong                 2009-11-29 00:00:00
Diaz                  Curtis                2010-06-14 00:00:00
.
.
.
20 rows selected.

TO_NCHAR(number)#

Syntax#

TO_NCHAR (n [,'fmt'])

Description#

TO_NCHAR(number) converts numeric type data from the database character set to the national character set.

Example#

SELECT TO_NCHAR('01110' + 1) FROM DUAL;

TO_NCHAR('01110' + 1)
------------------------------------------------
1111
1 row selected.

TO_NUMBER#

Syntax#

TO_NUMBER (char [, number_fmt] )

Description#

TO_NUMBER converts a string into a numeric data type. The user can specify the desired numeric output format. For more information about the numeric output format, please refer to the General Reference.

The return type is FLOAT.

Examples#

<Query> Convert the string "200.00" to FLOAT and then input the result into the database.

UPDATE employees
   SET salary = salary + TO_NUMBER( '200.00')
 WHERE eno = 10;

<Query> Convert the given strings into various numeric output formats.

SELECT TO_NUMBER ( '0123.4500', '0990.9909' ) FROM DUAL;

TO_NUMBER ( '0123.4500', '0990.9909' )
-----------------------------------------
123.45
1 row selected.

SELECT TO_NUMBER ( '$12,3.45-', '09,$0.00S' ) FROM DUAL;

TO_NUMBER ( '$12,3.45-', '09,$0.00S' )
-----------------------------------------
-123.45
1 row selected.

SELECT TO_NUMBER ( '<$183.5>', '$9,000.0PR' ) FROM DUAL;

TO_NUMBER ( '<$183.5>', '$9,000.0PR' )
-----------------------------------------
-183.5
1 row selected

TO_OCT#

Syntax#

TO_OCT (n)

Description#

TO_OCT converts n into an octal number. n can be a decimal integer or a string consisting of the numeric characters from 0 to 9.

TO_OCT returns VARCHAR type data.

Example#

<Query> Convert the given value into an octal number.

SELECT TO_OCT(1000) FROM DUAL;

TO_OCT(1000)  
----------------
1750          
1 row selected.

TO_RAW#

Syntax#

TO_RAW (n)

Description#

The TO_RAW function returns all of data type value which is entered in 'n' by converting a format of VARBYTE type.

Example#

<Query> Print out the input value with VARBYTE type.

SELECT TO_RAW(1000) FROM DUAL;

TO_RAW(1000)  
----------------
E803  
1 row selected.

UNISTR#

Syntax#

UNISTR(expr)

Description#

UNISTR converts the input character string to the national character set.

expr can be a Unicode-encoded string. A Unicode-encoded string is entered in UTF16 code units, such as \xxxx. The return type is NVARCHAR.

This function is the opposite of ASCIISTR.

Example#

<Query> Convert a string containing both ASCII- and Unicode-encoded characters to the national character set.

SELECT UNISTR('abc\00e5\00f1\00f6') FROM DUAL;

UNISTR
------
abcåñö
1 row selected.

UNIX_TO_DATE#

Syntax#

UNIX_TO_DATE (expr)

Description#

This function converts expr to DATE type and returns it. expr must take a value that satisfies the following conditions.

A value in the unit of seconds based on 1970-01-01 00:00:00 (UTC +00:00 time zone)
Value range: 0 - 64060588799

If expr is smaller than the minimum value of the above range, the minimum value is converted and returned; if expr is larger than the maximum value of the above range, the maximum value is converted and returned.

Example#

SELECT TO_CHAR( UNIX_TO_DATE( 1239079710 ), 'YYYY-MM-DD HH:MI:SS' ) unix_to_date
  FROM DUAL;

UNIX_TO_DATE
--------------------------------------------------------------
2009-04-07 04:48:30 
1 row selected.