Functions and operators

Aggregate functions

Functions which require a GROUP BY clause to be used in the SELECT statement. Below is the list of aggregate functions supported by Raijin:

AVG

COUNT

FIRST

GROUP_CONCAT

LAST

MAX

MIN

SUM

AVG

Returns the average value from a set of elements in a group. The function ignores NULL values.

Syntax
AVG ( field )
Parameters
field

Name of the column which values should be averaged.

Example 1. Using AVG
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;
USE testdb;
CREATE TABLE IF NOT EXISTS tbl(
    value INT, gr STRING
);

INSERT INTO tbl (value, gr) VALUES (10, 'one');
INSERT INTO tbl (value, gr) VALUES (-4, 'one');
INSERT INTO tbl (value, gr) VALUES (6, 'two');
INSERT INTO tbl (value, gr) VALUES (5, 'two');

SELECT AVG(value) FROM tbl;
SELECT 'With GROUP BY:' as _;
SELECT AVG(value) FROM tbl GROUP BY gr;
Result
{"avg":4.25}
{"_":"With GROUP BY:"}
{"avg":3}
{"avg":5.5}

COUNT

Returns the number of non-NULL rows. Use the DISTINCT clause to eliminate duplicates.

Syntax
COUNT ( [ * | field] )
Parameters
fields

Names of the columns to count the rows from. Alternatively, the asterisk (*) can be used to count the entire record.

Example 2. Using COUNT
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;
USE testdb;
CREATE TABLE IF NOT EXISTS tbl(
    value INT, gr STRING
);

INSERT INTO tbl (value, gr) VALUES (10, 'one');
INSERT INTO tbl (value, gr) VALUES (10, 'one');
INSERT INTO tbl (gr) VALUES ('one'); -- the NULL value here
INSERT INTO tbl (value, gr) VALUES (5, 'two');

'Count all:' _, COUNT(value) FROM tbl;
'With GROUP BY:' as _;
COUNT(DISTINCT value) FROM tbl GROUP BY gr;
Result
{"_":"Count all:","count":3}
{"_":"With GROUP BY:"}
{"count":1}
{"count":1}

FIRST

Returns the first element in a group as opposed to the LAST function.

Syntax
FIRST ( field )
Parameters
field

Name of the field the value is taken from.

Example 3. Using FIRST
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;

USE testdb;
CREATE TABLE IF NOT EXISTS tbl(idx_value INTEGER, gr_name TEXT);
CREATE INDEX ON tbl(idx_value);

INSERT INTO tbl (gr_name) VALUES ('gr_one'); -- NOTE the NULL value here
INSERT INTO tbl (idx_value, gr_name) VALUES (1, 'gr_one');
INSERT INTO tbl (idx_value, gr_name) VALUES (2, 'gr_one');
INSERT INTO tbl (idx_value, gr_name) VALUES (3, 'gr_two');
INSERT INTO tbl (idx_value, gr_name) VALUES (4, 'gr_two');

SELECT FIRST(idx_value) FROM tbl;
SELECT 'With GROUP BY:' _;
SELECT FIRST(idx_value) FROM tbl GROUP BY gr_name;
Result
{"first":1}
{"_":"With GROUP BY:"}
{"first":1}
{"first":3}

GROUP_CONCAT

Concatenates elements of the group.

Syntax
GROUP_CONCAT ( [DISTINCT] field )
Parameters
field

Name of the field which values should be concatenated.

Example 4. Using GROUP_CONCAT
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;

USE testdb;
CREATE TABLE tbl(
    str_value STRING,
    number DOUBLE,
    gr_name TEXT
);

INSERT INTO tbl (str_value, gr_name) VALUES ('Hello', 'gr_one');    -- NOTE the NULL value here
INSERT INTO tbl (str_value, number, gr_name) VALUES ('World!', 2.31, 'gr_one');
INSERT INTO tbl (str_value, number, gr_name) VALUES ('Foo', 3, 'gr_two');
INSERT INTO tbl (str_value, number, gr_name) VALUES ('bar', 3, 'gr_two');

SELECT GROUP_CONCAT(str_value) strings, GROUP_CONCAT(DISTINCT number) dist_numbers FROM tbl;
SELECT 'With GROUP BY:' _;
SELECT GROUP_CONCAT(str_value) strings, GROUP_CONCAT(number) numbers FROM tbl GROUP BY gr_name;
Result
{"strings":"Hello,World!,Foo,bar","dist_numbers":"2.31,3"}
{"_":"With GROUP BY:"}
{"strings":"Hello,World!","numbers":"2.31"}
{"strings":"Foo,bar","numbers":"3,3"}

LAST

Returns the last element in the group.

Syntax
LAST ( field )
Parameters
field

Name of the field the returned value should be taken from.

Example 5. Using LAST
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;

USE testdb;
CREATE TABLE IF NOT EXISTS tbl(idx_value INTEGER, gr_name TEXT);
CREATE INDEX ON tbl(idx_value);

INSERT INTO tbl (gr_name) VALUES ('gr_one'); -- NOTE the NULL value here
INSERT INTO tbl (idx_value, gr_name) VALUES (1, 'gr_one'),
                                            (2, 'gr_one'),
                                            (3, 'gr_two'),
                                            (4, 'gr_two');

SELECT '..................................................................1' _;
SELECT LAST(idx_value) FROM tbl;
SELECT 'With GROUP BY:....................................................2' _;
SELECT LAST(idx_value) FROM tbl GROUP BY gr_name;
SELECT 'With more row groups..............................................3' _;
FLUSH TABLE tbl;

INSERT INTO tbl (idx_value, gr_name) VALUES (5, 'gr_one'),
                                            (null, 'gr_one'),
                                            (null, 'gr_two'),
                                            (null, 'gr_two');
SELECT LAST(idx_value) FROM tbl WHERE idx_value > 2;
Result
{"_":"..................................................................1"}
{"last":4}
{"_":"With GROUP BY:....................................................2"}
{"last":2}
{"last":4}
{"_":"With more row groups..............................................3"}
{"last":5}

MAX

Returns the maximum value from the group.

Syntax
MAX ( field )
Parameters
field

Name of the field the maximum value should be selected from.

Example 6. Using MAX
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;

USE testdb;
CREATE TABLE IF NOT EXISTS tbl(idx_value INTEGER, gr_name TEXT);
CREATE INDEX ON tbl(idx_value);

INSERT INTO tbl (idx_value, gr_name) VALUES (1, 'gr_one');
INSERT INTO tbl (idx_value, gr_name) VALUES (8, 'gr_one');
INSERT INTO tbl (idx_value, gr_name) VALUES (3, 'gr_two');
INSERT INTO tbl (idx_value, gr_name) VALUES (4, 'gr_two');

SELECT MAX(idx_value) FROM tbl;
SELECT 'With GROUP BY:' _;
SELECT MAX(idx_value) FROM tbl GROUP BY gr_name;
Result
{"max":8}
{"_":"With GROUP BY:"}
{"max":8}
{"max":4}

MIN

Returns the minimum value from the group.

Syntax
MIN ( field )
Parameters
field

Name of the field the minimum value should be selected from.

Example 7. Using MIN
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;

USE testdb;
CREATE TABLE tbl(idx_value INTEGER, gr_name TEXT);
CREATE INDEX ON tbl(idx_value);

INSERT INTO tbl (idx_value, gr_name) VALUES (1, 'gr_one');
INSERT INTO tbl (idx_value, gr_name) VALUES (8, 'gr_one');
INSERT INTO tbl (idx_value, gr_name) VALUES (3, 'gr_two');
INSERT INTO tbl (idx_value, gr_name) VALUES (4, 'gr_two');

SELECT MIN(idx_value) FROM tbl;
SELECT 'With GROUP BY:' _;
SELECT MIN(idx_value) FROM tbl GROUP BY gr_name;
Result
{"min":1}
{"_":"With GROUP BY:"}
{"min":1}
{"min":3}

SUM

Returns the sum of elements in the group.

Syntax
SUM ( [DISTINCT] field )
Parameters
field

Name of the field which values should be summed.

Example 8. Using SUM
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;

USE testdb;
CREATE TABLE tbl(idx_value INTEGER, gr_name TEXT);
CREATE INDEX ON tbl(idx_value);

INSERT INTO tbl (idx_value, gr_name) VALUES (8, 'gr_one');
INSERT INTO tbl (idx_value, gr_name) VALUES (8, 'gr_one');
INSERT INTO tbl (idx_value, gr_name) VALUES (3, 'gr_two');
INSERT INTO tbl (idx_value, gr_name) VALUES (4, 'gr_two');

SELECT SUM(idx_value) sum_all, SUM(DISTINCT idx_value) sum_distinct FROM tbl;
SELECT 'With GROUP BY:' _;
SELECT SUM(idx_value) FROM tbl GROUP BY gr_name;
Result
{"sum_all":23,"sum_distinct":15}
{"_":"With GROUP BY:"}
{"sum":16}
{"sum":7}

Control functions

Raijin provides the following control functions:

CASE

COALESCE

IF

IFNULL

TYPE

IF

SQL variant of a ternary operator. Returns the evaluated value of then or otherwise depending on condition. The then and otherwise parameters must be type compatible.

Syntax
IF ( condition, then, otherwise )
Parameters
condition

This expression is evaluated using the boolean logic, and then used to decide which expression is the result.

then

Expression which value is evaluated and returned if condition is evaluated as true.

otherwise

Expression which value is evaluated and returned if condition is evaluated as false.

Example 9. Using IF
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;
USE testdb;

CREATE TABLE IF NOT EXISTS tbl(one INTEGER, two INTEGER);
INSERT INTO tbl (two) VALUES (23);
INSERT INTO tbl (one, two) VALUES (34, 23);
INSERT INTO tbl (one, two) VALUES (-153, 5158);

SELECT IF( one > two , one, two) as maximum FROM tbl;
SELECT IF(LENGTH('Some str') = 9, 'Right!', 'Not really') guessing_lenght;
Result
{"maximum":23}
{"maximum":34}
{"maximum":5158}
{"guessing_lenght":"Not really"}

IFNULL

Returns the evaluated value of expression or substitute depending on if expression is NULL. The expression and substitute parameters must be type compatible.

Syntax
IFNULL ( expression, substitute )
Parameters
expression

Value which should be evaluated for being non-nullable and returned its value is not NULL.

substitute

Value which should be returned if the expression value is NULL.

Example 10. Using IFNULL
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;
USE testdb;
CREATE TABLE IF NOT EXISTS tbl(one INTEGER, two INTEGER);
INSERT INTO tbl (one, two) VALUES (34, 23);
INSERT INTO tbl (two) VALUES (77);
INSERT INTO tbl (one, two) VALUES (null, 413);
INSERT INTO tbl (one, two) VALUES (null, null);

