SAND CDBMS SQL Reference Guide
Value Expressions

 

Previous Topic:
OLAP Functions
Chapter Index
 Next Topic:
String Functions

 

Functions


Cast Functions

The cast functions are used to convert input values from one format to another. A cast function can appear anywhere a value expression is permitted in SQL syntax. The following cast functions are supported:

CHAR ( value-expression [ , 'picture-format' ] )
Converts the input value to the CHAR data type equivalent and returns the value. DATE and TIME values are converted according to the picture format in the nucleus.ini file, unless a second parameter that specifies a particular date or time picture (between quotation marks) is included. Refer to the nucleus.ini documentation for a description of the valid date/time pictures.

Examples

Input Output
'2005-01-31', 'mmmm dd, yyyy' 'January 31, 2004'
1000 + 200 + 30 + 4 '1234'


CREATE TABLE string_table (col1 VARCHAR(20));
CREATE TABLE date_table (c1 DATE);
INSERT INTO date_table VALUES ('2005-01-31');
1 row affected
INSERT INTO string_table SELECT CHAR(c1, 'mmmm dd, yyyy') FROM date_table;
1 row affected
INSERT INTO string_table VALUES (CHAR(1000+200+30+4));
1 row affected
SELECT * FROM string_table;
2 rows selected
COL1
--------------------
January 31, 2005
  1234


DATE ( value-expression )
This form of the DATE function converts a properly formatted input expression to the DATE data type and returns the value. The input expression can evaluate to the CHAR, VARCHAR, TIMESTAMP, or DATE data type. For the character input types, the following formats are permitted:

If the input type is TIMESTAMP, the DATE portion of the value is returned. If the input type is DATE, the value is returned unchanged. (The other DATE function constructs a DATE value from several input values.)

Example

Input Output
'22.11.1975' 1975-11-22


CREATE TABLE emp_data (eyear CHAR(4), emonth CHAR(2), eday CHAR(2));
INSERT INTO emp_data VALUES ('1975', '11', '22');
1 row affected
CREATE TABLE birthdates (d1 DATE);
INSERT INTO birthdates SELECT DATE(eday || '.' || emonth || '.' || eyear) FROM emp_data;
1 row affected
SELECT d1 FROM birthdates;
1 row selected
d1
----------
1975-11-22


DEC ( value-expression [ , precision [ , scale ] ] )
DECIMAL ( value-expression [ , precision [ , scale ] ] )
Converts a numeric or string input to the DECIMAL data type and returns the value. Optionally, the precision and scale for the converted DECIMAL value can be set. If omitted, the following default precisions are used, depending on the input type:

The default scale is 0 in all cases.

Examples

Input Output
'1234.567', 10, 5 1234.56700
5.67192e3 5672


CREATE TABLE string_table (col1 VARCHAR(20));
INSERT INTO string_table VALUES ('1234.567');
1 row affected
SELECT DEC(col1, 10, 5) FROM string_table;
1 row selected
1
------------
  1234.56700
CREATE TABLE num_table (col1 FLOAT);
INSERT INTO num_table VALUES (5.67192e3);
1 row affected
SELECT DEC(col1) FROM num_table;
1 row selected
1
-----------------
             5672


DIGITS ( value-expression )
Returns a string representation of the numeric input expression, excluding non-digits. The input value's numeric sign, decimal point, and/or exponent character (if applicable) are not part of the return value. The length of the returned string is the precision of the input value, padded with leading zeroes if necessary.

Example

Input Output
-123456.789 '0123456789'


CREATE TABLE dec1 (col1 DEC(10, 3));
INSERT INTO dec1 VALUES (-123456.789);
1 row affected
CREATE TABLE varchar1 (c1 VARCHAR(10));
INSERT INTO varchar1(c1) SELECT DIGITS(col1) FROM dec1;
1 row affected
SELECT c1 FROM varchar1;
1 row selected
c1
----------
0123456789


FLOAT ( value-expression [ , precision ] )
Returns the FLOAT data type representation of a numeric or character input expression. If included, the second argument (precision) limits the precision of the returned value to the specified number of significant decimal digits, rounding the value if applicable. The valid range for precision is 1 to 50, inclusive.

The value produced by the conversion must fall within the range of the FLOAT type, or else an error condition will result.

