Built-In Functions(1)

Machbase provides a number of built-in functions that are always available. The functions are listed below.

Table 1. Description of Built-In Functions

Name of Built-in functions Description
ABS(column) It is executed against numeric columns and returns the value of real type after converted into a positive number.
ADD_TIME(column,time_diff_format) This function performs addition operations (negative or positive values) for a given datetime column.
AVG(column) It is an aggregate function, and prints the average value of a numeric column.
BITAND and BITOR These functions are to execute BITAND and BITOR operations.
COUNT(column) It is an aggregate function, and returns the number of records for a given column
DATE_TRUNC DATE_TRUNC function truncates date_val based on field values.
DECODE(column, [search, return],.. default) It compares the given column value with the search value.
Fixed Type (short, integer, long, float, double, IPv4, and IPv6) For numeric types, the value of each number will be converted into string type.
FROM_UNIXTIME It is a function that converts integer type data to a date type value.
GROUP_CONCAT(column) It is an aggregate function, and it returns the values of the columns in concatenating strings in a group.
LEAST(value_list) and GREATEST(value_list) When it specifies various columns or values as input parameters, the functions return the least value or the greatest value.
LENGTH(column) It returns the length of a string column in bytes.
LOWER(column) It returns a character expression after converting a string of uppercase to lowercase.
LPAD(column, length, char) and RPAD It appends characters to the left (LPAD) or right (RPAD) until input values reach the given length.
LTRIM(col, patter) and RTRIM(col, pattern) In a first parameter, it removes characters that matches with the pattern string.
MAX(column) It returns the maximum value of a numeric column.
MIN(column) It returns the minimum value of a numeric column.
NVL(column, value) It returns the value if the value of the column is NULL.
ROUND The ROUND function returns number rounded to n places to the right of the decimal point.
ROWNUM() The function specifies a number to the row from query results.
SERIESNUM It returns the group which the record belongs to.
STDDEV(column) and STDDEV_POP(column) These functions return standard deviation of the given column values.
SUBSTR (column, start, size) This function cuts the data of a variable string column from the START for the specified SIZE.
SUBSTRING_INDEX(expression, delim, count) It returns duplicated character strings until it finds the inserted numbers which was specified by the given delimiter.
SUM(column) It is an aggregate function, and returns the total sum of a numeric column.
SYSDATE, NOW It returns the current time of the system.
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.
TO_DATE(date_string, format_string) This function converts a character string represented in format string into datetime type.
TO_DATE_SAFE It converts a string string represented by a given format string to a DATETIME type
TO_HEX(column) This function returns the hexadecimal value of the column.
TO_IPV4(column) and TO_IPV4_SAFE(column) These functions convert a given string into IPv4
TO_IPV6(column) and TO_IPV6_SAFE(column) These functions convert a given string into IPv6.
TO_NUMBER(column) and TO_NUMBER_SAFE(column) These functions convert the given string to the numeric double type.
TRUNC The TRUNC function returns number truncated to n decimal places.
TS_CHANGE_COUNT(column) It is an aggregate function and counts the number of times for changing the values of specified columns.
UNIX_TIMESTAMP, FROM_UNIXTIME It converts a date type value to a 32-bit integer value converted by the unix time () system call.
UPPER(column) This function converts the contents of the given column into uppercase.
VARIANCE(column) and VAR_POP(column) The VARIANCE function returns the variance value of a numeric column.
YEAR, MONTH, DAY Extract the corresponding year, month, and day from the input datetime column value and return it as an integer type value.

Table 2. Supported Types