SELECT IFNULL(one, two) as result FROM tbl;
SELECT IFNULL(null, 99) as result;
SELECT IFNULL(IFNULL(null, null), 10) as result;
Result
{"result":34}
{"result":77}
{"result":413}
{"result":null}
{"result":99}
{"result":10}

CASE

Generic conditional expression, similar to if/else statements in other programming languages.

General CASE expression syntax (full specification)
CASE WHEN condition THEN result
    [WHEN condition THEN result]
    [...]
    [ELSE result]
END

Each condition is an expression that should return a boolean result. If the result is TRUE, the value of the CASE expression is the result following the condition, and the remaining WHEN-conditions are not processed.

If the condition result is FALSE, subsequent WHEN clauses are examined in the same manner. If no WHEN condition returns TRUE, the value of the CASE expression is the result of the ELSE clause. If the ELSE clause is not present and the TRUE condition is not met, the result is NULL.

Simple CASE expression syntax (implicit argument)
CASE expression
    WHEN value THEN result
    [WHEN value THEN result]
    [...]
    [ELSE result]
END

The simple form of the CASE expression is a variant of the general (full) form shown above. The first expression is computed, and the result is compared to each of the value expressions in the WHEN clauses until one is found that is equal to it. If no match is found, the result of the ELSE clause is returned. If the ELSE clause is not present, NULL is returned. This is similar to the switch statement in the C programming language.

Example 11. Using CASE
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;

USE testdb;

CREATE TABLE IF NOT EXISTS test(a int, b int, d bool);

INSERT INTO test(a, b, c) VALUES (1, 10, false);
INSERT INTO test(a, b, c) VALUES (2, 90, false);
INSERT INTO test(a, b, c) VALUES (3, 100, true);

SELECT CASE c WHEN true THEN a ELSE b END AS x FROM test;
SELECT CASE WHEN c = true THEN a WHEN b > 50 THEN b ELSE 0 END AS x FROM test;
Result
{"x":10}
{"x":90}
{"x":3}
{"x":0}
{"x":90}
{"x":3}

COALESCE

Returns the evaluated value of the first expression which is not NULL. If a non-null value is not found, NULL is returned. This is the variadic version of IFNULL. All parameters must be type compatible.

Syntax
COALESCE ( expression [, ...] )
Parameters
expression

Expression is evaluated until a non-null value is found which is then returned.

Example 12. Using COALESCE
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;

USE testdb;
CREATE TABLE IF NOT EXISTS tbl(one INTEGER, two INTEGER);
INSERT INTO tbl(one, two) VALUES (null, 4);
INSERT INTO tbl(one, two) VALUES (null, null);

SELECT COALESCE(one, two) as res1 from tbl;
SELECT COALESCE(one, two, 10) as res2 from tbl;
SELECT COALESCE(one, two, NULL, 12) as res3 from tbl;
SELECT COALESCE(null, null) as res4;
SELECT COALESCE(null, COALESCE(null, null), COALESCE(null, 43)) as nested;
Result
{"res1":4}
{"res1":null}
{"res2":4}
{"res2":10}
{"res3":4}
{"res3":12}
{"res4":null}
{"nested":43}

TYPE

Returns the data type of the expression as STRING.

Syntax
TYPE ( expression )
Parameters
expression

Expression which type should be returned.

Example 13. Using TYPE
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;

USE testdb;
CREATE TABLE IF NOT EXISTS tbl(i INT, d DOUBLE, v VARCHAR, b BOOL, t TEXT);
INSERT INTO tbl(i, d, v, b, t) VALUES (1, 2.0, 'test', false, 'a');
INSERT INTO tbl(i, d) VALUES (2, 3.14);

SELECT TYPE(i) AS i, TYPE(d) AS d, TYPE(v) AS v, TYPE(b) AS b, TYPE(t) AS t FROM tbl;
SELECT TYPE(smth) nonexistent_row FROM tbl LIMIT 1;
SELECT TYPE(now()) now_return_type;
Result
{"i":"int","d":"double","v":"string","b":"bool","t":"string"}
{"i":"int","d":"double","v":"string","b":"bool","t":"string"}
{"nonexistent_row":"null"}
{"now_return_type":"timestamp"}

Datetime functions

Raijin provides the following datetime functions:

CURRENT_DATE

CURRENT_TIME

DATE

DAY

DAYOFWEEK

DAYOFYEAR

FORMAT_UTC_USEC

HOUR

LOCALTIME

LOCALTIMESTAMP

MICROSECOND

MINUTE

MONTH

MSEC_TO_TIMESTAMP

NOW

PARSE_UTC_USEC

QUARTER

SEC_TO_TIMESTAMP

SECOND

TIME

TIMESTAMP

TIMESTAMP_TO_MSEC

TIMESTAMP_TO_SEC

TIMESTAMP_TO_USEC

USEC_TO_TIMESTAMP

YEAR

Table 1. Using datetime functions
SQL Result

SELECT DATE('2016-08-04 17:14:04') _;

{"_":"2016-08-04"}

SELECT TIME('2016-08-04 17:14:04') _;

{"_":"17:14:04"}

SELECT FORMAT_UTC_USEC(TIMESTAMP('2016-08-04 17:14:04Z')) _;

{"_":"2016-08-04T17:14:04.000000Z"}

SELECT MICROSECOND('2014-11-24 01:02:03.000005') _;

{"_":5}

SELECT SECOND(TIMESTAMP('2016-08-04 17:14:04')) _;

{"_":4}

SELECT MINUTE(TIMESTAMP('2016-08-04 17:14:04')) _;

{"_":14}

SELECT HOUR(TIMESTAMP('2016-08-04 17:14:04')) _;

{"_":17}

SELECT DAY('2016-08-04 17:14:04') _;

{"_":4}

SELECT DAYOFWEEK('2016-08-04 17:14:04') _;

{"_":3}

SELECT DAYOFYEAR('2016-08-04 17:14:04') _;

{"_":217}

SELECT MONTH(TIMESTAMP('2016-08-04 17:14:04')) _;

{"_":8}

SELECT QUARTER(TIMESTAMP('2016-08-04 17:14:04')) _;

{"_":3}

SELECT YEAR(TIMESTAMP('2016-08-04 17:14:04')) _;

{"_":2016}

SELECT TIMESTAMP_TO_SEC(TIMESTAMP('2016-08-04 17:14:04Z')) _;

{"_":1470330844}

SELECT TIMESTAMP_TO_MSEC(TIMESTAMP('2016-08-04 17:14:04Z')) _;

{"_":1470330844000}

SELECT TIMESTAMP_TO_USEC(TIMESTAMP('2016-08-04 17:14:04Z')) _;

{"_":1470330844000000}

SELECT FORMAT_UTC_USEC(USEC_TO_TIMESTAMP(1470320844676510)) _;

{"_":"2016-08-04T14:27:24.676510Z"}

SELECT FORMAT_UTC_USEC(MSEC_TO_TIMESTAMP(1470320865828)) _;

{"_":"2016-08-04T14:27:45.828000Z"}

SELECT FORMAT_UTC_USEC(SEC_TO_TIMESTAMP(1470320887)) _;

{"_":"2016-08-04T14:28:07.000000Z"}

SELECT FORMAT_UTC_USEC(TIMESTAMP('2014-11-24 01:02:03.5Z')) _;

{"_":"2014-11-24T01:02:03.500000Z"}

CURRENT_DATE

Returns the current date formatted in the ISO format and UTC time.

Syntax
CURRENT_DATE ()
Example 14. Using CURRENT_DATE
Retrieving the current date
SELECT CURRENT_DATE();
Result
{"CURRENT_DATE":"2016-08-05"}

CURRENT_TIME

Returns the current time in the ISO format and UTC time.

Syntax
CURRENT_TIME ()
Example 15. Using CURRENT_TIME
Retrieving current time
SELECT CURRENT_TIME();
Result
{"CURRENT_TIME":"07:36:09"}

DATE

Returns the given date formatted in ISO format and containing the local timezone information.

Syntax
DATE ( date )
Parameters
date

Date to be formatted.

Example 16. Using DATE
Converting to date
SELECT DATE('2016-08-04 17:14:04') _;
Result
{"_":"2016-08-04"}

DAY

Extracts the day of month from the given date.

Syntax
DAY ( date )
Parameters
date

Date to extract the day of month from.

Example 17. Using DAY
Retrieving the day
SELECT DAY('2016-08-04 17:14:04') _;
Result
{"_":4}

DAYOFWEEK

Returns the index of a week day (zero-based) from the given date. Is a local timezone function.

Syntax
DAYOFWEEK ( date )
Parameters
date

Date to extract the week day from.

Example 18. Using DAYOFWEEK
Retrieving the week day
SELECT DAYOFWEEK('2016-08-04 17:14:04') _;
Result
{"_":3}

DAYOFYEAR

Returns the index of the day of year from the given date. Is a local timezone function.

Syntax
DAYOFYEAR ( date )
Parameters
date

Date to extract the day of year from.

Example 19. Using DAYOFYEAR
Retrieving the year day
SELECT DAYOFYEAR('2016-08-04 17:14:04') _;
Result
{"_":217}

HOUR

Extracts the hour value from the given date.

Syntax
HOUR ( date )
Parameters
date

Date to extract the hour value from.

Example 20. Using HOUR
Retrieving the hour value
SELECT HOUR(TIMESTAMP('2016-08-04 17:14:04')) _;
Result
{"_":17}

LOCALTIME

Returns the current time in ISO format with the local timezone. This is a local version of the CURRENT_TIME function.

Syntax
LOCALTIME ()
Example 21. Using LOCALTIME
Retrieving local time
SELECT LOCALTIME();
The possible result
{"localtime":"10:36:09"}

LOCALTIMESTAMP

Returns the current date and time in ISO format with the local timezone. This is a local version of the NOW function.

Syntax
LOCALTIMESTAMP ()
Example 22. Using LOCALTIMESTAMP
Retrieving the local timestamp value
SELECT LOCALTIMESTAMP();
The possible result
{"localtimestamp":"2016-08-05 10:36:09"}

MICROSECOND

Extracts the microsecond field from the given date as an integer.

Syntax
MICROSECOND ( date )
Parameters
date

Date to extract the microsecond value from.

Example 23. Using MICROSECOND
Retrieving the amount of microseconds
SELECT MICROSECOND('2014-11-24 01:02:03.000005') _;
Result
{"_":5}

MINUTE

Extracts the minute field from the given date as an integer.

Syntax
MINUTE ( date )
Parameters
date

Date to extract the minute value from.

Example 24. Using MINUTE
Retrieving the minute value
SELECT MINUTE(TIMESTAMP('2016-08-04 17:14:04')) _;
Result
{"_":14}

MONTH

Returns the month field from the given date. Is a local timezone function.

