# Functions and operators

## Functions

Raijin contains several built-in functions for performing calculations and data conversion. This section describes the following types of functions:

### Aggregate functions

Functions that require a `GROUP BY` clause in the `SELECT` statement. Below is the list of aggregate functions supported by Raijin:

#### 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(value INTEGER, gr_name TEXT);

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

SELECT FIRST(value) FROM tbl;
SELECT 'With GROUP BY:' _;
SELECT FIRST(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(value INTEGER, gr_name TEXT);

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

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

INSERT INTO tbl (value, gr_name) VALUES (5, 'gr_one'),
(null, 'gr_one'),
(null, 'gr_two'),
(null, 'gr_two');
SELECT LAST(value) FROM tbl WHERE 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(value INTEGER, gr_name TEXT);

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

SELECT MAX(value) FROM tbl;
SELECT 'With GROUP BY:' _;
SELECT MAX(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(value INTEGER, gr_name TEXT);

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

SELECT MIN(value) FROM tbl;
SELECT 'With GROUP BY:' _;
SELECT MIN(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(value INTEGER, gr_name TEXT);

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

SELECT SUM(value) sum_all, SUM(DISTINCT value) sum_distinct FROM tbl;
SELECT 'With GROUP BY:' _;
SELECT SUM(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:

#### 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:

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 current UTC time in ISO format.

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

#### DATE

Extracts date part from datetime/TIMESTAMP

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 timestamp based on the local timezone.

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 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:

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}``

#### 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 function takes several parameters and returns 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 logarithm
``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}``

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

Syntax
``RADIANS ( x )``
Parameters
`x`

Numeral to convert.

``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:

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. 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"}
{"_":"2. Insert using SELECT capturing groups"}
{"_id":1,"AuthMethod":"sms_code","AccountName":"linda","group_1":"38376"}
{"_":"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":"group_1: password; AccountName: linda; SourceIPAddress: 192.168.1.60; Port: 38176; Protocol: ssh2; group_2: 1"}
{"regexp_extract":"AuthMethod: sms_code"}
{"regexp_extract":"AccountName: john"}
{"_":"2. Insert using SELECT capturing groups"}
{"_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":5,"regexp_extract":"AccountName: john"}
{"_":"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 always returns an empty string.:

``SUBSTRING(str, 0, len)``
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

`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 zeros 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`

`PL`

`SG`

`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 shows some examples of how to use the `to_char` function.

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 be converted to 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 DOUBLE BOOL STRING DATETIME INT

+

+

+

+

+

-

BIGINT

+

+

+

+

+

+

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

Operators are reserved characters that perform an operation on one or more expressions. Use them for specifying conditions or conjunctions in SQL statements.

Raijin has the following types of operators:

### Arithmetic operators

This table contains the list of arithmetic operators supported by Raijin.

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

`+`

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

### Bitwise operators

The following table lists the bitwise operators supported by Raijin.

Operator Name Operand type Return type

`&`

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#

### Comparison operators

The following table lists the comparison operators supported by Raijin.

Operator Name Operand type Return type

`=`

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

### Logical operators

The following table lists the logical operators supported by Raijin.

Operator Name Operand type Return type

`AND`

Logical AND

BOOLEAN

BOOLEAN

`OR`

Logical OR

BOOLEAN

BOOLEAN

`XOR`

Logical XOR#

BOOLEAN#

BOOLEAN#

`NOT`

Logical NOT

BOOLEAN

BOOLEAN

`~~`

Regex like

STRING

STRING

`!~~`

Regex not like

STRING

STRING

`~~*`

Regex like case insensitive

STRING

STRING

`!~~*`

Regex not like case insensitive

STRING

STRING

`LIKE`

Pattern based search, case-sensitive

STRING

BOOLEAN

`ILIKE`

Pattern based search, case-insensitive

STRING

BOOLEAN

`IN`

Search in a list of values

Any

BOOLEAN

#### LIKE

The `LIKE` operator is used in a `WHERE` clause to search for a pattern in a column. The `LIKE` operator is case-sensitive.

Two wildcards are supported, `%` represents zero or more characters, while `_` represents exactly one character.

Syntax
``LIKE 'expr'``
Parameters
`expr`

Search pattern to match.

#### ILIKE

The `ILIKE` operator is used in a `WHERE` clause to search for a pattern in a column. The `ILIKE` operator is not case-sensitive.

Two wildcards are supported, `%` represents zero or more characters, while `_` represents exactly one character.

Syntax
``ILIKE 'expr'``
Parameters
`expr`

Search pattern to match.

#### IN

The `IN` operator is used to match an expression’s result against a list of values. It is a shorthand for multiple `OR` conditions.

Syntax
``expr IN (value_1, value_2, ..., value_n)``

or

``expr IN (subquery)``
Parameters
`expr`

Value to match.

`value_1, value_2, …​, value_n`

List of values to match against. The `IN` operator returns `true` if any value in the list matches the expression.

`subquery`

A `SELECT` statement returning exactly one column. The `IN` operator returns `true` if any value in the result set produced by the subquery matches the expression.

Special care should be taken when multiple `IN` operators joined by an `AND` specify different value types to match against. This is because when the value lists contain variant data, the values are internally converted to a common data type for evaluation, depending on the value types specified. Therefore, the result might be different from what you expect.

Example 99. Multiple IN operators with variant data
``````DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;
USE testdb;

CREATE TABLE tbl();
INSERT INTO tbl(a) VALUES (-5);

SELECT * FROM tbl WHERE a IN (-5) AND a IN ('-5', '-2');

-- Returns an empty result even though you might expect -5 to be found``````

### Set operators

Raijin supports the following set operators:

#### UNION

The `UNION` operator is used to combine the results of multiple `SELECT` statements. It requires that:

• All `SELECT` statements have an equal number of columns

• The columns in each `SELECT` statement have similar data types

• The columns in each `SELECT` statement are specified in the same order

Syntax
``````SELECT col_1, col_2, ..., col_n FROM table1
UNION
SELECT col_1, col_2, ..., col_n FROM table2``````
Parameters
`col_1, col_2, …​, col_n`

The list of columns to retrieve.

`table`

Table from where to retrieve the records.

Example 100. Using UNION
``````DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;
USE testdb;

CREATE TABLE tbl1(a INTEGER, b INTEGER);
INSERT INTO tbl1(a, b) VALUES
(1, 10),
(2, 20),
(3, 30);

CREATE TABLE tbl2(a INTEGER, b INTEGER);
INSERT INTO tbl2(a, b) VALUES
(3, 30),
(4, 40),
(5, 50);

SELECT a, b FROM tbl1 UNION SELECT a, b FROM tbl2;
SELECT a, b, (a+b) AS sum FROM tbl1 UNION SELECT a, b, (a+b) FROM tbl2;``````
Result
``````{"a":1,"b":10}
{"a":2,"b":20}
{"a":3,"b":30}
{"a":4,"b":40}
{"a":5,"b":50}
{}
{"a":1,"b":10,"sum":11}
{"a":2,"b":20,"sum":22}
{"a":3,"b":30,"sum":33}
{"a":4,"b":40,"sum":44}
{"a":5,"b":50,"sum":55}``````

#### UNION ALL

The `UNION ALL` operator combines the results of multiple `SELECT` statements, including duplicate rows.

Syntax
``````SELECT col_1, col_2, ..., col_n FROM table1
UNION ALL
SELECT col_1, col_2, ..., col_n FROM table2``````
Usage notes

See the UNION operator description.

Example 101. Using UNION ALL
``````DROP DATABASE IF EXISTS testdb;
CREATE DATABASE testdb;
USE testdb;

CREATE TABLE tbl1(a INTEGER, b INTEGER);
INSERT INTO tbl1(a, b) VALUES
(1, 10),
(2, 20),
(3, 30);

CREATE TABLE tbl2(a INTEGER, b INTEGER);
INSERT INTO tbl2(a, b) VALUES
(3, 30),
(4, 40),
(5, 50);

SELECT a, b FROM tbl1 UNION ALL SELECT a, b FROM tbl2;
SELECT a, b, (a+b) AS sum FROM tbl1 UNION ALL SELECT a, b, (a+b) FROM tbl2;``````
Result
``````{"a":1,"b":10}
{"a":2,"b":20}
{"a":3,"b":30}
{"a":3,"b":30}
{"a":4,"b":40}
{"a":5,"b":50}
{}
{"a":1,"b":10,"sum":11}
{"a":2,"b":20,"sum":22}
{"a":3,"b":30,"sum":33}
{"a":3,"b":30,"sum":33}
{"a":4,"b":40,"sum":44}
{"a":5,"b":50,"sum":55}``````

### String operators

The following table lists the string operators supported by Raijin.

Operator Name Operand type Return type

`|`

Concatenate

Any

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 interpreted 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 operator description.