Short Integer Long Float Double Varchar Text Ipv4 Ipv6 Datetime Binary
ABS o o o o o x x x x x x
ADD_TIME x x x x x x x x x o x
AVG o o o o o x x x x x x
BITAND and BITOR o o o x x x x x x x x
COUNT o o o o o o x o o o x
DATE_TRUNC x x x x x x x x x o x
DATETIME x x x x x x x x x o x
DECODE o o o o o o x o x o x
FROM_UNIXTIME x x x x x x x x x o x
GROUP_CONCAT(column) o o o o o o x o o o x
LEAST(value_list) and GREATEST(value_list) o o o o o o x x x x x
LENGTH x x x x x o o x x x o
LOWER x x x x x o x x x x x
LPAD(column, length, char) and RPAD x x x x x o x x x x x
LTRIM(col, patter) and RTRIM(col, pattern) x x x x x o x x x x x
MAX o o o o o o x o o o x
MIX o o o o o o x o o o x
NVL x x x x x o x o x x x
Round o o o o o x x x x x x
Rownum o o o o o o o o o o o
STDDEV(_POP) o o o o o x x x x x x
SUBSTR x x x x x o x x x x x
SUBSTRING_INDEX(expression, delim, count) x x x x x o o x x x x
SUM(column) o o o o o x x x x x x
SYSDATE, NOW x x x x x x x x x x x
TO_CHAR o o o o o o x o o o x
TO_DATE x x x x x o x x x x x
TO_HEX o o o o o o o o o o o
TO_IPV4(_SAFE) x x x x x o x x x x x
TO_IPV6(_SAFE) x x x x x o x x x x x
TO_NUMBER(_SAFE) x x x x x o x x x x x
TRUNC o o o o o x x x x x x
TS_CHANGE_COUNT(column) o o o o o x x o o o x
UNIX_TIMESTAMP o o o o o x x x x x x
UPPER(column) x x x x x o x x x x x
VARIANCE(_POP) o o o o o x x x x x x
YEAR, MONTH, DAY x x x x x x x x x o x

ABS

It is executed against numeric columns and returns the value of real type after converted into a positive number.

Syntax:

ABS(column_name), ABS(column_name),...

Example:

Mach> CREATE TABLE abs_table (c1 integer, c2 double, c3 varchar(10));
Created successfully.

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

Mach> INSERT INTO abs_table VALUES(2, 2.0, 'sqltest');
1 row(s) inserted.

Mach> INSERT INTO abs_table VALUES(3, 3.0, 'sqltest');
1 row(s) inserted.

Mach> SELECT ABS(c1), ABS(c2) from abs_table;
SELECT ABS(c1), ABS(c2) from abs_table;
ABS(c1)                     ABS(c2)
...........................................................
3                           3
2                           2
1                           1
[3] row(s) selected.

ADD_TIME

This function performs a date and time operation on a given datetime column.
It supports increment / decrement operation up to year, month, day, hour, minute, second, and does not support operations on milli, micro, and nanoseconds.

Syntax:

ADD_TIME(column,time_diff_format)

Example:

Mach> CREATE TABLE add_time_table (id INTEGER, dt DATETIME);
Created successfully.

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

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

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

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

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

Mach> INSERT INTO  add_time_table VALUES(6, TO_DATE('2014.12.30 23:22:33 444:555:666'));
1 row(s) inserted.

Mach> SELECT ADD_TIME(dt, '1/0/0 0:0:0') FROM add_time_table;
ADD_TIME(dt, '1/0/0 0:0:0')
..................................
2015.12.30 23:22:33 444:555:666
2015.12.30 11:22:33 444:555:666
2014.11.11 01:02:03 004:005:006
2013.11.11 01:02:03 004:005:006
2001.11.11 01:02:03 004:005:006
2000.11.11 01:02:03 004:005:006
[6] row(s) selected.

Mach> SELECT ADD_TIME(dt, '0/0/0 1:1:1') FROM add_time_table;
ADD_TIME(dt, '0/0/0 1:1:1')
..................................
2014.12.31 00:23:34 444:555:666
2014.12.30 12:23:34 444:555:666
2013.11.11 02:03:04 004:005:006
2012.11.11 02:03:04 004:005:006
2000.11.11 02:03:04 004:005:006
1999.11.11 02:03:04 004:005:006
[6] row(s) selected.

Mach> SELECT ADD_TIME(dt, '1/1/1 0:0:0') FROM add_time_table;
ADD_TIME(dt, '1/1/1 0:0:0')
..................................
2016.01.31 23:22:33 444:555:666
2016.01.31 11:22:33 444:555:666
2014.12.12 01:02:03 004:005:006
2013.12.12 01:02:03 004:005:006
2001.12.12 01:02:03 004:005:006
2000.12.12 01:02:03 004:005:006
[6] row(s) selected.

Mach> SELECT ADD_TIME(dt, '.1/0/0 0:0:0') FROM add_time_table;
ADD_TIME(dt, '.1/0/0 0:0:0')
..................................
2013.12.30 23:22:33 444:555:666
2013.12.30 11:22:33 444:555:666
2012.11.11 01:02:03 004:005:006
2011.11.11 01:02:03 004:005:006
1999.11.11 01:02:03 004:005:006
1998.11.11 01:02:03 004:005:006
[6] row(s) selected.