Syntax
MONTH ( date )
Parameters
date

Date to extract the month value from.

Example 25. Using MONTH
Retrieving the month value
SELECT MONTH(TIMESTAMP('2016-08-04 17:14:04')) _;
Result
{"_":8}

MSEC_TO_TIMESTAMP

Converts a value of seconds to a timestamp. This is the inverse function to TIMESTAMP_TO_MSEC.

Syntax
MSEC_TO_TIMESTAMP ( value )
Parameters
value

Value to be converted to timestamp.

Example 26. Using MSEC_TO_TIMESTAMP
Retrieving the timestamp value
SELECT FORMAT_UTC_USEC(MSEC_TO_TIMESTAMP(1470320865828)) _;
Result
{"_":"2016-08-04T14:27:45.828000Z"}

NOW

Returns current date and time.

Syntax
NOW ()
Example 27. Using NOW
Retrieving current date and time
SELECT NOW();
The possible result
{"now":"2016-08-05 10:36:09"}

PARSE_UTC_USEC

Parses a string and returns a datetime value in the ISO format and UTC time. This function has support for fractional seconds.

Syntax
PARSE_UTC_USEC ( string )
Parameters
string

Microsecond value to be converted to a timestamp.

Example 28. Using PARSE_UTC_USEC
Retrieving a timestamp from a microsecond value
SELECT(PARSE_UTC_USEC('1470330844000000'));
Result
{"parse_utc_usec":"1970-01-01T03:00:00+03:00"}

QUARTER

Returns the quarter value from the given date.

Syntax
QUARTER ( date )
Parameters
date

Date to calculate the quarter value from.

Example 29. Using QUARTER
Retrieving the quarter value
SELECT QUARTER(TIMESTAMP('2016-08-04 17:14:04')) _;
Result
{"_":3}

SEC_TO_TIMESTAMP

Converts seconds to a timestamp. This is the inverse function to TIMESTAMP_TO_SEC.

Syntax
SEC_TO_TIMESTAMP ( value )
Parameters
value

Seconds which should be converted to a timestamp.

Example 30. Using SEC_TO_TIMESTAMP
Converting to a timestamp
SELECT FORMAT_UTC_USEC(SEC_TO_TIMESTAMP(1470320887)) _;
Result
{"_":"2016-08-04T14:28:07.000000Z"}

SECOND

Extracts the second field from the given date as an integer.

Syntax
SECOND ( date )
Parameters
date

Date to extract the second value from.

Example 31. Using SECOND
Extracting a second value
SELECT SECOND(TIMESTAMP('2016-08-04 17:14:04')) _;
Result
{"_":4}

TIME

Extracts the time value from the given date. The result is formatted as UTC time in the ISO format.

Syntax
TIME ( date )
Parameters
date

UTC-formatted timestamp to extract the time value from.

Example 32. Using TIME
Extracting a time value
SELECT TIME('2016-08-04 17:14:04') _;
Result
{"_":"17:14:04"}

TIMESTAMP_TO_MSEC

Converts a timestamp value to milliseconds, i.e. a UNIX timestamp multiplied by 1,000. This function is inverse to MSEC_TO_TIMESTAMP.

Syntax
TIMESTAMP_TO_MSEC ( date )
Parameters
date

Timestamp value to be converted.

Example 33. Using TIMESTAMP_TO_MSEC
Converting a timestamp
SELECT TIMESTAMP_TO_MSEC(TIMESTAMP('2016-08-04 17:14:04Z')) _;
Result
{"_":1470330844000}

TIMESTAMP_TO_SEC

Converts a timestamp to its value in seconds. This essentially converts the parameter to a UNIX timestamp. This is the inverse function to SEC_TO_TIMESTAMP.

Syntax
TIMESTAMP_TO_SEC ( date )
Parameters
date

Timestamp value to be converted.

Example 34. Using TIMESTAMP_TO_SEC
Converting a timestamp
SELECT TIMESTAMP_TO_SEC(TIMESTAMP('2016-08-04 17:14:04Z')) _;
Result
{"_":1470330844}

TIMESTAMP_TO_USEC

Converts a timestamp value to microseconds, i.e. a UNIX timestamp multiplied by 1,000,000. This function is inverse to USEC_TO_TIMESTAMP.

Syntax
TIMESTAMP_TO_USEC ( date )

Parameters: date::: Timestamp value to be converted.

Example 35. Using TIMESTAMP_TO_USEC
Converting a timestamp
SELECT TIMESTAMP_TO_USEC(TIMESTAMP('2016-08-04 17:14:04Z')) _;
Result
{"_":1470330844000000}

TIMESTAMP

Converts the given parameter to the datetime format. This is an alias to casting a value to DATETIME.

Syntax
TIMESTAMP ( parameter )
Parameters
parameter

Value to be converted.

See the Using datetime functions table at the beginning of this section.

USEC_TO_TIMESTAMP

Converts a value of microseconds to a timestamp. This is the inverse function to TIMESTAMP_TO_USEC.

Syntax
USEC_TO_TIMESTAMP ( value )
Parameters
value

Microsecond value to be converted.

Example 36. Using USEC_TO_TIMESTAMP
Converting to a timestamp
SELECT FORMAT_UTC_USEC(USEC_TO_TIMESTAMP(1470320844676510)) _;
Result
{"_":"2016-08-04T14:27:24.676510Z"}

YEAR

Extracts the year field from the given date.

Syntax
YEAR ( date )
Parameters
date

Date to extract the year value from.

Example 37. Using YEAR
Extracting the year value
SELECT YEAR(TIMESTAMP('2016-08-04 17:14:04')) _;
Result
{"_":2016}

FORMAT_UTC_USEC

Formats a DATETIME value to the ISO-formatted UTC time. This function supports fractional second conversion.

Syntax
FORMAT_UTC_USEC ( date )
Parameters
date

Value to be converted.

Example 38. Using FORMAT_UTC_USEC
Converting to a timestamp
SELECT FORMAT_UTC_USEC(TIMESTAMP('2016-08-04 17:14:04Z')) _;
Result
{"_":"2016-08-04T17:14:04.000000Z"}

Math functions

Raijin supports the following math functions:

ABS

ACOS

ACOSH

ASIN

ASINH

ATAN

ATAN2

ATANH

CEIL

CEIL_TO_INT

COS

COSH

DEGREES

EXP

FLOOR

FLOOR_TO_INT

GREATEST

IS_EVEN

IS_FINITE

IS_INF

IS_NAN

IS_NORMAL

IS_ODD

LEAST

LN

LOG

LOG10

LOG2

MOD

PI

POWER

RADIANS

ROUND

ROUND_TO_INT

SIN

SINH

SQRT

TAN

TANH

TRUNC

Table 2. Using math functions
SQL Result

SELECT ABS(-1) ai, ABS(-4.35) ad, ABS('str');

{"ai":1,"ad":4.35,"abs":null}

SELECT ACOS(0.5), PI()/3 angle;

{"acos":1.047198,"angle":1.047198}

SELECT ACOSH(11.5919), PI() angle;

{"acosh":3.141588,"angle":3.141593}

SELECT ASIN(1), PI()/2 angle;

{"asin":1.570796,"angle":1.570796}

SELECT ASINH(-11.5487), -PI() angle;

{"asinh":-3.141589,"angle":-3.141593}

SELECT ATAN(1), PI()/4 angle;

{"atan":0.785398,"angle":0.785398}

SELECT ATAN2(4,2);

{"atan2":1.107149}

SELECT ATANH(0);

{"atanh":0}

SELECT CEIL(4), TYPE(CEIL(4));

{"ceil":4,"type":"int"}

SELECT CEIL(4.35), TYPE(CEIL(4.35));

{"ceil":5,"type":"double"}

SELECT CEIL_TO_INT(4), TYPE(CEIL_TO_INT(4));

{"ceil_to_int":4,"type":"int"}

SELECT CEIL_TO_INT(4.35), TYPE(CEIL_TO_INT(4.35));

{"ceil_to_int":5,"type":"bigint"}

SELECT COS(PI()/3);

{"cos":0.5}

SELECT COSH(PI()/3);

{"cosh":1.600287}

SELECT DEGREES(PI()/3);

{"degrees":60}

SELECT EXP(-1);

{"exp":0.367879}

SELECT FLOOR(5), TYPE(FLOOR(5));

{"floor":5,"type":"int"}

SELECT FLOOR(5.75), TYPE(FLOOR(5.75));

{"floor":5,"type":"double"}

SELECT FLOOR_TO_INT(5), TYPE(FLOOR_TO_INT(5));

{"floor_to_int":5,"type":"int"}

SELECT FLOOR_TO_INT(5.75), TYPE(FLOOR_TO_INT(5.75));

{"floor_to_int":5,"type":"bigint"}

SELECT GREATEST(4, 5, NULL, 8.4);

{"greatest":8.4}

SELECT IS_EVEN(null) n, IS_EVEN(512);

{"n":null,"is_even":true}

SELECT 2/0 AS "2/0", IS_FINITE(2/0);

{"2/0":null,"is_finite":false}

SELECT IS_FINITE(null), IS_FINITE(512) AS fin;

{"is_finite":null,"fin":true}

SELECT 2/0 AS "2/0", IS_INF(2/0);

{"2/0":null,"is_inf":true}

SELECT IS_INF(null), IS_INF(512) AS inf;

{"is_inf":null,"inf":false}

SELECT IS_NAN(2/0) a, IS_NAN(0.2) b;

{"a":false,"b":false}

SELECT IS_NORMAL(3) "3", IS_NORMAL(0) "0";

{"3":true,"0":false}

SELECT IS_ODD(3) "3", IS_ODD(4) "4";

{"3":true,"4":false}

SELECT LEAST(4, 5, NULL, 8.4);

{"least":4}

SELECT LN(15) "15", LN(-14) "-14";

{"15":2.70805,"-14":null}

SELECT LOG(3) "ln(3)", LOG(3, 10) "log(3)";

{"ln(3)":1.098612,"log(3)":0.477121}

SELECT LOG2(3), LOG10(3);

{"log2":1.584963,"log10":0.477121}

SELECT MOD(3,2) "mod(3,2)";

{"mod(3,2)":1}

SELECT POWER(2, 8), POW(25, 0.5);

{"power":256,"pow":5}

SELECT RADIANS(180);

{"radians":3.141593}

SELECT ROUND(42.46), ROUND(42.46, 1) with_precision;

{"round":42,"with_precision":42.5}

SELECT TYPE(ROUND(42.46)) round_type;

{"round_type":"double"}

SELECT ROUND(1.5) "1.5", TYPE(ROUND(1.5));

{"1.5":2,"type":"double"}

SELECT ROUND_TO_INT(42) "42", TYPE(ROUND_TO_INT(42));

{"42":42,"type":"int"}

