Built-in Functions(2)

SERIESNUM

Returns the group to which the record belongs in the result set generated by SERIES BY. The return value is of type BIGINT, and always returns 1 if the SERIES BY clause is not used.

CREATE TABLE T1 (C1 INTEGER, C2 INTEGER);
Created successfully.
INSERT INTO T1 VALUES (0, 1);
1 row(s) inserted.
INSERT INTO T1 VALUES (1, 2);
1 row(s) inserted.
INSERT INTO T1 VALUES (2, 3);
1 row(s) inserted.
INSERT INTO T1 VALUES (3, 2);
1 row(s) inserted.
INSERT INTO T1 VALUES (4, 1);
1 row(s) inserted.
INSERT INTO T1 VALUES (5, 2);
1 row(s) inserted.
INSERT INTO T1 VALUES (6, 3);
1 row(s) inserted.
INSERT INTO T1 VALUES (7, 1);
1 row(s) inserted.


SELECT SERIESNUM(), C1, C2 FROM T1 ORDER BY C1 SERIES BY C2 > 1;
SERIESNUM() C1 C2
-------------------------------------------------
1 1 2
1 2 3
1 3 2
2 5 2
2 6 3
SYSDATE, NOW | 663
[5] row(s) selected.

STDDEV and STDDEV_POP

These functions return standard deviation of the given column values. STDDEV returns the standard deviation of the sample; STDDEV_POP returns the standard deviation of the population. The values are the square root of the values of VARIANCE and VAR_POP respectively.

Syntax:

DDEV(column)
STDDEV_POP(column)

Example:

Mach> CREATE TABLE stddev_table(c1 INTEGER, C2 DOUBLE);

Mach> INSERT INTO stddev_table VALUES (1, 1);
1 row(s) inserted.

Mach> INSERT INTO stddev_table VALUES (2, 1);
1 row(s) inserted.

Mach> INSERT INTO stddev_table VALUES (3, 2);
1 row(s) inserted.

Mach> INSERT INTO stddev_table VALUES (4, 2);
1 row(s) inserted.

Mach> SELECT c2, STDDEV(c1) FROM stddev_table GROUP BY c2;
c2                          STDDEV(c1)
...........................................................
1                           0.57735
2                           0.57735
[2] row(s) selected.

Mach> SELECT c2, STDDEV_POP(c1) FROM stddev_table GROUP BY c2;
c2                          STDDEV_POP(c1)
...........................................................
1                           0.5
2                           0.5
[2] row(s) selected.

SUBSTR

​This function cuts the data of a variable string column from the START for the specified SIZE.

Syntax:

SELECT SUBSTRING(column_name,start,length) AS some_name FROM table_name;

Example:

Mach> CREATE TABLE substr_table (c1 VARCHAR(10));
Created successfully.

Mach> INSERT INTO substr_table values('ABCDEFG');
1 row(s) inserted.

Mach> INSERT INTO substr_table values('abstract');
1 row(s) inserted.

Mach> SELECT SUBSTR(c1, 1, 1) FROM substr_table;
SUBSTR(c1, 1, 1)
....................
a
A
[2] row(s) selected.

Mach> SELECT SUBSTR(c1, 3, 3) FROM substr_table;
SUBSTR(c1, 3, 3)
....................
str
CDE
[2] row(s) selected.

Mach> SELECT SUBSTR(c1, 2) FROM substr_table;
SUBSTR(c1, 2)
.................
bstract
BCDEFG
[2] row(s) selected.

Mach> drop table substr_table;
Dropped successfully.

Mach> CREATE TABLE substr_table (c1 VARCHAR(10));
Created successfully.

Mach> INSERT INTO substr_table values('ABCDEFG');
1 row(s) inserted.

Mach> SELECT SUBSTR(c1, 1, 1) FROM substr_table;
SUBSTR(c1, 1, 1)
....................
A
[1] row(s) selected.

Mach> SELECT SUBSTR(c1, 3, 3) FROM substr_table;
SUBSTR(c1, 3, 3)
....................
CDE
[1] row(s) selected.

Mach> SELECT SUBSTR(c1, 2) FROM substr_table;
SUBSTR(c1, 2)
.................
BCDEFG
[1] row(s) selected.

The value of START starts from 1. If the value is set to 0, it returns NULL. If the size is not specified, it's internally designated as big as the string size. If the value of SIZE exceeds the length of the input string, SUBSTR returns the maximum length of the string.