Mach> SELECT ADD_TIME(dt, '0/0/0 .1:.1:.1') FROM add_time_table;
ADD_TIME(dt, '0/0/0 .1:.1:.1')
..................................
2014.12.30 22:21:32 444:555:666
2014.12.30 10:21:32 444:555:666
2013.11.11 00:01:02 004:005:006
2012.11.11 00:01:02 004:005:006
2000.11.11 00:01:02 004:005:006
1999.11.11 00:01:02 004:005:006
[6] row(s) selected.

Mach> SELECT ADD_TIME(dt, '.1/.1/.1 0:0:0') FROM add_time_table;
ADD_TIME(dt, '.1/.1/.1 0:0:0')
..................................
2013.11.29 23:22:33 444:555:666
2013.11.29 11:22:33 444:555:666
2012.10.10 01:02:03 004:005:006
2011.10.10 01:02:03 004:005:006
1999.10.10 01:02:03 004:005:006
1998.10.10 01:02:03 004:005:006
[6] row(s) selected.

Mach> SELECT * FROM add_time_table WHERE dt > ADD_TIME(TO_DATE('2014.12.30 11:22:33 444:555:666'), '.1/.1/.1 0:0:0');
ID          DT
...............................................
SELECT * FROM add_time_table WHERE dt > ADD_TIME(TO_DATE('2014.12.30 11:22:33 444:555:666'), '.1/.2/.1 0:0:0');
6           2014.12.30 23:22:33 444:555:666
5           2014.12.30 11:22:33 444:555:666
[2] row(s) selected.

Mach> SELECT * FROM add_time_table WHERE dt > ADD_TIME(TO_DATE('2014.12.30 11:22:33 444:555:666'), '.1/.2/.1 0:0:0');
ID          DT
...............................................
6           2014.12.30 23:22:33 444:555:666
5           2014.12.30 11:22:33 444:555:666
4           2013.11.11 01:02:03 004:005:006
[3] row(s) selected.