SELECT ROUND_TO_INT(1.5) "1.5", TYPE(ROUND_TO_INT(1.5));

{"1.5":2,"type":"bigint"}

SELECT SIN(RADIANS(90));

{"sin":1}

SELECT SINH(RADIANS(90));

{"sinh":2.301299}

SELECT SQRT(4) "positive", SQRT(-4) "negative";

{"positive":2,"negative":null}

SELECT TAN(PI()/4);

{"tan":1}

SELECT TANH(PI()/4);

{"tanh":0.655794}

SELECT TRUNC(18.99);

{"trunc":18}

ABS

Returns the absolute value of x.

Syntax
ABS ( x )
Parameters
x

Must be a numeric value or the value which can be cast to numeric. Otherwise the function will return NULL.

Example 39. Using ABS
Obtaining the absolute value
SELECT ABS(-1) ai, ABS(-4.35) ad, ABS('str');
Result
{"ai":1,"ad":4.35,"abs":null}

ACOS

Returns the arccosine value of x. This function is inverse to the COS function. The return value is expected to be in radians.

Syntax
ACOS ( x )
Parameters
x

Must be a numeric value or the value which can be cast to numeric between -1 and 1.

Example 40. Using ACOS
Obtaining the arccosine value
SELECT ACOS(0.5), PI()/3 angle;
Result
{"acos":1.047198,"angle":1.047198}

ACOSH

Returns the inverse hyperbolic cosine of x. The return value is expected to be in radians.

Syntax
ACOSH ( x )
Parameters
x

Must be a numeric value or the value which can be cast to numeric greater than 1.

Example 41. Using ACOSH
Obtaining the inverse hyperbolic cosine
SELECT ACOSH(11.5919), PI() angle;
Result
{"acosh":3.141588,"angle":3.141593}

ASIN

Returns the inverse sine of x. The return value is expected to be in radians.

Syntax
ASIN ( x )
Parameters
x

Must be a numeric value or the value which can be cast to the numeric format between -1 and 1.

Example 42. Using ASIN
Obtaining the inverse sine
SELECT ASIN(1), PI()/2 angle;
Result
{"asin":1.570796,"angle":1.570796}

ASINH

Returns the inverse hyperbolic sine of x. The return value is expected to be in radians.

Syntax
ASINH ( x )
Parameters
x

Must be a numeric value or the value which can be cast to numeric.

Example 43. Using ASINH
Obtaining the inverse hyperbolic sine
SELECT ASINH(-11.5487), -PI() angle;
Result
{"asinh":-3.141589,"angle":-3.141593}

ATAN

Returns the inverse tangent value of x. The return value is expected to be in radians.

Syntax
ATAN ( x )
Parameters
x

Must be a numeric value or the value which can be cast to the numeric format.

Example 44. Using ATAN
Obtaining the inverse tangent
SELECT ATAN(1), PI()/4 angle;
Result
{"atan":0.785398,"angle":0.785398}

ATAN2

Returns the four-quadrant inverse value of the tangent function. ATAN2(y, x) is the angle between the positive x-axis of a plane and the point given by the coordinates (x, y) on it measured in radians.

Syntax
ATAN2 ( x, y )
Parameters
x

The floating point value representing the x-coordinate. Must be a numeric value or the value which can be cast to the numeric format.

y

The floating point value representing the y-coordinate. Must be a numeric value or the value which can be cast to the numeric format.

Example 45. Using ATAN2
Obtaining the four-quadrant inverse value of the tangent
SELECT ATAN2(4,2);
Result
{"atan2":1.107149}

ATANH

Returns the inverse hyperbolic tangent of x. The return value is expected to be in radians.

Syntax
ATANH ( x )
Parameters
x

Numeric value or value which can be cast to the numeric format at the range between -1 and 1.

Example 46. Using ATANH
Obtaining the inverse hyperbolic tangent value
SELECT ATANH(0);
Result
{"atanh":0}

CEIL

Returns the smallest integer value not less than the parameter. Return type is the same as the type of x.

Syntax
CEIL ( x )
Parameters
x

Numeric value to convert.

Example 47. Using CEIL
List of SQL commands
SELECT CEIL(4), TYPE(CEIL(4));
SELECT CEIL(4.35), TYPE(CEIL(4.35));
Result
{"ceil":4,"type":"int"}
{"ceil":5,"type":"double"}

CEIL_TO_INT

Returns the smallest integer value not less than the parameter.

This function is similar to CEIL with the following alterations:

  • Type of the CEIL return value is the same as its argument, while CEIL_TO_INT always returns integers

  • The CEIL function returns negative zero for real values at the range between -1 and 0, while CEIL_TO_INT returns 0

    Syntax
CEIL_TO_INT ( x )
Parameters
x

A numeric value.

Example 48. Using CEIL_TO_INT
List of SQL commands
SELECT CEIL_TO_INT(4), TYPE(CEIL_TO_INT(4));
SELECT CEIL_TO_INT(4.35), TYPE(CEIL_TO_INT(4.35));
Result
{"ceil_to_int":4,"type":"int"}
{"ceil_to_int":5,"type":"bigint"}

COS

Returns the cosine of x. The argument is expected to be in radians.

Syntax
COS ( x )
Parameters
x

Numeric value or value which can be cast to the numeric format.

Example 49. Using COS
Obtaining the cosine value
SELECT COS(PI()/3);
Result
{"cos":0.5}

COSH

Returns the hyperbolic cosine of x. The argument is expected to be in radians.

Syntax
COSH ( x )
Parameters
x

Numeric value or value which can be cast to numeric.

Example 50. Using COSH
Obtaining the hyperbolic cosine value
SELECT COSH(PI()/3);
Result
{"cosh":1.600287}

DEGREES

Converts a value in radians to degrees, as opposed to the RADIANS function.

Syntax
DEGREES ( x )
Parameters
x

Numeric value to convert.

Example 51. Using DEGREES
Converting to degrees
SELECT DEGREES(PI()/3);
Result
{"degrees":60}

EXP

Returns the exponential value of x.

Syntax
EXP ( x )
Parameters
x

Numeric value or value which can be cast to the numeric format.

Example 52. Using EXP
Obtaining the exponential value
SELECT EXP(-1);
Result
{"exp":0.367879}

FLOOR

Returns the largest integer value not greater than the parameter. The return type is the same with x.

Syntax
FLOOR ( x )
Parameters
x

Numeric value to convert.

Example 53. Using FLOOR
List of SQL commands
SELECT FLOOR(5), TYPE(FLOOR(5));
SELECT FLOOR(5.75), TYPE(FLOOR(5.75));
Result
{"floor":5,"type":"int"}
{"floor":5,"type":"double"}

FLOOR_TO_INT

Returns the largest integer value not greater than the parameter. The return type is integer.

Syntax
FLOOR_TO_INT ( x )
Parameters
x

Numeric value to convert.

Example 54. Using FLOOR_TO_INT
List of SQL commands
SELECT FLOOR_TO_INT(5), TYPE(FLOOR_TO_INT(5));
SELECT FLOOR_TO_INT(5.75), TYPE(FLOOR_TO_INT(5.75));
Result
{"floor_to_int":5,"type":"int"}
{"floor_to_int":5,"type":"bigint"}

GREATEST

This variadic function takes several parameters and returns the greatest value among them. All parameters must be of the same type.

Syntax
GREATEST ( x [, ... ] )
Parameters
x, …​

Several parameters of the same type.

Example 55. Using GREATEST
Obtaining the greatest value
SELECT GREATEST(4, 5, NULL, 8.4);
Result
{"greatest":8.4}

IS_EVEN

Checks whether a value is even and can be divided by 2. For floating point values, the function checks for presence of a remainder also dividing it by 2.

Syntax
IS_EVEN ( x )
Parameters
x

Value of a numeric type or a string containing a numeral.

Example 56. Using IS_EVEN
Checking for evenness
SELECT IS_EVEN(null) n, IS_EVEN(512);
Result
{"n":null,"is_even":true}
See also

IS_ODD, MOD

IS_FINITE

Check whether x is finite. The values above the maximum of DOUBLE are considered as infinite.

Note that IS_NAN is neither finite nor infinite. The opposite function to IS_FINITE is IS_INF.

Syntax
IS_FINITE ( x )
Parameters
x

Must be a numeric value. If this parameter is NULL, the NULL value will be returned.

Example 57. Using IS_FINITE
List of SQL commands
SELECT 2/0 AS "2/0", IS_FINITE(2/0);
SELECT IS_FINITE(null), IS_FINITE(512) AS fin;
Result
{"2/0":null,"is_finite":false}
{"is_finite":null,"fin":true}

IS_INF

Checks whether the parameter is infinite, i.e. the value is greater than the maximum of DOUBLE. Note that IS_NAN is not infinite. The opposite function to IS_INF is the IS_FINITE function.

Syntax
IS_INF ( x )
Parameters
x

Must be a numeral. If this parameter is NULL, the NULL value will be returned.

Example 58. Using IS_INF
List of SQL commands
SELECT 2/0 AS "2/0", IS_INF(2/0);
SELECT IS_INF(null), IS_INF(512) AS inf;
Result
{"2/0":null,"is_inf":true}
{"is_inf":null,"inf":false}

IS_NAN

Checks whether x is an invalid number or NaN (Not a Number) representing undefined or unrepresentable values like 0/0 or ±∞/±∞. For non-numeric arguments this function returns NULL.

Both infinite values are not NaN.

Syntax
IS_NAN ( x )
Parameters
x

Numeric value to check for validity.

Example 59. Using IS_NAN
Checking for the number validity
SELECT IS_NAN(2/0) a, IS_NAN(0.2) b;
Result
{"a":false,"b":false}

IS_NORMAL

Checks whether x is a normal number. It means that running the IS_INF and IS_NAN will return FALSE. Besides that, x cannot be zero or subnormal.

Syntax
IS_NORMAL ( x )
Parameters
x

Numeric value to check.

Example 60. Using IS_NORMAL
Checking for normality
SELECT IS_NORMAL(3) "3", IS_NORMAL(0) "0";
Result
{"3":true,"0":false}

IS_ODD

Checks if x is odd, i.e. not divisible by 2. For floating point values, it checks that dividing the value by 2 produces a remainder of 1.

Syntax
IS_ODD ( x )
Parameters
x

Must be a numeral or a string holding a numeral.

Example 61. Using IS_ODD
Checking for oddness
SELECT IS_ODD(3) "3", IS_ODD(4) "4";
Result
{"3":true,"4":false}

LEAST

This is a variadic function that takes several parameters and the smallest numeral. All parameters must be of the same type.

Syntax
LEAST ( x [, ... ] )
Parameters
x, …​

A number of parameters of the same type.

Example 62. Using LEAST
Obtaining the smallest numeral
SELECT LEAST(4, 5, NULL, 8.4);
Result
{"least":4}