SUBSTRING_INDEX(expression, delim, count)

It returns duplicated character strings until it finds the inserted numbers which was specified by the given delimiter. If the number is negative value, it evaluates the inserted character string and returns the rest of the string from the point where it finds the delimiter.

Syntax:

SUBSTRING_INDEX (expression, delim, count)

Example:

Mach> CREATE TABLE substring_table (url VARCHAR(30));
Created successfully.

Mach> INSERT INTO substring_table VALUES('www.machbase.com');
1 row(s) inserted.

Mach> SELECT SUBSTRING_INDEX(url, '.', 1) FROM substring_table;
SUBSTRING_INDEX(url, '.', 1)
..................................
www
[1] row(s) selected.

Mach> SELECT SUBSTRING_INDEX(url, '.', 2) FROM substring_table;
SUBSTRING_INDEX(url, '.', 2)
..................................
www.machbase
[1] row(s) selected.

Mach> SELECT SUBSTRING_INDEX(url, '.', .1) FROM substring_table;
SUBSTRING_INDEX(url, '.', .1)
..................................
com
[1] row(s) selected.

Mach> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(url, '.', 2), '.', .1) FROM substring_table;
SUBSTRING_INDEX(SUBSTRING_INDEX(url, '.
...........................................
machbase
[1] row(s) selected.

Mach> SELECT SUBSTRING_INDEX(url, '.', 0) FROM substring_table;
SUBSTRING_INDEX(url, '.', 0)
..................................
NULL
[1] row(s) selected.

Mach>

If the count is set to 0 or there is no delimiter in the string, it returns NULL.

SUM(column)

It is an aggregate function, and returns the total sum of a numeric column.

Syntax:

SELECT SUM(column_name) FROM table_name;

Example:

Mach> CREATE TABLE sum_table (c1 INTEGER, c2 INTEGER);
Created successfully.

Mach> INSERT INTO sum_table VALUES(1, 1);
1 row(s) inserted.

Mach> INSERT INTO sum_table VALUES(1, 2);
1 row(s) inserted.

Mach> INSERT INTO sum_table VALUES(1, 3);
1 row(s) inserted.

Mach> INSERT INTO sum_table VALUES(2, 1);
1 row(s) inserted.

Mach> INSERT INTO sum_table VALUES(2, 2);
1 row(s) inserted.

Mach> INSERT INTO sum_table VALUES(2, 3);
1 row(s) inserted.

Mach> INSERT INTO sum_table VALUES(3, 4);
1 row(s) inserted.

Mach> SELECT c1, SUM(c1) from sum_table group by c1;
c1          SUM(c1)
....................................
2           6
3           3
1           3
[3] row(s) selected.

Mach> SELECT c1, SUM(c2) from sum_table group by c1;
c1          SUM(c2)
....................................
2           6
3           4
1           6
[3] row(s) selected.

SYSDATE, NOW

SYSDATE returns the current time of the system. NOW is exactly the same as SYSDATE. (This function is added for the convenience of the user.)

Example

SELECT SYSDATE, NOW FROM t1;

sysdate                         now
-------------------------------------------------------------------
2017-01-16 14:14:53 310:973:000 2017-01-16 14:14:53 310:973:000

TO_CHAR(datatype, type_format_string)

It converts a given data type into a string. It can specify "format_string" based on data types. It doesn't support binary type.

DATETIME

Example:

Mach> CREATE TABLE datetime_table (id integer, dt datetime);
Created successfully.

Mach> INSERT INTO  datetime_table values(1, TO_DATE('1999.11.11 1:2:3 4:5:6'));
1 row(s) inserted.

Mach> INSERT INTO  datetime_table values(2, TO_DATE('2012.11.11 1:2:3 4:5:6'));
1 row(s) inserted.

Mach> INSERT INTO  datetime_table values(3, TO_DATE('2013.11.11 1:2:3 4:5:6'));
1 row(s) inserted.

Mach> INSERT INTO  datetime_table values(4, TO_DATE('2014.12.30 11:22:33 444:555:666'));
1 row(s) inserted.

Mach> SELECT id, dt FROM datetime_table WHERE dt > TO_DATE('2000.11.11 1:2:3 4:5:0');
id          dt
...............................................
4           2014.12.30 11:22:33 444:555:666
3           2013.11.11 01:02:03 004:005:006
2           2012.11.11 01:02:03 004:005:006
[3] row(s) selected.

Mach> SELECT id, dt FROM datetime_table WHERE dt > TO_DATE('2013.11.11 1:2:3') and dt < TO_DATE('2014.11.11 1:2:3');
id          dt
...............................................
3           2013.11.11 01:02:03 004:005:006
[1] row(s) selected.

Mach> SELECT id, TO_CHAR(dt) FROM datetime_table;
id          TO_CHAR(dt)
.................................................................................................
4           2014.12.30 11:22:33 444:555:666
3           2013.11.11 01:02:03 004:005:006
2           2012.11.11 01:02:03 004:005:006
1           1999.11.11 01:02:03 004:005:006
[4] row(s) selected.

Mach> SELECT id, TO_CHAR(dt, 'YYYY') FROM datetime_table;
id          TO_CHAR(dt, 'YYYY')
.................................................................................................
4           2014
3           2013
2           2012
1           1999
[4] row(s) selected.

Mach> SELECT id, TO_CHAR(dt, 'YYYY.MM') FROM datetime_table;
id          TO_CHAR(dt, 'YYYY.MM')
.................................................................................................
4           2014.12
3           2013.11
2           2012.11
1           1999.11
[4] row(s) selected.

Mach> SELECT id, TO_CHAR(dt, 'YYYY.MM.DD') FROM datetime_table;
id          TO_CHAR(dt, 'YYYY.MM.DD')
.................................................................................................
4           2014.12.30
3           2013.11.11
2           2012.11.11
1           1999.11.11
[4] row(s) selected.

Mach> SELECT id, TO_CHAR(dt, 'YYYY.MM.DD TO_CHAR') FROM datetime_table;
id          TO_CHAR(dt, 'YYYY.MM.DD TO_CHAR')
.................................................................................................
4           2014.12.30 TO_CHAR
3           2013.11.11 TO_CHAR
2           2012.11.11 TO_CHAR
1           1999.11.11 TO_CHAR
[4] row(s) selected.

Mach> SELECT id, TO_CHAR(dt, 'YYYY.MM.DD HH24:MI:SS') FROM datetime_table;
id          TO_CHAR(dt, 'YYYY.MM.DD HH24:MI:SS')
.................................................................................................
4           2014.12.30 11:22:33
3           2013.11.11 01:02:03
2           2012.11.11 01:02:03
1           1999.11.11 01:02:03
[4] row(s) selected.

Mach> SELECT id, TO_CHAR(dt, 'YYYY.MM.DD HH24:MI:SS mmm.uuu.nnn') FROM datetime_table;
id          TO_CHAR(dt, 'YYYY.MM.DD HH24:MI:SS mmm.
.................................................................................................
4           2014.12.30 11:22:33 444.555.666
3           2013.11.11 01:02:03 004.005.006
2           2012.11.11 01:02:03 004.005.006
1           1999.11.11 01:02:03 004.005.006
[4] row(s) selected.

It converts the value of datetime column to the specified string. "Datetime" function allows you to make or combine strings in various format. If format_string is omitted, the default is "YYYY.MM.DD HH24:MI:SS mmm:uuu:nnn".

Table 1. Format String

Format of expression Description
YYYY The year as a four.digit number.
MM The month as a two.digit number.
MON The abbreviated name of the month in three letters in Alphabet.
DD The day as a two.digit number.
HH The hour as a two.digit number.
HH12 The hour, using a 12.hour clock from 01 to 12.
HH24 The hour, using a 24.hour clock from 00 to 23.
HH2, HH3, HH6 The hour as a two.digit number, but will be categorized as the time by the numbers after HH. In other words, if you use HH6, 0 represents from 0'o clock to 5'o clock and 6 represents from 6'o clock to 12'o clock. This method of representation is useful when you calculate time series data based on the specified time. This value is represented based on 24 hours.
MI The minute as a two.digit number.
MI2, MI5, MI10, MI20, MI30 The minute as a two.digit number, but be categorized as the time by the numbers after MI. In other words, if you use MI30, 0 represents from 0 min to 29 min, and 30 numbers from 30 min to 59 min. The method of representation is useful when you calculate time series data based on the specified time.
SS The second as a two.digit number.
SS2, SS5, SS10, SS20, SS30 The second as a two.digit number, but be categorized as the time by the numbers after SS. In other words, if you are using SS30, 0 represents from 0 sec to 29 sec, and 30 represents from 30 sec to 59 sec. The method of representation is useful when you calculate time series data based on the specified time.
AM Based on the current time, it is represented as AM or PM.
mmm The millisecond as a three.digit number. The range of values is 000 to 999.
uuu The micro second as a three.digit number. The range of values is 000 to 999.
nnn The nano second as a three.digit number. The range of values is 000 to 999.

Fixed Type (short, integer, long, float, double, IPv4, and IPv6)

Example:

Mach> CREATE TABLE fixed_table (id1 SHORT, id2 INTEGER, id3 LONG, id4 FLOAT, id5 DOUBLE, id6 IPV4, id7 IPV6, id8 VARCHAR (128));
Created successfully.

Mach> INSERT INTO fixed_table values(200, 19234, 1234123412, 3.14, 7.8338, '192.168.0.1', '::127.0.0.1', 'log varchar');
1 row(s) inserted.

Mach> SELECT '[ ' || TO_CHAR(id1) || ' ]' FROM fixed_table;
'[ ' || TO_CHAR(id1) || ' ]'
....................................................................................
[ 200 ]
[1] row(s) selected.

Mach> SELECT '[ ' || TO_CHAR(id2) || ' ]' FROM fixed_table;
'[ ' || TO_CHAR(id2) || ' ]'
....................................................................................
[ 19234 ]
[1] row(s) selected.

Mach> SELECT '[ ' || TO_CHAR(id3) || ' ]' FROM fixed_table;
'[ ' || TO_CHAR(id3) || ' ]'
....................................................................................
[ 1234123412 ]
[1] row(s) selected.

Mach> SELECT '[ ' || TO_CHAR(id4) || ' ]' FROM fixed_table;
'[ ' || TO_CHAR(id4) || ' ]'
....................................................................................
[ 3.140000 ]
[1] row(s) selected.

Mach> SELECT '[ ' || TO_CHAR(id5) || ' ]' FROM fixed_table;
'[ ' || TO_CHAR(id5) || ' ]'
....................................................................................
[ 7.833800 ]
[1] row(s) selected.

Mach> SELECT '[ ' || TO_CHAR(id6) || ' ]' FROM fixed_table;
'[ ' || TO_CHAR(id6) || ' ]'
....................................................................................
[ 192.168.0.1 ]
[1] row(s) selected.

Mach> SELECT '[ ' || TO_CHAR(id7) || ' ]' FROM fixed_table;
'[ ' || TO_CHAR(id7) || ' ]'
....................................................................................
[ 0000:0000:0000:0000:0000:0000:7F00:0001 ]
[1] row(s) selected.

Mach> SELECT '[ ' || TO_CHAR(id8) || ' ]' FROM fixed_table;
'[ ' || TO_CHAR(id8) || ' ]'
....................................................................................
[ log varchar ]
[1] row(s) selected.

For numeric types, the value of each number will be converted into string type.

Not Supported (binary)

Binary type doesn't support TO.CHAR because it is impossible to convert it into texts. If you want to display it on the screen, you can check it displaying a hexadecimal value through TO_HEX() function.

TO_DATE(date_string, format_string)

This function converts a character string represented in format string into datetime type. When the format_string is omitted, the default is "YYYY.MM.DD HH24:MI:SS mmm:uuu:nnn".

Example:

Mach> CREATE TABLE to_date_table (id INTEGER, dt datetime);
Created successfully.

Mach> INSERT INTO  to_date_table VALUES(1, TO_DATE('1999.11.11 1:2:3 4:5:6'));
1 row(s) inserted.

Mach> INSERT INTO  to_date_table VALUES(2, TO_DATE('2012.11.11 1:2:3 4:5:6'));
1 row(s) inserted.

Mach> INSERT INTO  to_date_table VALUES(3, TO_DATE('2014.12.30 11:22:33 444:555:666'));
1 row(s) inserted.

Mach> INSERT INTO  to_date_table VALUES(4, TO_DATE('2014.DEC.30 23:22:34 777:888:999', 'YYYY.MON.DD HH24:MI:SS 1 row(s) inserted.

Mach> SELECT id, dt FROM to_date_table WHERE dt > TO_DATE('1999.11.11 1:2:3 4:5:0');
id          dt
...............................................
4           2014.12.30 23:22:34 777:888:999
3           2014.12.30 11:22:33 444:555:666
2           2012.11.11 01:02:03 004:005:006
1           1999.11.11 01:02:03 004:005:006
[4] row(s) selected.

Mach> SELECT id, dt FROM to_date_table WHERE dt > TO_DATE('2000.11.11 1:2:3 4:5:0');
id          dt
...............................................
4           2014.12.30 23:22:34 777:888:999
3           2014.12.30 11:22:33 444:555:666
2           2012.11.11 01:02:03 004:005:006
[3] row(s) selected.

Mach> SELECT id, dt FROM to_date_table WHERE dt > TO_DATE('2012.11.11 1:2:3','YYYY.MM.DD HH24:MI:SS') and dt < TO_DATE('2014.11.11 1:2:3','YYYY.MM.DD HH24:MI:SS');
id          dt
...............................................
2           2012.11.11 01:02:03 004:005:006
[1] row(s) selected.

Mach> SELECT id, TO_DATE('1999', 'YYYY') FROM to_date_table LIMIT 1;
id          TO_DATE('1999', 'YYYY')
...............................................
4           1999.01.01 00:00:00 000:000:000
[1] row(s) selected.

Mach> SELECT id, TO_DATE('1999.12', 'YYYY.MM') FROM to_date_table LIMIT 1;
id          TO_DATE('1999.12', 'YYYY.MM')
...............................................
4           1999.12.01 00:00:00 000:000:000
[1] row(s) selected.

Mach> SELECT id, TO_DATE('1999', 'YYYY') FROM to_date_table LIMIT 1;
id          TO_DATE('1999', 'YYYY')
...............................................
4           1999.01.01 00:00:00 000:000:000
[1] row(s) selected.

Mach> SELECT id, TO_DATE('1999.12', 'YYYY.MM') FROM to_date_table LIMIT 1;
id          TO_DATE('1999.12', 'YYYY.MM')
...............................................
4           1999.12.01 00:00:00 000:000:000
[1] row(s) selected.

Mach> SELECT id, TO_DATE('1999.12.31 13:12', 'YYYY.MM.DD HH24:MI') FROM to_date_table LIMIT 1;
id          TO_DATE('1999.12.31 13:12', 'YYYY.MM.DD
.......................................................
4           1999.12.31 13:12:00 000:000:000
[1] row(s) selected.

Mach> SELECT id, TO_DATE('1999.12.31 13:12:32', 'YYYY.MM.DD HH24:MI:SS') FROM to_date_table LIMIT 1;
id          TO_DATE('1999.12.31 13:12:32', 'YYYY.MM
.......................................................
4           1999.12.31 13:12:32 000:000:000
[1] row(s) selected.

Mach> SELECT id, TO_DATE('1999.12.31 13:12:32 123', 'YYYY.MM.DD HH24:MI:SS mmm') FROM to_date_table LIMIT 1;
id          TO_DATE('1999.12.31 13:12:32 123', 'YYY
.......................................................
4           1999.12.31 13:12:32 123:000:000
[1] row(s) selected.

Mach> SELECT id, TO_DATE('1999.12.31 13:12:32 123:456', 'YYYY.MM.DD HH24:MI:SS mmm:uuu') FROM to_date_table LIMIT 1;
id          TO_DATE('1999.12.31 13:12:32 123:456',
.......................................................
4           1999.12.31 13:12:32 123:456:000
[1] row(s) selected.

Mach> SELECT id, TO_DATE('1999.12.31 13:12:32 123:456:789', 'YYYY.MM.DD HH24:MI:SS mmm:uuu:nnn') FROM to_date_table LIMIT 1;
id          TO_DATE('1999.12.31 13:12:32 123:456:78
.......................................................
4           1999.12.31 13:12:32 123:456:789
[1] row(s) selected.

TO_DATE_SAFE

Like the TO_DATE function, it converts a string string represented by a given format string to a DATETIME type. If the conversion fails, NULL is returned.

Syntax:

TO_DATE_SAFE(date_string [, format_string])

Example:

Mach> CREATE TABLE sqltest (ts DATETIME);
Created successfully.

Mach> INSERT INTO date_table VALUES (TO_DATE_SAFE('2016-01-01', 'YYYY-MM-DD'));
1 row(s) inserted.
Mach> INSERT INTO date_table VALUES (TO_DATE_SAFE('2016-01-02', 'YYYY'));
1 row(s) inserted.
Mach> INSERT INTO date_table VALUES (TO_DATE_SAFE('2016-12-32', 'YYYY-MM-DD'));
1 row(s) inserted.

Mach> SELECT ts FROM date_table;
ts
----------------------------------
NULL
NULL
2016-01-01 00:00:00 000:000:000
[3] row(s) selected.

TO_HEX(column)

This function returns the hexadecimal value of the column. If the column value is NULL, TO_HEX returns its value. If not, TO_HEX returns the value of the column. To ensure consistency of the output, it converts into BIG ENDIAN format and displays for short, int, and long types.

Example:

Mach> CREATE TABLE hex_table (id1 SHORT, id2 INTEGER, id3 VARCHAR(10), id4 FLOAT, id5 DOUBLE, id6 LONG, id7 IPV4, id8 IPV6, id9 TEXT, id10 BINARY, 
id11 DATETIME);
Created successfully.

Mach> INSERT INTO hex_table VALUES(256, 65535, '0123456789', 3.141592, 1024 * 1024 * 1024 * 3.14, 13513135446, '192.168.0.1', '::192.168.0.1', 'textext', 
'binary', TO_DATE('1999', 'YYYY'));
1 row(s) inserted.

Mach> SELECT TO_HEX(id1), TO_HEX(id2), TO_HEX(id3), TO_HEX(id4), TO_HEX(id5), TO_HEX(id6), TO_HEX(id7), TO_HEX(id8), TO_HEX(id9), TO_HEX(id10), TO_HEX(id11) 
FROM hex_table;
TO_HEX(id1)  TO_HEX(id2)  TO_HEX(id3)            TO_HEX(id4)  TO_HEX(id5)        TO_HEX(id6)        TO_HEX(id7)
.........................................................................................................................
TO_HEX(id8)                          TO_HEX(id9)
..........................................................................................................................
TO_HEX(id10)                                                                      TO_HEX(id11)
........................................................................................................
0100   0000FFFF   30313233343536373839   D80F4940   1F85EB51B81EE941   0000000325721556   04C0A80001
06000000000000000000000000C0A80001   74657874657874
62696E617279                                                                      0CB325846E226000
[1] row(s) selected.

TO_IPV4(column) and TO_IPV4_SAFE(column)

These functions convert a given string into IPv4. If the string cannot be converted into numeric, TO_IPV4() returns an error and terminates the operation. For TO_IPV4_SAFE() function, it returns NULL and continues the operation.

Example:

Mach> CREATE TABLE ipv4_table (c1 varchar(100));
Created successfully.

Mach> INSERT INTO ipv4_table VALUES('192.168.0.1');
1 row(s) inserted.

Mach> INSERT INTO ipv4_table VALUES('     192.168.0.2    ');
1 row(s) inserted.

Mach> INSERT INTO ipv4_table VALUES(NULL);
1 row(s) inserted.

Mach> SELECT c1 FROM ipv4_table;
c1
....................................................................................
NULL
     192.168.0.2
192.168.0.1
[3] row(s) selected.

Mach> SELECT TO_IPV4(c1) FROM ipv4_table;
TO_IPV4(c1)
..................
NULL
192.168.0.2
192.168.0.1
[3] row(s) selected.

Mach> INSERT INTO ipv4_table VALUES('192.168.0.1.1');
1 row(s) inserted.

Mach> SELECT TO_IPV4(c1) FROM ipv4_table limit 1;
TO_IPV4(c1)
..................
[ERR.02068 : Invalid IPv4 address format (192.168.0.1.1).]
[0] row(s) selected.

Mach> SELECT TO_IPV4_SAFE(c1) FROM ipv4_table;
TO_IPV4_SAFE(c1)
...................
NULL
NULL
192.168.0.2
192.168.0.1
[4] row(s) selected.

TO_IPV6(column) and TO_IPV6_SAFE(column)

These functions convert a given string into IPv6. If the string cannot be converted to numeric, TO_IPV6() returns an error and terminates the operation. For TO_IPV6_SAFE() function, it returns NULL and continues the operation.

Example:

Mach> CREATE TABLE ipv6_table (id varchar(100));
Created successfully.

Mach> INSERT INTO ipv6_table VALUES('::0.0.0.0');
1 row(s) inserted.

Mach> INSERT INTO ipv6_table VALUES('::127.0.0.1');
1 row(s) inserted.

Mach> INSERT INTO ipv6_table VALUES('::127.0' || '.0.2');
1 row(s) inserted.

Mach> INSERT INTO ipv6_table VALUES('   ::127.0.0.3');
1 row(s) inserted.

Mach> INSERT INTO ipv6_table VALUES('::127.0.0.4  ');
1 row(s) inserted.

Mach> INSERT INTO ipv6_table VALUES('   ::FFFF:255.255.255.255   ');
1 row(s) inserted.

Mach> INSERT INTO ipv6_table VALUES('21DA:D3:0:2F3B:2AA:FF:FE28:9C5A');
1 row(s) inserted.

Mach> SELECT TO_IPV6(id) FROM ipv6_table;
TO_IPV6(id)
...............................................................
21da:d3::2f3b:2aa:ff:fe28:9c5a
::ffff:255.255.255.255
::127.0.0.4
::127.0.0.3
::127.0.0.2
::127.0.0.1
::
[7] row(s) selected.

Mach> INSERT INTO ipv6_table VALUES('127.0.0.10.10');
1 row(s) inserted.

Mach> SELECT TO_IPV6(id) FROM ipv6_table limit 1;
TO_IPV6(id)
...............................................................
[ERR.02148 : Invalid IPv6 address format.(127.0.0.10.10)]
[0] row(s) selected.

Mach> SELECT TO_IPV6_SAFE(id) FROM ipv6_table;
TO_IPV6_SAFE(id)
...............................................................
NULL
21da:d3::2f3b:2aa:ff:fe28:9c5a
::ffff:255.255.255.255
::127.0.0.4
::127.0.0.3
::127.0.0.2
::127.0.0.1
::
[8] row(s) selected.

TO_NUMBER(column) and TO_NUMBER_SAFE(column)

These functions convert the given string to the numeric double type. If the string cannot be converted into numeric, TO_NUMBER() function returns an error and terminates the operation. For TO_NUMBER_SAFE() function, it returns NULL and continues the operation.

Example:

Mach> CREATE TABLE number_table (id varchar(100));
Created successfully.

Mach> INSERT INTO number_table VALUES('10');
1 row(s) inserted.

Mach> INSERT INTO number_table VALUES('20');
1 row(s) inserted.

Mach> INSERT INTO number_table VALUES('30');
1 row(s) inserted.

Mach> SELECT TO_NUMBER(id) from number_table;
TO_NUMBER(id)
..............................
30
20
10
[3] row(s) selected.

Mach> CREATE TABLE safe_table (id varchar(100));
Created successfully.

Mach> INSERT INTO safe_table VALUES('invalidnumber');
1 row(s) inserted.

Mach> SELECT TO_NUMBER(id) from safe_table;
TO_NUMBER(id)
..............................
[ERR.02145 : The string cannot be converted to number value.(invalidnumber)]
[0] row(s) selected.

Mach> SELECT TO_NUMBER_SAFE(id) from safe_table;
TO_NUMBER_SAFE(id)
..............................
NULL
[1] row(s) selected.

TRUNC

​The TRUNC function returns number truncated to n decimal places. If n is omitted, then number is truncated to 0 places. n can be negative to truncate (make zero) n digits left of the decimal point.

Syntax:

TRUNC( number [, n] )

Example:

Mach> CREATE TABLE trunc_table (i1 DOUBLE);
Created successfully.

Mach> INSERT INTO trunc_table VALUES (158.799);
1 row(s) inserted.

Mach> SELECT TRUNC(i1, 1), TRUNC(i1, .1) FROM trunc_table;
TRUNC(i1, 1)                TRUNC(i1, .1)
...........................................................
158.7                       150
[1] row(s) selected.

Mach> SELECT TRUNC(i1, 2), TRUNC(i1, .2) FROM trunc_table;
TRUNC(i1, 2)                TRUNC(i1, .2)
...........................................................
158.79                      100
[1] row(s) selected.

TS_CHANGE_COUNT(column)

Example:

Mach> CREATE TABLE ipcount_table (id INTEGER, ip IPV4);
Created successfully.

Mach> INSERT INTO ipcount_table VALUES (1, '192.168.0.1');
1 row(s) inserted.

Mach> INSERT INTO ipcount_table VALUES (1, '192.168.0.2');
1 row(s) inserted.

Mach> INSERT INTO ipcount_table VALUES (1, '192.168.0.1');
1 row(s) inserted.

Mach> INSERT INTO ipcount_table VALUES (1, '192.168.0.2');
1 row(s) inserted.

Mach> INSERT INTO ipcount_table VALUES (2, '192.168.0.3');
1 row(s) inserted.

Mach> INSERT INTO ipcount_table VALUES (2, '192.168.0.3');
1 row(s) inserted.

Mach> INSERT INTO ipcount_table VALUES (2, '192.168.0.4');
1 row(s) inserted.

Mach> INSERT INTO ipcount_table VALUES (2, '192.168.0.4');
1 row(s) inserted.

Mach> SELECT id, TS_CHANGE_COUNT(ip) from ipcount_table GROUP BY id;
id          TS_CHANGE_COUNT(ip)
....................................
2           2
1           4
[2] row(s) selected.

It is an aggregate function and counts the number of times for changing the values of specified columns.To have accurate results, the row of data must be inserted in sequential order. Therefore, this function cannot be used with JOIN or INLINE VIEW. The current version supports all data types except varchar type.

UNIX_TIMESTAMP

UNIX_TIMESTAMP is a function that converts a date type value to a 32-bit integer value converted by the unix time () system call, while FROM_UNIXTIME is a function that converts integer type data to a date type value.

Example:

SELECT UNIX_TIMESTAMP('2001-01-01') FROM unix_table;
UNIX_TIMESTAMP('2001-01-01')
-------------------------------
978274800

UPPER(column)

This function converts the contents of the given column into uppercase.

Example:

Mach> CREATE TABLE upper_table(id INTEGER,name VARCHAR(10));
Created successfully.

Mach> INSERT INTO upper_table VALUES(1, '');
1 row(s) inserted.

Mach> INSERT INTO upper_table VALUES(2, 'James');
1 row(s) inserted.

Mach> INSERT INTO upper_table VALUES(3, 'sarah');
1 row(s) inserted.

Mach> INSERT INTO upper_table VALUES(4, 'THOMAS');
1 row(s) inserted.

Mach> SELECT id, UPPER(name) FROM upper_table;
id          UPPER(name)
............................
4           THOMAS
3           SARAH
2           JAMES
1           NULL
[4] row(s) selected.

VARIANCE and VAR_POP

The VARIANCE function returns the variance value of a numeric column. Variance function returns the variance of the sample and VAR_POP function returns the variance of the population.

Syntax:

VARIANCE (column_name)
VAR_POP (column_name)

Example:

Mach> CREATE TABLE var_table(c1 INTEGER, c2 DOUBLE);
Created successfully.

Mach> INSERT INTO var_table VALUES (1, 1);
1 row(s) inserted.

Mach> INSERT INTO var_table VALUES (2, 1);
1 row(s) inserted.

Mach> INSERT INTO var_table VALUES (1, 2);
1 row(s) inserted.

Mach> INSERT INTO var_table VALUES (2, 2);
1 row(s) inserted.

Mach> SELECT VARIANCE(c1) FROM var_table;
VARIANCE(c1)
..............................
0.333333
[1] row(s) selected.

Mach> SELECT VAR_POP(c1) FROM var_table;
VAR_POP(c1)
..............................
0.25
[1] row(s) selected.

YEAR, MONTH, DAY

YEAR (datetime_col), MONTH (datetime_col), and DAY (datetime_col) are functions that extract the corresponding year, month, and day from the input datetime column values and return them as integer type values.

Example:

CREATE TABLE extract_table(c1 DATETIME, c2 INTEGER);
Created successfully.

INSERT INTO extract_table VALUES (to_date('2001-01-01 12:30:00 000:000:000'), 1);
1 row(s) inserted.

SELECT YEAR(c1), MONTH(c1), DAY(c1) FROM extract_table;
year(c1)    month(c1)   day(c1)
----------------------------------------
2001        1           1

UNIX_TIMESTAMP

UNIX_TIMESTAMP is a function that converts a date type value to a 32-bit integer value converted by the unix time () system call, while FROM_UNIXTIME is a function that converts integer type data to a date type value.

Example:

SELECT UNIX_TIMESTAMP('2001-01-01') FROM unix_table;
UNIX_TIMESTAMP('2001-01-01')
-------------------------------
978274800

results matching ""

    No results matching ""