Mach> SELECT ADD_TIME(TO_DATE('2000.12.01 00:00:00 000:000:001'), '.1/0/0 0:0:.1') FROM add_time_table;
ADD_TIME(TO_DATE('2000.12.01 00:00:00 0
..........................................
1999.11.30 23:59:59 000:000:001
1999.11.30 23:59:59 000:000:001
1999.11.30 23:59:59 000:000:001
1999.11.30 23:59:59 000:000:001
1999.11.30 23:59:59 000:000:001
1999.11.30 23:59:59 000:000:001
[6] row(s) selected.

Mach> SELECT * FROM add_time_table WHERE dt > ADD_TIME(TO_DATE('2014.12.30 11:22:33 444:555:666'), '.1/.2/.1 0:0:0');
ID          DT
...............................................
6           2014.12.30 23:22:33 444:555:666
5           2014.12.30 11:22:33 444:555:666
4           2013.11.11 01:02:03 004:005:006
[3] row(s) selected.

The Diff format is as follows.
"Year / Month / Day Hour: Minute: Second"
Each item has a positive or negative value.

AVG(column)

It is an aggregate function, and prints the average value of a numeric column.

Syntax:

AVG(column_name)

Example:

Mach> CREATE TABLE avg_table (id1 INTEGER, id2 INTEGER);
Created successfully.

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

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

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

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

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

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

Mach> INSERT INTO avg_table VALUES(null, 4);
1 row(s) inserted.

Mach> SELECT id1, AVG(id2) FROM avg_table GROUP BY id1;
id1         AVG(id2)
...........................................
2                2
NULL             4
1                2

BITAND and BITOR

​Input values must be integer type. It converts into a signed 64.bit integer and execute AND/OR operations, and then it returns signed 64.bit values.
For values less than 0, it is recommended to use uinteger and ushort types as it may return different results based on platforms.

Syntax:

BITAND (<expression1>, <expression2>)
BITOR (<expression1>, <expression2>)

Example:

Mach> CREATE TABLE bit_table (i1 INTEGER, i2 UINTEGER, i3 FLOAT, i4 DOUBLE, i5 SHORT, i6 VARCHAR(10));
Created successfully.

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

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

Mach> SELECT BITAND(i1, i2) FROM bit_table;
BITAND(i1, i2)
.......................
2
1
[2] row(s) selected.

Mach> SELECT * FROM bit_table WHERE BITAND(i2, 1) = 1;
I1          I2          I3                          I4                          I5          I6
...............................................................................................................
.1          1           1                           1                           2           aaa
[1] row(s) selected.

Mach> SELECT BITOR(i5, 1) FROM bit_table WHERE BITOR(i5, 1) = 3;
BITOR(i5, 1)
.......................
3
3
[2] row(s) selected.

Mach> SELECT * FROM bit_table WHERE BITOR(i2, 1) = 1;
I1          I2          I3                          I4                          I5          I6
...............................................................................................................
.1          1           1                           1                           2           aaa
[1] row(s) selected.

Mach> SELECT * FROM bit_table WHERE BITAND(i3, 1) = 1;
I1          I2          I3                          I4                          I5          I6
...............................................................................................................
[ERR.02037 : Function [BITAND] argument data type is mismatched.]
[0] row(s) selected.

Mach> SELECT * FROM bit_table WHERE BITAND(i4, 1) = 1;
I1          I2          I3                          I4                          I5          I6
...............................................................................................................
[ERR.02037 : Function [BITAND] argument data type is mismatched.]
[0] row(s) selected.
Elapsed time: 0.001
Mach>
Mach> SELECT BITAND(i5, 1) FROM bit_table WHERE BITAND(i5, 1) = 1;
BITAND(i5, 1)
.......................
1
[1] row(s) selected.

Mach> SELECT * FROM bit_table WHERE BITOR(i6, 1) = 1;
I1          I2          I3                          I4                          I5          I6
...............................................................................................................
[ERR.02037 : Function [BITOR] argument data type is mismatched.]
[0] row(s) selected.
Elapsed time: 0.002
Mach> SELECT BITOR(i1, i2) FROM bit_table;
BITOR(i1, i2)
.......................
.2
.1
[2] row(s) selected.

Mach> SELECT BITAND(i1, i3) FROM bit_table;
BITAND(i1, i3)
.......................
[ERR.02037 : Function [BITAND] argument data type is mismatched.]
[0] row(s) selected.
Elapsed time: 0.001
Mach> SELECT BITOR(i1, i6) FROM bit_table;
BITOR(i1, i6)
.......................
[ERR.02037 : Function [BITOR] argument data type is mismatched.]
[0] row(s) selected.

COUNT(column)

It is an aggregate function, and returns the number of records for a given column.

Syntax:
``sql
SELECT COUNT(column_name) FROM table_name;

** Example:** 
```sql
Mach> CREATE TABLE count_table (id1 INTEGER, id2 INTEGER);
Created successfully.

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

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

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

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

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

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

Mach> INSERT INTO count_table VALUES(null, 4);
1 row(s) inserted.

Mach> SELECT COUNT(*) FROM count_table;
COUNT(*)
.......................
7
[1] row(s) selected.

Mach> SELECT COUNT(id1) FROM count_table;
COUNT(id1)
.......................
6
[1] row(s) selected.

​

DATE_TRUNC

​The parameter, [count], determines how to truncate data based on second, minutes or hour.

Syntax:

DATE_TRUNC (field, date_val [, count])

Example:

Mach> CREATE TABLE trunc_table (i1 INTEGER, i2 DATETIME);
Created successfully.

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

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

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

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

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

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

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

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

Mach> SELECT COUNT(*), DATE_TRUNC('second', i2) tm FROM trunc_table group by tm ORDER BY 2;
COUNT(*)             tm
........................................................
2                    1999.11.11 01:02:00 000:000:000
2                    1999.11.11 01:02:01 000:000:000
2                    1999.11.11 01:02:02 000:000:000
2                    1999.11.11 01:02:03 000:000:000
[4] row(s) selected.

Mach> SELECT COUNT(*), DATE_TRUNC('second', i2, 2) tm FROM trunc_table group by tm ORDER BY 2;
COUNT(*)             tm
........................................................
4                    1999.11.11 01:02:00 000:000:000
4                    1999.11.11 01:02:02 000:000:000
[2] row(s) selected.

For example, date_trunc('hour', time, 6) means that it will truncate data every six hours.

DATA_TRUNC function allows end users easily to obtain statistics per seconds, minutes and even hours. For seconds and minutes, it can have parameters from 1 to 30 even though the numbers cannot be divided, and for hours, 1 to 6 number can be delivered as count, and only "1" is allowed for the type of day.

  • DATE_TRUNC function truncates date_val based on field values.
Field type Parameters
Second 1-30
Minute 1-30
Hour 1-6
Day 1

DECODE(column, [search, return],.. default)

It compares the given column value with the search value. If the two values are equal, it returns the corresponding result. If it is not matched, it returns default value. If default is omitted, it returns NULL.

Syntax:

DECODE(column, [search, return],.. default)

Example:

Mach> CREATE TABLE decode_table (id1 VARCHAR(11));
Created successfully.

Mach> INSERT INTO decode_table VALUES('decodetest1');
1 row(s) inserted.

Mach> INSERT INTO decode_table VALUES('decodetest2');
1 row(s) inserted.

Mach> SELECT id1, DECODE(id1, 'decodetest1', 'result1', 'decodetest2', 'result2', 'DEFAULT') FROM decode_table;
id1          DECODE(id1, 'decodetest1', 'result1', '
.........................................................
decodetest2  result2
decodetest1  result1
[2] row(s) selected.

Mach> SELECT id1, DECODE(id1, 'codetest', 2, 99) FROM decode_table;
id1          DECODE(id1, 'codetest', 2, 99)
...............................................
decodetest2  99
decodetest1  99
[2] row(s) selected.


Mach> SELECT DECODE(id1, 'decodetest1', 2) FROM decode_table;
DECODE(id1, 'decodetest1', 2)
................................
NULL
2
[2] row(s) selected.

Mach> SELECT DECODE(id1, 'codetest', 2) FROM decode_table;
DECODE(id1, 'codetest', 2)
.............................
NULL
NULL
[2] row(s) selected.

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.

FROM_UNIXTIME

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

Example:

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP('2001-01-01')) FROM unix_table;
from_unixtime(unix_timestamp('2001-01-0
------------------------------------------
2001-01-01 00:00:00 000:000:000

GROUP_CONCAT(column)

It is an aggregate function, and it returns the values of the columns in concatenating strings in a group.

  • DISTINCT: When the values are duplicated, it does not concatenate the duplicated values.
  • ORDER BY: It sorts the order of concatenated values of columns based on specified column values.
  • SEPARATOR: To concatenate values, use "SEPARATOR" clause to separate them. Default separator is comma ( , ).

Syntax:

GROUP_CONCAT(
     [DISTINCT] column
     [ORDER BY { unsigned_integer | column }
     [ASC | DESC] [, column ...]]
     [SEPARATOR str_val]
)

Example:

Mach> CREATE TABLE concat_table(id1 INTEGER, id2 DOUBLE, name VARCHAR(10));
Created successfully.

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

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

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

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

Mach> INSERT INTO concat_table VALUES (5, 1, 'Jack');
1 row(s) inserted.

Mach> INSERT INTO concat_table VALUES (6, 1, 'Jack');
1 row(s) inserted.


Mach> SELECT GROUP_CONCAT(name) AS G_NAMES FROM concat_table GROUP BY id2;
G_NAMES                                                                                                           
....................................................................................
Jack,Jack,Ram                                                                                                     
Jill,Zara,John                                                                                                    
[2] row(s) selected.

Mach> SELECT GROUP_CONCAT(DISTINCT name) AS G_NAMES FROM concat_table GROUP BY Id2;
G_NAMES                                                                                                           
....................................................................................
Jack,Ram                                                                                                          
Jill,Zara,John                                                                                                    
[2] row(s) selected.

Mach> SELECT GROUP_CONCAT(name SEPARATOR '.') G_NAMES FROM concat_table GROUP BY Id2;
G_NAMES                                                                                                           
....................................................................................
Jack.Jack.Ram                                                                                                     
Jill.Zara.John                                                                                                    
[2] row(s) selected.
Elapsed time: 0.003

Mach> SELECT GROUP_CONCAT(name ORDER BY id1) G_NAMES, GROUP_CONCAT(id1 ORDER BY id1) G_SORTID FROM concat_table GROUP BY id2;
G_NAMES                                                                                                           
....................................................................................
G_SORTID                                                                                                          
....................................................................................
Ram,Jack,Jack                                                                                                     
2,5,6                                                                                                             
John,Zara,Jill                                                                                                    
1,3,4                                                                                                             
[2] row(s) selected.

Caution required when you use this syntax. See below.

  • You can specify only one column to concatenate values. When you want to specify more than two, use TO_CHAR() and CONCAT operator (||) to combine them into one expression.
  • Users can specify other columns besides the concatenated columns, and multiple columns can be specified as well.
  • For SEPARATOR clause, only a string constant must be used. String column cannot be used here.

LEAST and GREATEST

When it specifies various columns or values as input parameters, the functions return the least value or the greatest value.

Syntax:

LEAST(value_list, value_list,...)
GREATEST(value_list, value_list,...)

Example:

Mach> CREATE TABLE lgtest_table(c1 INTEGER, c2 LONG, c3 VARCHAR(10), c4 VARCHAR(5));
Created successfully.

Mach> INSERT INTO lgtest_table VALUES (1, 2, 'abstract', 'ace');
1 row(s) inserted.

Mach> INSERT INTO lgtest_table VALUES (null, 100, null, 'bag');
1 row(s) inserted.

Mach> SELECT LEAST (c1, c2) FROM lgtest_table;
LEAST (c1, c2)
.......................
NULL
1
[2] row(s) selected.

Mach> SELECT LEAST (c1, c2, .1) FROM lgtest_table;
LEAST (c1, c2, .1)
.......................
NULL
.1
[2] row(s) selected.

Mach> SELECT GREATEST(c3, c4) FROM lgtest_table;
GREATEST(c3, c4)
....................
NULL
ace
[2] row(s) selected.

Mach> SELECT LEAST(c3, c4) FROM lgtest_table;
LEAST(c3, c4)
.................
NULL
abstract
[2] row(s) selected.

Mach> SELECT LEAST(NVL(c3, 'aa'), c4) FROM lgtest_table;
LEAST(NVL(c3, 'aa'), c4)
............................
aa
abstract
[2] row(s) selected.

Mach>

If input value is 1 or none, an error occurs.
If NULL is included for input values, it returns NULL, thus, it requires to convert it before executing the statement by using NVL function if input value is column.
An error occurs when the input values have BLOB or TEXT which cannot be compared or have values that cannot convert into types for comparison of small versus large.

LENGTH(column)

It returns the length of a string column in bytes.

Syntax:

LENGTH (column_name)

Example:

Mach> CREATE TABLE length_table (id1 INTEGER, id2 DOUBLE, name VARCHAR(15));
Created successfully.

Mach> INSERT INTO length_table VALUES(1, 10, 'Around the Horn');
1 row(s) inserted.

Mach> INSERT INTO length_table VALUES(NULL, 20, 'Alfreds Futterkiste');
1 row(s) inserted.

Mach> INSERT INTO length_table VALUES(3, NULL, 'Antonio Moreno');
1 row(s) inserted.

Mach> INSERT INTO length_table VALUES(4, 40, NULL);
1 row(s) inserted.

Mach> select * FROM length_table;
ID1         ID2                         NAME
.............................................................
4           40                          NULL
3           NULL                        Antonio Moreno
NULL        20                          Alfreds Futterk
1           10                          Around the Horn
[4] row(s) selected.

Mach> select id1 * 10 FROM length_table;
id1 * 10
.......................
40
30
NULL
10
[4] row(s) selected.

Mach> select * FROM length_table Where id1 > 1 and id2 < 50;
ID1         ID2                         NAME
.............................................................
4           40                          NULL
[1] row(s) selected.

Mach> select name || ' with null concat' FROM length_table;
name || ' with null concat'
....................................
NULL
Antonio Moreno with null concat
Alfreds Futterk with null concat
Around the Horn with null concat
[4] row(s) selected.

Mach> select LENGTH(name) FROM length_table;
LENGTH(name)
...............
NULL
14
15
15
[4] row(s) selected.

LOWER(column)

It returns a character expression after converting a string of uppercase to lowercase.

Syntax:

LOWER (column_name)

Example:

Mach> CREATE TABLE lower_table (name VARCHAR(20));
Created successfully.

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

Mach> INSERT INTO lower_table VALUES('James Backley');
1 row(s) inserted.

Mach> INSERT INTO lower_table VALUES('Alfreds Futterkiste');
1 row(s) inserted.

Mach> INSERT INTO lower_table VALUES('Antonio MORENO');
1 row(s) inserted.

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

Mach> SELECT LOWER(name) FROM lower_table;
LOWER(name)
........................
NULL
antonio moreno
alfreds futterkiste
james backley
NULL
[5] row(s) selected.

LPAD(column, length, char) and RPAD

​It appends characters to the left (LPAD) or right (RPAD) until input values reach the given length.

Syntax:

RPAD(str, len, padstr)
LPAD(str, len, padstr)

Example:

Mach> CREATE TABLE pad_table (c1 integer, c2 varchar(15));
Created successfully.

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

Mach> INSERT INTO pad_table VALUES (25, 'Johnathan');
1 row(s) inserted.

Mach> INSERT INTO pad_table VALUES (30, 'M');
1 row(s) inserted.

Mach> SELECT LPAD(to_char(c1), 5, '0') FROM pad_table;
LPAD(to_char(c1), 5, '0')
.............................
00030
00025
00001
[3] row(s) selected.

Mach> SELECT RPAD(to_char(c1), 5, '0') FROM pad_table;
RPAD(to_char(c1), 5, '0')
.............................
30000
25000
10000
[3] row(s) selected.

Mach> SELECT LPAD(c2, 5) FROM pad_table;
LPAD(c2, 5)
...............
    M
Johna
Anton
[3] row(s) selected.

Mach> SELECT RPAD(c2, 5) FROM pad_table;
RPAD(c2, 5)
...............
M
Johna
Anton
[3] row(s) selected.



Mach> SELECT RPAD(c2, 10, '***') FROM pad_table;
RPAD(c2, 10, '***')
.......................
M*********
Johnathan*
Antonio***
[3] row(s) selected.

The last parameter "char" can be omitted, and if omitted, a white space ' ' should be used. If inserted column values are longer than the given length, it only retrieves the length of the inserted data from the beginning rather than appending additional characters.

LTRIM and RTRIM

​In a first parameter, it removes characters that matches with the pattern string. LTRIM function removes characters from the left side of a string. RTRIM function removes characters from the right side of a string.

Syntax:

LTRIM(column_name, pattern)
RTRIM(column_name, pattern)

Example:

Mach> CREATE TABLE trim_table1(name VARCHAR(10));
Created successfully.

Mach> INSERT INTO trim_table1 VALUES ('   smith   ');
1 row(s) inserted.

Mach> SELECT ltrim(name) FROM trim_table1;
ltrim(name)
...............
smith
[1] row(s) selected.

Mach> SELECT rtrim(name) FROM trim_table1;
rtrim(name)
...............
   smith
[1] row(s) selected.

Mach> SELECT ltrim(name, ' s') FROM trim_table1;
ltrim(name, ' s')
.....................
mith
[1] row(s) selected.

Mach> SELECT rtrim(name, 'h ') FROM trim_table1;
rtrim(name, 'h ')
.....................
   smit
[1] row(s) selected.

Mach> CREATE TABLE trim_table2 (name VARCHAR(10));
Created successfully.

Mach> INSERT INTO trim_table2 VALUES ('ddckaaadkk');
1 row(s) inserted.

Mach> SELECT ltrim(name, 'dc') FROM trim_table2;
ltrim(name, 'dc')
.....................
kaaadkk
[1] row(s) selected.

Mach> SELECT rtrim(name, 'dk') FROM trim_table2;
rtrim(name, 'dk')
.....................
ddckaaa
[1] row(s) selected.

Mach> SELECT ltrim(name, 'dckak') FROM trim_table2;
ltrim(name, 'dckak')
........................
NULL
[1] row(s) selected.

Mach> SELECT rtrim(name, 'dckak') FROM trim_table2;
rtrim(name, 'dckak')
........................
NULL
[1] row(s) selected.

These functions remove characters until it meets a character that doesn't have the pattern. If all characters are removed because every character has the pattern, it returns NULL.
If pattern expression is not specified, it removes a white space by using a white space ' ' as default.

MAX(column)

It returns the maximum value of a numeric column.

Syntax:

SELECT MAX(column_name) FROM table_name;

Example:

Mach> CREATE TABLE max_table (c INTEGER);
Created successfully.

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

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

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

Mach> SELECT MAX(c) FROM max_table;
MAX(c)
..............
30
[1] row(s) selected.

MIN(column)

It returns the minimum value of a numeric column.

Syntax:

SELECT MIN(column_name) FROM table_name;

Example:

Mach> CREATE TABLE min_table(c1 INTEGER);
Created successfully.

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

Mach> INSERT INTO min_table VALUES(22);
1 row(s) inserted.

Mach> INSERT INTO min_table VALUES(33);
1 row(s) inserted.

Mach> SELECT MIN(c1) FROM min_table;
MIN(c1)
..............
1
[1] row(s) selected.

NVL(column, value)

It returns the value if the value of the column is NULL. Otherwise, it returns existing value of the column.

Syntax:

NVL(string1, replace_with )

Example:

Mach> CREATE TABLE nvl_table (c1 varchar(10));
Created successfully.

Mach> INSERT INTO nvl_table VALUES ('Johnathan');
1 row(s) inserted.

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

Mach> SELECT NVL(c1, 'Thomas') FROM nvl_table;
NVL(c1, 'Thomas')
.....................
Thomas
Johnathan

ROUND

The ROUND function returns number rounded to n places to the right of the decimal point. If you omit n, then number is rounded to 0 places. The argument n can be negative to round off digits left of the decimal point.

Syntax:

SELECT ROUND(column_name,decimals) FROM table_name;

Example:

Flux> CREATE TABLE round_table (c1 DOUBLE);
Created successfully.

Mach> INSERT INTO round_table VALUES (1.994);
1 row(s) inserted.

Mach> INSERT INTO round_table VALUES (1.995);
1 row(s) inserted.

Mach> SELECT c1, ROUND(c1, 2) FROM round_table;
c1                          ROUND(c1, 2)
...........................................................
1.995                       2
1.994                       1.99

ROWNUM()

The function specifies a number to the row from query results. It can be used in "Subquery" or "Inlinve View" that are used inside the SELECT query. In "Inline View", to reference from the outside, "Alias" must be used with ROWNUM() function in the target list.

Example;

Mach> CREATE TABLE rownum_table(c1 INTEGER, c2 DOUBLE, c3 VARCHAR(10));
Created successfully.

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

Mach> INSERT INTO rownum_table VALUES(2, 2.0, 'Second Row');
1 row(s) inserted.

Mach> INSERT INTO rownum_table VALUES(3, 3.3, 'Third Row');
1 row(s) inserted.

Mach> INSERT INTO rownum_table VALUES(4, 4.3, 'Fourth Row');
1 row(s) inserted.

Mach> SELECT INNER_RANK, c3 AS NAME
    2 FROM   (SELECT ROWNUM() AS INNER_RANK, * FROM rownum_table)
    3 WHERE  INNER_RANK < 3;
INNER_RANK           NAME
....................................
1                    Fourth Row
2                    Third Row
[2] row(s) selected.

The function can be used for Target List, GROUP BY, or ORDER BY clause of SELECT query. However, it cannot be used for WHERE and HAVING clauses of SELECT query. If you want to control the result number of ROWNUM() through WHERE or HAVING clause, use "Inline View" with SELECT query that includes ROWNUM(), and then reference it from the outer query of WHERE or HAVING clause.

Available clause Not applicable clause
Target List / GROUP BY / ORDER BY WHERE / HAVING
  • Results Order by Sorting

Example:

Mach> CREATE TABLE rownum_table(c1 INTEGER, c2 DOUBLE, c3 VARCHAR(10));
Created successfully.

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

Mach> INSERT INTO rownum_table VALUES(2, 2.0, 'John');
1 row(s) inserted.

Mach> INSERT INTO rownum_table VALUES(3, 3.3, 'Sarah');
1 row(s) inserted.

Mach> INSERT INTO rownum_table VALUES(4, 4.3, 'Micheal');
1 row(s) inserted.

Mach> SELECT ROWNUM(), c2 AS SORT, c3 AS NAME
    2 FROM   ( SELECT * FROM rownum_table ORDER BY c3 );
ROWNUM()             SORT                        NAME
.................................................................
1                    1                           NULL
2                    2                           John
3                    4.3                         Micheal
4                    3.3                         Sarah

[4] row(s) selected.

When SELECT query has ORDER BY clause, result numbers of ROWNUM() in the target list may not be sorted in consecutive order. This is caused because ROWNUM() operation is done before an operation of ORDER BY clause. If you want to sort the results in consecutive order, use the inline view construct with the query including ORDER BY clause, then call ROWNUM() from outer SELECT query.

results matching ""

    No results matching ""