LN

Returns the natural logarithm of x.

Syntax
LN ( x )
Parameters
x

Numeral or value of any type which can be cast to numeric greater than zero.

Example 63. Using LN
Obtaining the natural logrithm
SELECT LN(15) "15", LN(-14) "-14";
Result
{"15":2.70805,"-14":null}

LOG

Returns the base y logarithm of x. In case of one parameter, it returns the natural logarithm.

Syntax
LOG ( x )
LOG ( x, y )
Parameters
x, y

Numerals or any values which can be cast to the numeric format.

Example 64. Using LOG
List of SQL commands
SELECT LOG(3) "ln(3)", LOG(3, 10) "log(3)";
SELECT LOG2(3), LOG10(3);
Result
{"ln(3)":1.098612,"log(3)":0.477121}
{"log2":1.584963,"log10":0.477121}

LOG10

Returns the logarithm with base 10 of x.

Syntax
LOG10 ( x )
Parameters
x

Numeral or any value which can be cast to numeric format.

Example 65. Using LOG10
Obtaining the logarithm
SELECT LOG2(3), LOG10(3);
Result
{"log2":1.584963,"log10":0.477121}

LOG2

Returns the logarithm with base 2 of x.

Syntax
LOG2 ( x )
Parameters
x

Numeral or any value which can be cast to numeric format.

Example 66. Using LOG2
Obtaining the logarithm
SELECT LOG2(3), LOG10(3);
Result
{"log2":1.584963,"log10":0.477121}

MOD

Returns the remainder of x divided by y. If y equals 0, the result is NULL.

The return type depends on the operands, see the example below.

Syntax
MOD ( x, y )
Parameters
x, y

Must be of a numeric type or a string holding a numeral.

Example 67. Using MOD
Obtaining the division remainder
SELECT MOD(3,2) "mod(3,2)", TYPE(MOD(3,2));
SELECT MOD(8.3,3.1) "mod(8.3,3.1)", TYPE(MOD(8.3,3.1));
SELECT MOD(8.3,3) "mod(8.3,3)", TYPE(MOD(8.3,3));
SELECT MOD('8.3','3') "mod(8.3,3)", TYPE(MOD('8.3','3'));
Result
{"mod(3,2)":1,"type":"int"}
{"mod(8.3,3.1)":2.1,"type":"double"}
{"mod(8.3,3)":2.3,"type":"double"}
{"mod(8.3,3)":2.3,"type":"double"}

PI

Returns the value of PI (3.14159).

Syntax
PI ()

POWER

Returns x to the power of y.

Aliases

POW

Syntax
POWER ( x, y )
Parameters
x, y

Numerals or any values which can be cast to the numeric format.

Example 68. Using POWER
Calculating a numeral
SELECT POWER(2, 8), POW(25, 0.5);
Result
{"power":256,"pow":5}

RADIANS

Converts a value in degrees to radians as opposed to the DEGREES function.

Syntax
RADIANS ( x )
Parameters
x

Numeral to convert.

Example 69. Using RADIANS
Converting to radians
SELECT RADIANS(180);
Result
{"radians":3.141593}

ROUND

Returns a value of x rounded to the nearest integer. Return value type is the same with x.

Syntax
ROUND ( x )
ROUND ( x, y )
Parameters
x

The value to round. Must be a numeric value.

y

Required precision. Must be an integer value. Note that negative values are also correct and using them causes rounding the integer part of the value.

Example 70. Using ROUND
List of SQL commands
SELECT ROUND(42.46), ROUND(42.46, 1) with_precision;
SELECT TYPE(ROUND(42.46)) round_type;
SELECT ROUND(1.5) "1.5", TYPE(ROUND(1.5));
Result
{"round":42,"with_precision":42.5}
{"round_type":"double"}
{"1.5":2,"type":"double"}

ROUND_TO_INT

Returns a value of x rounded to the nearest integer. The return value type is INT.

Syntax
ROUND_TO_INT ( x )
Parameters
x

Numeric value to round.

Example 71. Using ROUND_TO_INT
List of SQL commands
SELECT ROUND_TO_INT(42) "42", TYPE(ROUND_TO_INT(42));
SELECT ROUND_TO_INT(1.5) "1.5", TYPE(ROUND_TO_INT(1.5));
Result
{"42":42,"type":"int"}
{"1.5":2,"type":"bigint"}

SIN

Returns the sine of x. The argument is expected to be in RADIANS.

Syntax
SIN ( x )
Parameters
x

Numeral or any value which can be cast to numeric format.

Example 72. Using SIN
Obtaining the sine
SELECT SIN(RADIANS(90));
Result
{"sin":1}

SINH

Returns the hyperbolic sine of x. The argument is expected to be in RADIANS.

Syntax
SINH ( x )
Parameters
x

Numeral or any value which can be cast to numeric format.

Example 73. Using SINH
Obtaining the hyperbolic sine
SELECT SINH(RADIANS(90));
Result
{"sinh":2.301299}

SQRT

Returns the square root of x. Fails for negative parameters.

Syntax
SQRT ( x )
Parameters
x

Positive numeral or any positive value which can be cast to numeric format.

Example 74. Using SQRT
Obtaining the square root
SELECT SQRT(4) "positive", SQRT(-4) "negative";
Result
{"positive":2,"negative":null}

TAN

Returns the tangent of x. The argument is expected to be in RADIANS.

Syntax
TAN ( x )
Parameters
x

Numeral or any value which can be cast to numeric format.

Example 75. Using TAN
Obtaining the tangent value
SELECT TAN(PI()/4);
Result
{"tan":1}

TANH

Returns the hyperbolic tangent of x. The argument is expected to be in RADIANS.

Syntax
TANH ( x )
Parameters
x

Numeral or any value which can be cast to numeric format.

Example 76. Using TANH
Obtaining the hyperbolic tangent value
SELECT TANH(PI()/4);
Result
{"tanh":0.655794}

TRUNC

Truncates x toward zero. Return value type is the same as the type of x.

Syntax
TRUNC ( x )
Parameters
x

Value to be truncated of numeric format.

Example 77. Using TRUNC
Truncating the value
SELECT TRUNC(18.99);
Result
{"trunc":18}

String functions

Raijin supports the following string functions:

CONCAT

LEFT

LENGTH

LOWER

LTRIM

OVERLAY

POSITION

REGEXP_EXTRACT

REGEXP_MATCH

REGEXP_MATCH_PARTIAL

REGEXP_REPLACE

REPLACE

RIGHT

RTRIM

SUBSTRING

TO_STRING

TO_CHAR

TRIM

UPPER

Table 3. Using string functions
SQL Result

SELECT CONCAT('Foo ', 'bar ', null, 123) _;

{"_":"Foo bar 123"}

SELECT LEFT('Foo_bar', 4);

{"left":"Foo_"}

SELECT LENGTH('Foo_bar');

{"length":7}

SELECT CHAR_LENGTH('Foo_bar');

{"char_length":7}

SELECT CHARACTER_LENGTH('Foo_bar');

{"character_length":7}

SELECT LOWER('FOOBAR');

{"lower":"foobar"}

SELECT LCASE('FOOBAR');

{"lcase":"foobar"}

SELECT LTRIM(' Foo_bar');

{"ltrim":"Foo_bar"}

SELECT OVERLAY('Foo_bar' PLACING '432' FROM 3);

{"overlay":"Fo432ar"}

SELECT POSITION('bar' in 'Foo_bar');

{"position":5}

SELECT POSITION('hello' in 'Foo_bar');

{"position":0}

SELECT REGEXP_EXTRACT('www.host.org', '.*\\.(\\w+)') domain;

{"group_1":"org"}

SELECT REGEXP_MATCH('www.host.org', '[w]{3}\\.[\\w]+');

{"regexp_match":false}

SELECT REGEXP_MATCH('www.host.org', '[w]{3}\\.[\\w]+', true);

{"regexp_match_partial":true}

SELECT REGEXP_MATCH_PARTIAL('www.host.org', '[w]{3}\\.[\\w]+');

{"regexp_match_partial":true}

SELECT REGEXP_REPLACE('www.host.org', '[\\w]', '*');

{"regexp_replace":".."}

SELECT REPLACE('war war war', 'war', 'peace');

{"replace":"peace peace peace"}

SELECT RIGHT('Foo_bar', 4);

{"right":"_bar"}

SELECT RTRIM('Foo_bar ');

{"rtrim":"Foo_bar"}

SELECT SUBSTRING('Foo_bar', 2, 5);

{"substring":"oo_ba"}

SELECT SUBSTRING('Foo_bar', 5);

{"substring":"bar"}

SELECT SUBSTRING('Foo_bar' FROM 2 FOR 5);

{"substring":"oo_ba"}

SELECT SUBSTRING('Foo_bar' FOR 5);

{"substring":"Foo_b"}

SELECT SUBSTR('Foo_bar', 2, 5);

{"substr":"oo_ba"}

SELECT TO_STRING(-634.27);

{"to_string":"-634.27"}

SELECT STRING(true);

{"string":"TRUE"}

SELECT TRIM(' Foo_bar ');

{"trim":"Foo_bar"}

SELECT BTRIM(' Foo_bar ');

{"btrim":"Foo_bar"}

SELECT UPPER('Foo_bar');

{"upper":"FOO_BAR"}

SELECT UCASE('Foo_bar');

{"ucase":"FOO_BAR"}

CONCAT

Joins all of the parameters to one string and returns it.

Syntax
CONCAT ( value [, ...] )
Parameters
value, …​

An arbitrary amount of values that will be converted to strings.

Example 78. Using CONCAT
Concatenating strings
SELECT CONCAT('Foo ', 'bar ', null, 123) _;
Result
{"_":"Foo bar 123"}

LEFT

Returns the leftmost len characters from string.

Returns NULL if either string or len evaluates to NULL.

Syntax
LEFT ( string, len )
Parameters
string

Value of the STRING type.

len

Length of the substring. If negative, it is interpreted as zero.

Example 79. Using LEFT
Obtaining a substring
SELECT LEFT('Foo_bar', 4);
Result
{"left":"Foo_"}

LENGTH

Returns the number of characters in a string.

Syntax
LENGTH ( string )
Aliases

LEN, CHAR_LENGTH, CHARACTER_LENGTH

Parameters
string

A string to obtain the length from.

Example 80. Using LENGTH
List of SQL commands
SELECT LENGTH('Foo_bar');
SELECT CHAR_LENGTH('Foo_bar');
SELECT CHARACTER_LENGTH('Foo_bar');
Result
{"length":7}
{"char_length":7}
{"character_length":7}

LOWER

Converts the given string to lower case.

Syntax
LOWER ( string )
Aliases

LCASE

Parameters
string

A string to lower case.

