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.
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;{"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.
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;{"_":"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.
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;{"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.
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;{"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.
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;{"_":"..................................................................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.
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;{"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.
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;{"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.
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;{"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
conditionis evaluated astrue. otherwise-
Expression which value is evaluated and returned if
conditionis evaluated asfalse.
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;{"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
expressionvalue isNULL.
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":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
CASEexpression syntax (full specification)
CASE WHEN condition THEN result
[WHEN condition THEN result]
[...]
[ELSE result]
ENDEach 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
CASEexpression syntax (implicit argument)
CASE expression
WHEN value THEN result
[WHEN value THEN result]
[...]
[ELSE result]
ENDThe 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.
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;{"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.
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;{"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.
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;{"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:
| SQL | Result |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
CURRENT_DATE
Returns the current date formatted in the ISO format and UTC time.
- Syntax
CURRENT_DATE ()SELECT CURRENT_DATE();{"CURRENT_DATE":"2016-08-05"}CURRENT_TIME
Returns current UTC time in ISO format.
- Syntax
CURRENT_TIME ()SELECT CURRENT_TIME();{"CURRENT_TIME":"07:36:09"}DATE
Extracts date part from datetime/TIMESTAMP
- Syntax
DATE ( date )- Parameters
-
date-
Date to be formatted.
SELECT DATE('2016-08-04 17:14:04') _;{"_":"2016-08-04"}DATE_TRUNC
This function is used to truncate a timestamp with a given interval. The interval can be natural or arbitrary.
- Syntax
date_trunc('datepart', field)- Accepted values for natural time intervals
-
year
quarter
month
week
day
hour
minute
second - Accepted values for natural time intervals
-
The
DATE_TRUNCfunction also accepts arbitrary time intervals, ranging from a second to a week. For example, "10w" equals 10 weeks.w - weeks
d - days
h - hours
m - minutes
s - seconds
SELECT '2022-03-14T13:05:23.223456+00:00'::DATETIME;
SELECT DATE_TRUNC('day', '2022-03-14T13:05:23.223456+00:00'::DATETIME) as day;
SELECT DATE_TRUNC('hour', '2022-03-14T13:05:23.223456+00:00'::DATETIME) as hour;{"DATETIME":"2022-03-14T16:05:23.223456+03:00"}
{"day":"2022-03-14T03:00:00.000000+03:00"}
{"hour":"2022-03-14T16:00:00.000000+03:00"}| The date truncation is done in a UTC zone, which is why the 3 hours difference is in the above example. The machine’s time zone where the queries were executed is UTC+3. |
SELECT '2022-03-14T13:05:23.223456+00:00'::DATETIME;
SELECT DATE_TRUNC('15s', '2022-03-14T13:05:23.223456+00:00'::DATETIME) as arbitrary_truncated1;
SELECT DATE_TRUNC('65m', '2022-03-14T13:05:23.223456+00:00'::DATETIME) as arbitrary_truncated2;{"DATETIME":"2022-03-14T16:05:23.223456+03:00"}
{"arbitrary_truncated1":"2022-03-14T16:05:15.000000+03:00"}
{"arbitrary_truncated2":"2022-03-14T15:55:00.000000+03:00"}DAY
Extracts the day of month from the given date.
- Syntax
DAY ( date )- Parameters
-
date-
Date to extract the day of month from.
SELECT DAY('2016-08-04 17:14:04') _;{"_":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.
SELECT DAYOFWEEK('2016-08-04 17:14:04') _;{"_":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.
SELECT DAYOFYEAR('2016-08-04 17:14:04') _;{"_":217}HOUR
Extracts the hour value from the given date.
- Syntax
HOUR ( date )- Parameters
-
date-
Date to extract the hour value from.
SELECT HOUR(TIMESTAMP('2016-08-04 17:14:04')) _;{"_":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 ()SELECT LOCALTIME();{"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 ()SELECT LOCALTIMESTAMP();{"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.
SELECT MICROSECOND('2014-11-24 01:02:03.000005') _;{"_":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.
SELECT MINUTE(TIMESTAMP('2016-08-04 17:14:04')) _;{"_":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.
SELECT MONTH(TIMESTAMP('2016-08-04 17:14:04')) _;{"_":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.
SELECT FORMAT_UTC_USEC(MSEC_TO_TIMESTAMP(1470320865828)) _;{"_":"2016-08-04T14:27:45.828000Z"}NOW
Returns current date and time.
- Syntax
NOW ()SELECT NOW();{"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.
SELECT(PARSE_UTC_USEC('1470330844000000'));{"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.
SELECT QUARTER(TIMESTAMP('2016-08-04 17:14:04')) _;{"_":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.
SELECT FORMAT_UTC_USEC(SEC_TO_TIMESTAMP(1470320887)) _;{"_":"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.
SELECT SECOND(TIMESTAMP('2016-08-04 17:14:04')) _;{"_":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.
SELECT TIME('2016-08-04 17:14:04') _;{"_":"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.
SELECT TIMESTAMP_TO_MSEC(TIMESTAMP('2016-08-04 17:14:04Z')) _;{"_":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.
SELECT TIMESTAMP_TO_SEC(TIMESTAMP('2016-08-04 17:14:04Z')) _;{"_":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.
SELECT TIMESTAMP_TO_USEC(TIMESTAMP('2016-08-04 17:14:04Z')) _;{"_":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.
SELECT FORMAT_UTC_USEC(USEC_TO_TIMESTAMP(1470320844676510)) _;{"_":"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.
SELECT YEAR(TIMESTAMP('2016-08-04 17:14:04')) _;{"_":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.
SELECT FORMAT_UTC_USEC(TIMESTAMP('2016-08-04 17:14:04Z')) _;{"_":"2016-08-04T17:14:04.000000Z"}Math functions
Raijin supports the following math functions:
| SQL | Result |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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.
SELECT ABS(-1) ai, ABS(-4.35) ad, ABS('str');{"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
-1and1.
SELECT ACOS(0.5), PI()/3 angle;{"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.
SELECT ACOSH(11.5919), PI() angle;{"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
-1and1.
SELECT ASIN(1), PI()/2 angle;{"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.
SELECT ASINH(-11.5487), -PI() angle;{"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.
SELECT ATAN(1), PI()/4 angle;{"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.
SELECT ATAN2(4,2);{"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.
SELECT ATANH(0);{"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.
SELECT CEIL(4), TYPE(CEIL(4));
SELECT CEIL(4.35), TYPE(CEIL(4.35));{"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:
CEIL_TO_INT ( x )
- Parameters
-
x-
A numeric value.
SELECT CEIL_TO_INT(4), TYPE(CEIL_TO_INT(4));
SELECT CEIL_TO_INT(4.35), TYPE(CEIL_TO_INT(4.35));{"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.
SELECT COS(PI()/3);{"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.
SELECT COSH(PI()/3);{"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.
SELECT DEGREES(PI()/3);{"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.
SELECT EXP(-1);{"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.
SELECT FLOOR(5), TYPE(FLOOR(5));
SELECT FLOOR(5.75), TYPE(FLOOR(5.75));{"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.
SELECT FLOOR_TO_INT(5), TYPE(FLOOR_TO_INT(5));
SELECT FLOOR_TO_INT(5.75), TYPE(FLOOR_TO_INT(5.75));{"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.
SELECT GREATEST(4, 5, NULL, 8.4);{"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 the 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.
SELECT IS_EVEN(null) n, IS_EVEN(512);{"n":null,"is_even":true}IS_FINITE
- Syntax
IS_FINITE ( x )- Parameters
-
x-
Must be a numeric value. If this parameter is NULL, the NULL value will be returned.
SELECT 2/0 AS "2/0", IS_FINITE(2/0);
SELECT IS_FINITE(null), IS_FINITE(512) AS fin;{"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, theNULLvalue will be returned.
SELECT 2/0 AS "2/0", IS_INF(2/0);
SELECT IS_INF(null), IS_INF(512) AS inf;{"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.
SELECT IS_NAN(2/0) a, IS_NAN(0.2) b;{"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.
SELECT IS_NORMAL(3) "3", IS_NORMAL(0) "0";{"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.
SELECT IS_ODD(3) "3", IS_ODD(4) "4";{"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.
SELECT LEAST(4, 5, NULL, 8.4);{"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.
SELECT LN(15) "15", LN(-14) "-14";{"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.
SELECT LOG(3) "ln(3)", LOG(3, 10) "log(3)";
SELECT LOG2(3), LOG10(3);{"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.
SELECT LOG2(3), LOG10(3);{"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.
SELECT LOG2(3), LOG10(3);{"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.
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'));{"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"}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.
SELECT POWER(2, 8), POW(25, 0.5);{"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.
SELECT RADIANS(180);{"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.
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));{"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.
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));{"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.
SELECT SIN(RADIANS(90));{"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.
SELECT SINH(RADIANS(90));{"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.
SELECT SQRT(4) "positive", SQRT(-4) "negative";{"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.
SELECT TAN(PI()/4);{"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.
SELECT TANH(PI()/4);{"tanh":0.655794}String functions
Raijin supports the following string functions:
| SQL | Result |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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.
SELECT CONCAT('Foo ', 'bar ', null, 123) _;{"_":"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.
SELECT LEFT('Foo_bar', 4);{"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.
SELECT LENGTH('Foo_bar');
SELECT CHAR_LENGTH('Foo_bar');
SELECT CHARACTER_LENGTH('Foo_bar');{"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.
SELECT LOWER('FOOBAR');
SELECT LCASE('FOOBAR');{"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.
SELECT LTRIM(' Foo_bar');{"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
stringfrom the given position. pos-
Position where to place the
substituteparameter. len-
Optional parameter specifying the maximum length of the replacement.
SELECT OVERLAY('Foo_bar' PLACING '432' FROM 3);
SELECT OVERLAY('Foo_bar' PLACING '1234' FROM 3 FOR 3);{"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
substringshould be searched. substring-
A string which should be searched in the
string.
SELECT POSITION('bar' in 'Foo_bar');
SELECT POSITION('hello' in 'Foo_bar');{"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+)');{"group_1":"host"}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;{"group_1":"Some sentence with "}
{"group_1":"youcansay"}
{"group_1":"Yeah, "}
{"implicit":"Some sentence with hell"}
{"implicit":"youcansayhell"}
{"implicit":"Yeah, hell"}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;{"_":"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"}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;{"_":"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 expression.
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_MATCH('www.host.org', '[w]{3}\.(\\w+)');{"regexp_match":false}partial-
If set to
TRUE, this boolean parameter will call partial matching. By default, it is set toFALSE.
SELECT REGEXP_MATCH('www.host.org', '[w]{3}\.[\\w]+');
SELECT REGEXP_MATCH('www.host.org', '[w]{3}\.[\\w]+', true);{"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_MATCHwhen used with thepartialparameter set toTRUE. - 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+)');{"group_1":"host"}SELECT REGEXP_MATCH_PARTIAL('www.host.org', '[w]{3}\.[\\w]+');{"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
patternparameter. 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+)');{"group_1":"host"}substitute-
String which should replace with the matched substring.
SELECT REGEXP_REPLACE('www.host.org', '[\\w]', '*');
SELECT REGEXP_REPLACE('www.host.org', '\\.', '*');{"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
substringoccurrences. substring-
Substring to be searched in the
string. substitute-
Substring which should replace all occurrences of the
substringfound in thestring. If not given, allsubstringoccurrences are removed from the string.
SELECT REPLACE('war war war', 'war', 'peace');
SELECT REPLACE('war war war', 'war');{"replace":"peace peace peace"}
{"replace":" "}RIGHT
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.
SELECT RIGHT('Foo_bar', 4);{"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.
SELECT RTRIM('Foo_bar ');{"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
posand 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)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);{"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.
SELECT TO_STRING(-634.27);
SELECT STRING(true);{"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_charoutput 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.
| Pattern | Description |
|---|---|
|
hour of day (01-12) |
|
hour of day (01-12) |
|
hour of day (00-23) |
|
minute (00-59) |
|
second (00-59) |
|
millisecond (000-999) |
|
microsecond (000000-999999) |
|
seconds past midnight (0-86399) |
|
meridiem indicator (without periods) |
|
meridiem indicator (with periods) |
|
year (4 or more digits) with comma |
|
year (4 or more digits) |
|
last 3 digits of year |
|
last 2 digits of year |
|
last digit of year |
|
ISO 8601 week-numbering year (4 or more digits) |
|
last 3 digits of ISO 8601 week-numbering year |
|
last 2 digits of ISO 8601 week-numbering year |
|
last digit of ISO 8601 week-numbering year |
|
era indicator (without periods) |
|
era indicator (with periods) |
|
full upper case month name (blank-padded to 9 chars) |
|
full capitalized month name (blank-padded to 9 chars) |
|
full lower case month name (blank-padded to 9 chars) |
|
abbreviated upper case month name (3 chars in English, localized lengths vary) |
|
abbreviated capitalized month name (3 chars in English, localized lengths vary) |
|
abbreviated lower case month name (3 chars in English, localized lengths vary) |
|
month number (01-12) |
|
full upper case day name (blank-padded to 9 chars) |
|
full capitalized day name (blank-padded to 9 chars) |
|
full lower case day name (blank-padded to 9 chars) |
|
abbreviated upper case day name (3 chars in English, localized lengths vary) |
|
abbreviated capitalized day name (3 chars in English, localized lengths vary) |
|
abbreviated lower case day name (3 chars in English, localized lengths vary) |
|
day of year (001-366) |
|
day of ISO 8601 week-numbering year (001-371; day 1 of the year is Monday of the first ISO week) |
|
day of month (01-31) |
|
day of the week, Sunday (1) to Saturday (7) |
|
ISO 8601 day of the week, Monday (1) to Sunday (7) |
|
week of month (1-5) (the first week starts on the first day of the month) |
|
week number of year (1-53) (the first week starts on the first day of the year) |
|
week number of ISO 8601 week-numbering year (01-53; the first Thursday of the year is in week 1) |
|
century (2 digits) (the twenty-first century starts on 2001-01-01) |
|
Julian Day (integer days since November 24, 4714 BC at midnight UTC) |
|
quarter |
|
month in upper case Roman numerals (I-XII; I=January) |
|
month in lower case Roman numerals (i-xii; i=January) |
|
upper case time-zone abbreviation |
|
lower case time-zone abbreviation |
|
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.
| Modifier | Description | Example |
|---|---|---|
|
Fill mode (suppresses leading zeroes and padding blanks) |
|
|
Upper case ordinal number suffix |
|
|
Lower case ordinal number suffix |
|
|
Fixed format global option (see usage notes) |
|
|
Translation mode (print localized day and month names based on lc_time) |
|
Usage notes for date/time formatting:
-
FMsuppresses leading zeros and trailing blanks that would otherwise be added to make the output of a pattern be fixed-width. Like PostgreSQL, in RaijinFMmodifies only the next specification, while in OracleFMaffects all subsequent specifications, and repeatedFMmodifiers toggle fill mode on and off. -
TMdoes not include trailing blanks. -
Ordinary text is allowed in
to_chartemplates 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, theYYYYpart will be replaced by the year data, but the singleYinYearwill 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.
| Pattern | Description |
|---|---|
|
digit position (can be dropped if insignificant) |
|
digit position (will not be dropped, even if insignificant) |
|
decimal point |
|
group (thousands) separator |
|
negative value in angle brackets |
|
sign anchored to number (uses locale) |
|
currency symbol (uses locale) |
|
decimal point (uses locale) |
|
group separator (uses locale) |
|
minus sign in specified position (if number < 0) |
|
plus sign in specified position (if number > 0) |
|
plus/minus sign in specified position |
|
Roman numeral (input between 1 and 3999) |
|
ordinal number suffix |
|
shift specified number of digits (see notes) |
|
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, andGrepresent 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. IfSappears just to the left of9, it will likewise be anchored to the number. -
A sign formatted using
SG,PL, orMIis not anchored to the number. For example, usingto_char(-12, 'MI9999')produces- 12, but theto_char(-12, 'S9999')function call produces` -12. The Oracle implementation does not allow usingMIbefore9, it requires that9precedeMIinstead. -
THdoes not convert values less than zero and does not convert fractional numbers. -
Vwithto_charmultiplies the input values by10^n, wherenis the number of digits followingV. Theto_charfunction does not support the use ofVcombined with a decimal point, e.g. 99.9V99 is not allowed. -
The
EEEEscientific 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.99EEEEis a valid pattern. -
Certain modifiers can be applied to any template pattern to alter its behavior. For example,
FM99.99is the99.99pattern with theFMmodifier applied.
The table below shows the modifier patterns for numeric formatting.
| Modifier | Description | Example |
|---|---|---|
|
fill mode (suppress trailing zeroes and padding blanks) |
|
|
upper case ordinal number suffix |
|
|
lower case ordinal number suffix |
|
The following table shows some examples of how to use the to_char function.
| Expression | Result |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
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.
SELECT TRIM(' Foo_bar ');
SELECT BTRIM(' Foo_bar ');{"trim":"Foo_bar"}
{"btrim":"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,
NULLwill be returned.
Only allowed conversions are performed as per the following table.
| \ | INT | UNSIGNED INT | BIGINT | UNSIGNED BIGINT | DOUBLE | BOOL | STRING | TIMESTAMP |
|---|---|---|---|---|---|---|---|---|
INT |
+ |
+ |
+ |
+ |
+ |
+ |
+ |
- |
UNSIGNED INT |
+ |
+ |
+ |
+ |
+ |
+ |
+ |
- |
BIGINT |
+ |
+ |
+ |
+ |
+ |
+ |
+ |
+ |
UNSIGNED BIGINT |
+ |
+ |
+ |
+ |
+ |
+ |
+ |
+ |
DOUBLE |
+ |
+ |
+ |
+ |
+ |
+ |
+ |
+ |
BOOL |
+ |
+ |
+ |
+ |
+ |
+ |
+ |
- |
STRING |
+ |
+ |
+ |
+ |
+ |
+ |
+ |
+ |
TIMESTAMP |
- |
- |
+ |
+ |
+ |
- |
+ |
+ |
SELECT CAST('1.89' AS BIGINT);
SELECT CAST(1.89 AS STRING);
SELECT CAST(1 AS UNSIGNED BIGINT);
SELECT 1.89::DOUBLE;
SELECT 1.89::BOOL;
SELECT 1.89::BOOL::STRING;
SELECT 1::UNSIGNED INT;
SELECT '2016-08-04 17:14:04Z'::DATETIME::BIGINT;{"INT64":2}
{"STRING":"1.89"}
{"UNSIGNED BIGINT":1}
{"DOUBLE":1.89}
{"BOOL":true}
{"STRING":"TRUE"}
{"UNSIGNED INT":1}
{"INT64":1470330844000000}