Examples

Input Output Output (with precision limits)
10000.00001 1.00000000100000e+04 1.00000000000000e+04
2147483647 2.14748364700000e+09 2.14750000000000e+09
'-129e2' -1.29000000000000e+04 -1.00000000000000e+04


CREATE TABLE float_cast (coldec DEC(10,5), colint INT, colchar CHAR(10));
INSERT INTO float_cast VALUES (10000.00001, 2147483647, '-129e2');
1 row affected
SELECT FLOAT(coldec) FROM float_cast;
1 row selected
1
----------------------
  1.00000000100000e+04
SELECT FLOAT(coldec, 9) FROM float_cast;
1 row selected
1
----------------------
  1.00000000000000e+04
SELECT FLOAT(colint) FROM float_cast;
1 row selected
1
----------------------
  2.14748364700000e+09
SELECT FLOAT(colint, 5) FROM float_cast;
1 row selected
1
----------------------
  2.14750000000000e+09
SELECT FLOAT(colchar) FROM float_cast;
1 row selected
1
----------------------
 -1.29000000000000e+04
SELECT FLOAT(colchar, 1) FROM float_cast;
1 row selected
1
----------------------
 -1.00000000000000e+04


INT ( value-expression )
INTEGER ( value-expression )
Returns the INTEGER data type representation of a numeric, character, or date/time interval input expression. The value produced by the conversion must fall within the range of the INTEGER type, or else an error condition will result. If present, the decimal part of the input value is rounded.

Examples

Input Output
'1.024e4' 10240
1.024099999999e+04 10241


CREATE TABLE temp_int (col1 INT);
INSERT INTO temp_int values (INTEGER('1.024e4'));
1 row affected
INSERT INTO temp_int values (INTEGER(1.024099999999e+04));
1 row affected
SELECT * FROM temp_int;
2 rows selected
COL1
-----------
      10240
      10241


TIME ( value-expression )
Returns a TIME data type representation of a TIME, TIMESTAMP, or character string input expression. An input string expression must correspond to either the standard TIME format (hh:mm:ss), or any legal format that matches the time picture (TimePic) specified in the [CLIENT] section of the nucleus.ini file.

Examples

Input Output
'1999-03-01-01.59.30.123456' 01:59:30
'23:12:23' 23:12:23


CREATE TABLE temp_time (col1 TIME);
CREATE TABLE temp_timestamp (c1 TIMESTAMP);
INSERT INTO temp_timestamp VALUES ('1999-03-01-01.59.30.123456');
1 row affected
INSERT INTO temp_time SELECT TIME(c1) FROM temp_timestamp;
1 row affected
INSERT into temp_time VALUES (TIME('23:12:23'));
1 row affected
SELECT col1 FROM temp_time;
2 rows selected
col1
--------
01:59:30
11:12:23


TIMESTAMP ( value-expression )
Returns a TIMESTAMP data type representation of a character string input expression. The input string expression must correspond to the TIMESTAMP format: yyyy-mm-dd-HH.MM.SS.NNNNNN, where yyyy is the year, mm the month, dd the day, HH the hour, MM the minutes, SS the seconds, and NNNNNN the microseconds. The microseconds part may be omitted when casting a string expression; the microseconds will be padded with zeroes in the resulting TIMESTAMP value.

Examples

Input Output
'2000-01-01-00.00.00.999999' 2000-01-01-00.00.00.999999
'1996-02-29'||'-'||'23.59.59' 1996-02-29-23.59.59.000000


CREATE TABLE temp_char (col1 CHAR(26));
INSERT INTO temp_char VALUES ('2000-01-01-00.00.00.999999');
1 row affected
CREATE TABLE temp_ts (c1 TIMESTAMP);
INSERT INTO temp_ts (c1) SELECT TIMESTAMP (col1) FROM temp_char;
1 row affected
INSERT INTO temp_ts VALUES ( TIMESTAMP ('1996-02-29'||'-'||'23.59.59') );
1 row affected
SELECT c1 FROM temp_ts;
2 rows selected
c1
--------------------------
2000-01-01-00.00.00.999999
1996-02-29-23.59.59.000000
2 rows fetched

 

Previous Topic:
OLAP Functions
Chapter Index
 Next Topic:
String Functions