Example 81. Using LOWER
List of SQL commands
SELECT LOWER('FOOBAR');
SELECT LCASE('FOOBAR');
Result
{"lower":"foobar"}
{"lcase":"foobar"}

LTRIM

Removes left-side whitespaces around a string. Returns a string with no whitespaces on the left side.

Syntax
LTRIM ( string )
Parameters
string

A string to remove left-side whitespaces from.

Example 82. Using LTRIM
Trimming a string
SELECT LTRIM('    Foo_bar');
Result
{"ltrim":"Foo_bar"}

OVERLAY

Overlays a string on the given position. Returns a string with the substitution at the pos position and optionally limited according to the len parameter.

Syntax
OVERLAY ( string PLACING substitute FROM pos [ FOR len ] )
Parameters
string

A string to replace a substring in.

substitute

A string that overlays the string from the given position.

pos

Position where to place the substitute parameter.

len

Optional parameter specifying the maximum length of the replacement.

Example 83. Using OVERLAY
Overlaying a string
SELECT OVERLAY('Foo_bar' PLACING '432' FROM 3);
SELECT OVERLAY('Foo_bar' PLACING '1234' FROM 3 FOR 3);
Result
{"overlay":"Fo432ar"}
{"overlay":"Fo123ar"}

POSITION

Returns a position of a substring in the string. Position index starts with 1.

If the substring was not found in the string, this function returns 0 (zero).

Syntax
POSITION ( substring IN string )
Parameters
string

A string where a substring should be searched.

substring

A string which should be searched in the string.

Example 84. Using POSITION
List of SQL commands
SELECT POSITION('bar' in 'Foo_bar');
SELECT POSITION('hello' in 'Foo_bar');
Result
{"position":5}
{"position":0}

REGEXP_EXTRACT

Returns substrings which match capturing groups specified by regular expression patterns.

In case a pattern is constant, all matched parts of a string are returned as separate columns. Each column name is a capturing group name. If the group is unnamed, the column name is formed as <group_name_prefix><unnamed_group_index>, e.g. group_1.

The <group_name_prefix> part is configurable via the RegexpCapturingGroupPrefix config parameter. The default value is group_.

The returned columns can be used in the WHERE clause to filter data, in the ORDER BY clause it can be used to sort data, and the INSERT INTO …​ SELECT statement can use it to insert data to a new table.

At present, the returned result can be used only for simple queries containing one table.

If the regular expression pattern is not constant and contains explicit capturing groups, i.e. represents a table column, then one column is returned in a form: group_name_1: value, group_name_2: value, …​.

Syntax
REGEXP_EXTRACT ( string, pattern )
Parameters
string

String which should be processed by a regular expression.

pattern

String which represents a regular expression; RE2 syntax is used.

By default, the BackslashEscaping config parameter is set ON and two backslash characters are required when entering the pattern as an SQL string constant. The first backslash is used for the Raijin parser, and the second character is applied for the regex library. For example:
SELECT REGEXP_EXTRACT('www.host.org', '[w]{3}\.(\\w+)');
Result
{"group_1":"host"}
Example 85. Using REGEXP_EXTRACT
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;

USE testdb;
CREATE TABLE IF NOT EXISTS tbl(col varchar);
INSERT INTO tbl VALUES ('Some sentence with hello '),
                       ('youcansayhelloifyouwant '),
                       ('Yeah, hello.');

SELECT REGEXP_EXTRACT(col,'(.*)hello.*') as explicit from tbl;
SELECT REGEXP_EXTRACT(col,'.*hell') as implicit from tbl;
Result
{"group_1":"Some sentence with "}
{"group_1":"youcansay"}
{"group_1":"Yeah, "}
{"implicit":"Some sentence with hell"}
{"implicit":"youcansayhell"}
{"implicit":"Yeah, hell"}
Example 86. Capturing groups with constant pattern examples
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;
USE testdb;

CREATE TABLE t1 (data varchar);

INSERT INTO t1 (data) VALUES
('Failed password for invalid user linda from 192.168.1.60 port 38176 ssh2 attempts: 1'),
('Failed sms_code for invalid user peter from 192.168.1.70 port 38276 telnet attempts: 10'),
('Failed password for invalid user samanta from 192.168.1.80 port 38376 secure-ssh2 attempts: 5'),
('Failed password for invalid user john from 192.168.1.80 port 38376 secure-ssh2 attempts: 3'),
('Failed sms_code for invalid user linda from 192.168.1.160 port 38376 secure-ssh2 attempts: 11');

CREATE TABLE t2 ();

SELECT '1. Select capturing groups, using WHERE, ORDER BY clauses' _;
SELECT regexp_extract(data, '^Failed\\ (?P<AuthMethod>\\S+)\\ for(?:\\ invalid\\ user)\\ (?P<AccountName>\\S+)\\ .* port\\ (\\d+)') FROM t1 WHERE accountname LIKE 'lin%' ORDER BY group_1::int DESC;

SELECT '2. Insert using SELECT capturing groups' _;
INSERT INTO t2 SELECT regexp_extract(data, '^Failed\\ (?P<AuthMethod>\\S+)\\ for(?:\\ invalid\\ user)\\ (?P<AccountName>\\S+)\\ .* port\\ (\\d+)') FROM t1 WHERE accountname LIKE 'lin%' ORDER BY group_1::int DESC;
SELECT * FROM t2;

SELECT '2.1 New table fields' _;
SELECT FIELDS FROM t2;
Result
{"_":"1. Select capturing groups, using WHERE, ORDER BY clauses"}
{"AuthMethod":"sms_code","AccountName":"linda","group_1":"38376"}
{"AuthMethod":"password","AccountName":"linda","group_1":"38176"}
{"_":"2. Insert using SELECT capturing groups"}
{"_id":1,"AuthMethod":"sms_code","AccountName":"linda","group_1":"38376"}
{"_id":2,"AuthMethod":"password","AccountName":"linda","group_1":"38176"}
{"_":"2.1 New table fields"}
{"field":"AuthMethod","type":"STRING"}
{"field":"AccountName","type":"STRING"}
{"field":"group_1","type":"STRING"}
Example 87. Capturing groups with non-constant pattern examples
List of SQL commands
DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;
USE testdb;

CREATE TABLE t1 (data varchar, regexp varchar);

INSERT INTO t1 (data, regexp) VALUES
('Failed password for invalid user linda from 192.168.1.60 port 38176 ssh2 attempts: 1', '^Failed\\ (?P<AuthMethod>\\S+)\\ for(?:\\ invalid\\ user)\\ (?P<AccountName>\\S+)\\ from\\ (?P<SourceIPAddress>(?:\\d{1,3}\\.)\{3\}\\d{1,3})\\ port\\ (?P<Port>\\S+)\\ (?P<Protocol>\\S+)'),
('Failed password for invalid user linda from 192.168.1.60 port 38176 ssh2 attempts: 1', '^Failed\\ (\\S+)\\ for(?:\\ invalid\\ user)\\ (?P<AccountName>\\S+)\\ from\\ (?P<SourceIPAddress>(?:\\d{1,3}\\.)\{3\}\\d{1,3})\\ port\\ (?P<Port>\\S+)\\ (?P<Protocol>\\S+)\\ attempts\\:\\ (\\d+)'),
('Failed sms_code for invalid user peter from 192.168.1.70 port 38276 telnet attempts: 10', '^Failed\\ (?P<AuthMethod>\\S+)'),
('Failed password for invalid user samanta from 192.168.1.80 port 38376 secure-ssh2 attempts: 5', '(?P<AuthMethod>\\S+)\\ for(?:\\ invalid\\ user)\\ (?P<AccountName>\\S+)'),
('Failed password for invalid user john from 192.168.1.80 port 38376 secure-ssh2 attempts: 3', '\\S+\\ for(?:\\ invalid\\ user)\\ (?P<AccountName>\\S+)'),
('Failed sms_code for invalid user linda from 192.168.1.160 port 38376 secure-ssh2 attempts: 11', '(?P<SourceIPAddress>(?:\\d{1,3}\\.)\{3\}\\d{1,3})\\ port\\ (?P<Port>\\S+)');

SELECT '1. Regexp extract capturing groups non-constant pattern' _;
SELECT regexp_extract(data, regexp) FROM t1;

SELECT '2. Insert using SELECT capturing groups' _;
CREATE TABLE t2();
INSERT INTO t2 SELECT regexp_extract(data, regexp) FROM t1;
SELECT * FROM t2;

SELECT '2.1 New table fields' _;
SELECT FIELDS FROM t2;
Result
{"_":"1. Regexp extract capturing groups non-constant pattern"}
{"regexp_extract":"AuthMethod: password; AccountName: linda; SourceIPAddress: 192.168.1.60; Port: 38176; Protocol: ssh2"}
{"regexp_extract":"group_1: password; AccountName: linda; SourceIPAddress: 192.168.1.60; Port: 38176; Protocol: ssh2; group_2: 1"}
{"regexp_extract":"AuthMethod: sms_code"}
{"regexp_extract":"AuthMethod: password; AccountName: samanta"}
{"regexp_extract":"AccountName: john"}
{"regexp_extract":"SourceIPAddress: 192.168.1.160; Port: 38376"}
{"_":"2. Insert using SELECT capturing groups"}
{"_id":1,"regexp_extract":"AuthMethod: password; AccountName: linda; SourceIPAddress: 192.168.1.60; Port: 38176; Protocol: ssh2"}
{"_id":2,"regexp_extract":"group_1: password; AccountName: linda; SourceIPAddress: 192.168.1.60; Port: 38176; Protocol: ssh2; group_2: 1"}
{"_id":3,"regexp_extract":"AuthMethod: sms_code"}
{"_id":4,"regexp_extract":"AuthMethod: password; AccountName: samanta"}
{"_id":5,"regexp_extract":"AccountName: john"}
{"_id":6,"regexp_extract":"SourceIPAddress: 192.168.1.160; Port: 38376"}
{"_":"2.1 New table fields"}
{"field":"regexp_extract","type":"STRING"}

REGEXP_MATCH

Checks if a string matches a regular expressions. Returns TRUE if a string or its part matches the regular expression and FALSE otherwise.

Syntax
REGEXP_MATCH ( string, pattern [, partial])
Parameters
string

String to match against the regular expression.

pattern

String which represents a regular expression. The RE2 syntax is used.

By default, the BackslashEscaping config parameter is set ON and two backslash characters are required when entering the pattern as an SQL string constant. The first backslash is used for the Raijin parser, and the second character is applied for the regex library. For example:
SELECT REGEXP_EXTRACT('www.host.org', '[w]{3}\.(\\w+)');
Result
{"group_1":"host"}
partial

If set to TRUE, this boolean parameter will call partial matching. By default, it is set to FALSE.

Example 88. Using REGEXP_MATCH
List of SQL commands
SELECT REGEXP_MATCH('www.host.org', '[w]{3}\.[\\w]+');
SELECT REGEXP_MATCH('www.host.org', '[w]{3}\.[\\w]+', true);
Result
{"regexp_match":false}
{"regexp_match_partial":true}

REGEXP_MATCH_PARTIAL

Checks if a substring matches a regular expression.

Syntax
REGEXP_MATCH_PARTIAL ( string, pattern )
Aliases

REGEXP_MATCH when used with the partial parameter set to TRUE.

Parameters
string

String to match against the regular expression.

pattern

String which represents a regular expression. The RE2 syntax is used.

By default, the BackslashEscaping config parameter is ON and two backslash characters are required when entering the pattern as an SQL string constant. The first backslash is used for the Raijin parser, and the second character is applied for the regex library. For example:
SELECT REGEXP_EXTRACT('www.host.org', '[w]{3}\.(\\w+)');
Result
{"group_1":"host"}
Example 89. Using REGEXP_MATCH_PARTIAL
Matching a substring to a regular expression
SELECT REGEXP_MATCH_PARTIAL('www.host.org', '[w]{3}\.[\\w]+');
Result
{"regexp_match_partial":true}

REGEXP_REPLACE

Returns a substring matching the regular expression pattern and replaced according to the substitute pattern.

Syntax
REGEXP_REPLACE ( string, pattern, substitute )
string

String to be matched against the regular expression specified in the pattern parameter.

pattern

String which represents a regular expression. The RE2 syntax is used.

By default, the BackslashEscaping config parameter is set ON, and two backslash characters are required when entering the pattern as an SQL string constant. The first backslash is used for the Raijin parser, and the second character is applied for the regex library. For example:
SELECT REGEXP_EXTRACT('www.host.org', '[w]{3}\.(\\w+)');
Result
{"group_1":"host"}
substitute

String which should replace with the matched substring.

Example 90. Using REGEXP_REPLACE
Replacing a string
SELECT REGEXP_REPLACE('www.host.org', '[\\w]', '*');
SELECT REGEXP_REPLACE('www.host.org', '\\.', '*');
Result
{"regexp_replace":"***.****.***"}
{"regexp_replace":"www*host*org"}

REPLACE

Replaces all occurrences of the substring found in a string. Returns the string containing all occurrences of substring after replacing with the substitute parameter value.

Syntax
REPLACE ( string, substring, substitute )
Parameters
string

String which should be scanned for the substring occurrences.

substring

Substring to be searched in the string.

substitute

Substring which should replace all occurrences of the substring found in the string. If not given, all substring occurrences are removed from the string.

Example 91. Using REPLACE
Replacing a string
SELECT REPLACE('war war war', 'war', 'peace');
SELECT REPLACE('war war war', 'war');
Result
{"replace":"peace peace peace"}
{"replace":"  "}

Extracts the substring to the right of a specified position. Returns a substring starting at the len position to the end of string.

Returns NULL if either the string or len parameters evaluate to NULL.

Syntax
RIGHT ( string, len )
Parameters
string

String which substring should be extracted.

len

The position to extract the substring from. Position index starts with 1.

Example 92. Using RIGHT
Extracting the right part of a string
SELECT RIGHT('Foo_bar', 4);
Result
{"right":"_bar"}

RTRIM

Removes right-side whitespaces from a string. Returns the string containing no whitespaces on the right side.

Syntax
RTRIM ( string )
Parameters
string

String to cut right-side whitespaces from.

Example 93. Using RTRIM
Removing whitespaces from a string
SELECT RTRIM('Foo_bar    ');
Result
{"rtrim":"Foo_bar"}

SUBSTRING

Returns a substring starting at the specified position with the specified length.

Syntax
SUBSTRING ( string, pos, len )
SUBSTRING ( string, pos )
SUBSTRING ( string FROM pos FOR len )
SUBSTRING ( string FROM pos )
SUBSTRING ( string FOR len )
Parameters
string

String to create the substring from.

pos

Position to start the substring from. Index starts from 1. If position is specified negatively, it is interpreted as counted from the end backwards.

len

Length of the substring. If this parameter is not specified, it takes a substring starting at the position determined by pos and ending at the end of the original string.

Negative value is interpreted as zero.

If either the string, pos, or len parameters evaluate to NULL, this function returns NULL.

The following usage of the function:

SUBSTRING(str, 0, len)

always returns an empty string.

Example 94. Using SUBSTRING
List of SQL commands
SELECT SUBSTRING('Foo_bar', 2, 5);
SELECT SUBSTRING('Foo_bar', 5);
SELECT SUBSTRING('Foo_bar' FROM 2 FOR 5);
SELECT SUBSTRING('Foo_bar' FOR 5);
Result
{"substring":"oo_ba"}
{"substring":"bar"}
{"substring":"oo_ba"}
{"substring":"Foo_b"}

TO_STRING

Returns a string representation of a value.

Syntax
TO_STRING ( value )
Aliases

STRING

Parameters
value

Value which needs to be represented as string. Can be any value including NULL.

Example 95. Using TO_STRING
List of SQL commands
SELECT TO_STRING(-634.27);
SELECT STRING(true);
Result
{"to_string":"-634.27"}
{"string":"TRUE"}

TO_CHAR

Converts various data types to formatted strings.

Syntax
TO_CHAR ( value, format )
Parameters
value

Value of datetime, integer, or floating point type.

format

String template to define the output format.

Description

In a to_char output template string, there are certain patterns that are recognized and replaced with appropriately-formatted data based on the given value. Any text that is not a template pattern is simply copied verbatim.

Table 4. Template patterns for date/time formatting
Pattern Description

HH

hour of day (01-12)

HH12

hour of day (01-12)

HH24

hour of day (00-23)

MI

minute (00-59)

SS

second (00-59)

MS

millisecond (000-999)

US

microsecond (000000-999999)

SSSS

seconds past midnight (0-86399)

AM, am, PM, or pm

meridiem indicator (without periods)

A.M., a.m., P.M., or p.m.

meridiem indicator (with periods)

Y, YYY

year (4 or more digits) with comma

YYYY

year (4 or more digits)

YYY

last 3 digits of year

YY

last 2 digits of year

Y

last digit of year

IYYY

ISO 8601 week-numbering year (4 or more digits)

IYY

last 3 digits of ISO 8601 week-numbering year

IY

last 2 digits of ISO 8601 week-numbering year

I

last digit of ISO 8601 week-numbering year

BC, bc, AD, or ad

era indicator (without periods)

B.C., b.c., A.D., or a.d.

era indicator (with periods)

MONTH

full upper case month name (blank-padded to 9 chars)

Month

full capitalized month name (blank-padded to 9 chars)

month

full lower case month name (blank-padded to 9 chars)

MON

abbreviated upper case month name (3 chars in English, localized lengths vary)

Mon

abbreviated capitalized month name (3 chars in English, localized lengths vary)

mon

abbreviated lower case month name (3 chars in English, localized lengths vary)

MM

month number (01-12)

DAY

full upper case day name (blank-padded to 9 chars)

Day

full capitalized day name (blank-padded to 9 chars)

day

full lower case day name (blank-padded to 9 chars)

DY

abbreviated upper case day name (3 chars in English, localized lengths vary)

Dy

abbreviated capitalized day name (3 chars in English, localized lengths vary)

dy

abbreviated lower case day name (3 chars in English, localized lengths vary)

DDD

day of year (001-366)

IDDD

day of ISO 8601 week-numbering year (001-371; day 1 of the year is Monday of the first ISO week)

DD

day of month (01-31)

D

day of the week, Sunday (1) to Saturday (7)

ID

ISO 8601 day of the week, Monday (1) to Sunday (7)

W

week of month (1-5) (the first week starts on the first day of the month)

WW

week number of year (1-53) (the first week starts on the first day of the year)

IW

week number of ISO 8601 week-numbering year (01-53; the first Thursday of the year is in week 1)

CC

century (2 digits) (the twenty-first century starts on 2001-01-01)

J

Julian Day (integer days since November 24, 4714 BC at midnight UTC)

Q

quarter

RM

month in upper case Roman numerals (I-XII; I=January)

rm

month in lower case Roman numerals (i-xii; i=January)

TZ

upper case time-zone abbreviation

tz

lower case time-zone abbreviation

OF

time-zone offset from UTC

Modifiers can be applied to any template pattern to alter its behavior. For example, FMMonth is the Month pattern with the FM modifier. The table below shows the modifier patterns for date/time formatting.

Table 5. Template pattern modifiers for date/time formatting
Modifier Description Example

FM prefix

Fill mode (suppresses leading zeroes and padding blanks)

FMMonth

TH suffix

Upper case ordinal number suffix

DDTH, e.g. 12TH

th suffix

Lower case ordinal number suffix

DDth, e.g. 12th

FX prefix

Fixed format global option (see usage notes)

FX Month DD Day

TM prefix

Translation mode (print localized day and month names based on lc_time)

TMMonth

Usage notes for date/time formatting:

  • FM suppresses leading zeroes and trailing blanks that would otherwise be added to make the output of a pattern be fixed-width. Like PostgreSQL, in Raijin FM modifies only the next specification, while in Oracle FM affects all subsequent specifications, and repeated FM modifiers toggle fill mode on and off

  • TM does not include trailing blanks

  • Ordinary text is allowed in to_char templates and will be output literally. You can put a substring in double quotes to force it to be interpreted as literal text even if it contains pattern keywords. For example, in "Hello Year "YYYY, the YYYY part will be replaced by the year data, but the single Y in Year will not.

  • If you want to have a double quote in the output, you must precede it with a backslash like \"YYYY Month\".

The table below shows the template patterns available for formatting numeric values.

Table 6. Template patterns for numeric formatting
Pattern Description

9

digit position (can be dropped if insignificant)

0

digit position (will not be dropped, even if insignificant)

. (period)

decimal point

, (comma)

group (thousands) separator

PR

negative value in angle brackets

S

sign anchored to number (uses locale)

L

currency symbol (uses locale)

D

decimal point (uses locale)

G

group separator (uses locale)

MI

minus sign in specified position (if number < 0)

PL

plus sign in specified position (if number > 0)

SG

plus/minus sign in specified position

RN

Roman numeral (input between 1 and 3999)

TH or `th

ordinal number suffix

V

shift specified number of digits (see notes)

EEEE

exponent for scientific notation

Usage notes for numeric formatting:

  • Zero (0) specifies a digit position that will always be printed, even if it contains a leading/trailing zero. Nin (9) also specifies a digit position; in case it is a leading zero, it will be replaced by a space. It will be deleted if it is a trailing zero and fill mode is specified

  • The pattern characters S, L, D, and G represent the sign, currency symbol, decimal point, and thousands separator characters defined by the current locale. The period (.) and comma (,) pattern characters represent those exact characters meaning decimal point and thousands separators, regardless of locale

  • If no explicit provision is made for a sign in to_char() pattern, one column will be reserved for the sign, and it will be anchored to (appear just left of) the number. If S appears just to the left of 9, it will likewise be anchored to the number

  • A sign formatted using SG, PL, or MI is not anchored to the number. For example, using to_char(-12, 'MI9999') produces - 12, but the to_char(-12, 'S9999') function call produces ` -12. The Oracle implementation does not allow using MI before 9, it requires that 9 precede MI instead

  • TH does not convert values less than zero and does not convert fractional numbers

  • V with to_char multiplies the input values by 10^n, where n is the number of digits following V. The to_char function does not support the use of V combined with a decimal point, e.g. 99.9V99 is not allowed

  • The EEEE scientific notation cannot be used in combination with any of the other formatting patterns or modifiers other than digit and decimal point patterns. Instead, it must be at the end of the format string, e.g. 9.99EEEE is a valid pattern

  • Certain modifiers can be applied to any template pattern to alter its behavior. For example, FM99.99 is the 99.99 pattern with the FM modifier applied.

The table below shows the modifier patterns for numeric formatting.

Table 7. Template pattern modifiers for numeric formatting
Modifier Description Example

FM prefix

fill mode (suppress trailing zeroes and padding blanks)

FM99.99

TH suffix

upper case ordinal number suffix

999TH

th suffix

lower case ordinal number suffix

999th

The following table demonstrates some examples of the to_char function using.

Table 8. Using TO_CHAR
Expression Result

SELECT to_char('2017-01-01 00:00:00'::DATETIME, 'Day, DD HH12:MI:SS');

{"to_char":"Sunday , 01 12:00:00"}

SELECT to_char('2017-12-26 00:00:00'::DATETIME, 'FMDay, FMDD HH12:MI:SS');

{"to_char":"Tuesday, 26 12:00:00"}

SELECT to_char(-0.1, '99.99');

{"to_char":" -.10"}

SELECT to_char(-0.1, 'FM9.99');

{"to_char":"-.1"}

SELECT to_char(-0.1, 'FM90.99');

{"to_char":"-0.1"}

SELECT to_char(0.1, '0.9');

{"to_char":" 0.1"}

SELECT to_char(12, '9990999.9');

{"to_char":" 0012.0"}

SELECT to_char(12, 'FM9990999.9');

{"to_char":"0012."}

SELECT to_char(485, '999');

{"to_char":" 485"}

SELECT to_char(-485, '999');

{"to_char":"-485"}

SELECT to_char(485, '9 9 9');

{"to_char":" 4 8 5"}

SELECT to_char(1485, '9,999');

{"to_char":" 1,485"}

SELECT to_char(148.5, '999.999');

{"to_char":" 148.500"}

SELECT to_char(148.5, 'FM999.999');

{"to_char":"148.5"}

SELECT to_char(148.5, 'FM999.990');

{"to_char":"148.500"}

SELECT to_char(-485, '999MI');

{"to_char":"485-"}

SELECT to_char(485, '999MI');

{"to_char":"485 "}

SELECT to_char(485, 'FM999MI');

{"to_char":"485"}

SELECT to_char(-485, '999PR');

{"to_char":"<485>"}

SELECT to_char(485, 'RN');

{"to_char":" CDLXXXV"}

SELECT to_char(485, 'FMRN');

{"to_char":"CDLXXXV"}

SELECT to_char(5.2, 'FMRN');

{"to_char":"V"}

SELECT to_char(482, '999th');

{"to_char":" 482nd"}

SELECT to_char(485, '"Good number:"999');

{"to_char":"Good number: 485"}

SELECT to_char(485.8, '"Pre:"999" Post:" .999');

{"to_char":"Pre: 485 Post: .800"}

SELECT to_char(12, '99V999');

{"to_char":" 12000"}

SELECT to_char(12.4, '99V999');

{"to_char":" 12400"}

SELECT to_char(12.45, '99V9');

{"to_char":" 124"}

SELECT to_char(0.0004859, '9.99EEEE');

{"to_char":" 4.86e-04"}

TRIM

Returns the input string with both leading and trailing spaces removed.

Syntax
TRIM ( string )
Aliases

BTRIM

Parameters
string

A string value for removing trailing spaces.

Example 96. Using TRIM
List of SQL commands
SELECT TRIM('  Foo_bar  ');
SELECT BTRIM('  Foo_bar  ');
Result
{"trim":"Foo_bar"}
{"btrim":"Foo_bar"}

UPPER

Converts a string to upper case.

Syntax
UPPER ( string )
Aliases

UCASE

Parameters
string

A string value to make it uppercase.

Example 97. Using UPPER
List of SQL commands
SELECT UPPER('Foo_bar');
SELECT UCASE('Foo_bar');
Result
{"upper":"FOO_BAR"}
{"ucase":"FOO_BAR"}

Miscellaneous functions

Raijin supports the CAST function described below.

CAST

Converts the expression of one data type to another.

Syntax
CAST ( expression AS data_type )

Alternatively, you can use the equivalent PostgreSQL syntax:

expression :: data_type
Parameters
expression

Expression which should be converted to a different type.

data_type

Data type which the expression should be converted to.

Usage notes

If the casted value does not fit the target type, NULL will be returned.

Only allowed conversions are performed as per the following table.

Table 9. Conversions
\ INT BIGINT FLOAT DOUBLE BOOL STRING DATETIME

INT

+

+

+

+

+

+

-

BIGINT

+

+

+

+

+

+

+

FLOAT

+

+

+

+

+

+

+

DOUBLE

+

+

+

+

+

+

+

BOOL

+

+

+

+

+

+

-

STRING

+

+

+

+

+

+

+

DATETIME

-

+

+

+

-

+

+

Example 98. Using CAST
List of SQL commands
SELECT CAST('1.89' AS BIGINT);
SELECT CAST(1.89 AS STRING);
SELECT 1.89::DOUBLE;
SELECT 1.89::BOOL;
SELECT 1.89::BOOL::STRING;
SELECT '2016-08-04 17:14:04Z'::DATETIME::BIGINT;
Result
{"INT64":2}
{"STRING":"1.89"}
{"DOUBLE":1.89}
{"BOOL":true}
{"STRING":"TRUE"}
{"INT64":1470330844000000}

Operators

This table contains the list of operators supported by Raijin.

Table 10. List of operators
Operator Name Operand type Return type

+

Add

INT, BIGINT, DOUBLE

INT, BIGINT, DOUBLE

-

Subtract

INT, BIGINT, DOUBLE

INT, BIGINT, DOUBLE

*

Multiply

INT, BIGINT, DOUBLE

INT, BIGINT, DOUBLE

/

Divide

INT, BIGINT, DOUBLE

INT, BIGINT, DOUBLE

%

Modulus

INT, BIGINT

INT, BIGINT

-

Negate

INT, BIGINT, DOUBLE

INT, BIGINT, DOUBLE

=

Equal

Any

BOOLEAN

!=

Not equal

Any

BOOLEAN

<>

Not equal

Any

BOOLEAN

<

Less than

Any

BOOLEAN

<=

Less than or equal

Any

BOOLEAN

>

Greater than

Any

BOOLEAN

>=

Greater than or equal

Any

BOOLEAN

|

Concatenate

Any

STRING

AND

Logical AND

BOOLEAN

BOOLEAN

OR

Logical OR

BOOLEAN

BOOLEAN

XOR

Logical XOR#

BOOLEAN#

BOOLEAN#

NOT

Logical NOT

BOOLEAN

BOOLEAN

&

Binary AND

BOOLEAN

BOOLEAN

\

Binary OR#

INT, BIGINT#

INT, BIGINT#

^

Binary XOR#

INT, BIGINT#

INT, BIGINT#

~

Binary NOT

BOOLEAN

INT, BIGINT

<<

Binary shift left

INT, BIGINT

INT, BIGINT

>>

Binary shift right#

INT, BIGINT#

INT, BIGINT#

~~

Regex like

STRING

STRING

!~~

Regex not like

STRING

STRING

~~*

Regex like case insensitive

STRING

STRING

!~~*

Regex not like case insensitive

STRING

STRING

MATCH AGAINST

Word-based search, case-sensitive

STRING

BOOLEAN

IMATCH AGAINST

Word-based search, case-insensitive

STRING

BOOLEAN

MATCH AGAINST

Performs word-based search, case-sensitive.

Syntax
MATCH (col_name) AGAINST (expr)
Parameters
col_name

Column to be searched.

expr

Search string that specifies which words to search for.

Usage notes

The table below shows some example searches. Words in a search string not preceded by operator are optional, and the row is selected if any of the optional words is present in a column. Words preceded by + are mandatory, and they must be present in a row. Words preceded by - are prohibited, and they must not be present in a row.

Note that - operator acts only to exclude rows that are otherwise matched by other search terms. Thus, a search that contains only terms preceded by - returns an empty result. It does not return "all rows except those containing any of the excluded terms."

For + and - operators to have effect, they must be placed immediately before the word or open parentheses. Otherwise, they are ignored. For example, + word1 or - word1 are iterpreted as ` word1` (meaning that word1 is optional). Similarly, +word1 - (word2 word3) is interpreted as +word1 (word2 word3).

Table 11. MATCH AGAINST queries
Query Description

SELECT * FROM tbl WHERE MATCH (col) AGAINST ('word1')

Selects all rows that contain word1

SELECT * FROM tbl WHERE MATCH (col) AGAINST ('word1 word2')

Selects all rows that contain either word1 or word2

SELECT * FROM tbl WHERE MATCH (col) AGAINST ('+word1 +word2')

Selects all rows that contain both word1 and word2

SELECT * FROM tbl WHERE MATCH (col) AGAINST ('word1 -word2')

Selects all rows that contain word1 and do not contain word2

SELECT * FROM tbl WHERE MATCH (col) AGAINST ('word1 word2 +(word3 word4)')

Selects all rows that contain word1 or word2, and which either contain word3 or word4

SELECT * FROM tbl WHERE MATCH (col) AGAINST ('+word1 -(word2 word3)')

Selects all rows that contain word1 and that neither contain word2 nor word3

Column is tokenized or split to words by considering everything that is not a letter or digit as a word boundary. That is, word is a sequence of letters and/or digits.

Infix syntax with the operators explicitly named and placed between operands is not supported, but it can be expressed using the supported syntax:

word1 OR word2

word1 word2

word1 AND word2

+word1 +word2

word1 AND NOT word2

+word1 -word2

IMATCH AGAINST

Performs word-based search, case-insensitive.

Syntax
IMATCH (col_name) AGAINST (expr)
Parameters
col_name

Column to be searched.

expr

Search string that specifies which words to search for.

Usage notes

See the MATCH AGAINST function description.