All Products
Search
Document Center

Single row functions

Last Updated: Jun 08, 2020

Date and time functions

Date and time functions are mainly used to display information about date and time.

CURDATE

Syntax

  1. CURDATE()

Description

Returns the current date without the exact time.

Example:

  1. OceanBase (root@test)> SELECT CURDATE();
  2. +------------+
  3. | CURDATE() |
  4. +------------+
  5. | 2018-05-05 |
  6. +------------+
  7. 1 row in set (0.00 sec)

CURRENT_DATE

Syntax

  1. CURRENT_DATE()
  2. CURRENT_DATE

Description

It is the same as CURDATE().

CURRENT_TIME

Syntax

  1. CURRENT_TIME([scale])

Description

Returns the current time without the exact date.

scale specifies the precision of the fractional seconds. Valid values are 0 ~ 6. The default value is 0.

Example:

  1. OceanBase (root@test)> SELECT CURRENT_TIME(6);
  2. +-----------------+
  3. | CURRENT_TIME(6) |
  4. +-----------------+
  5. | 11:11:45.215311 |
  6. +-----------------+
  7. 1 row in set (0.01 sec)

CURRENT_TIMESTAMP

Syntax

  1. CURRENT_TIMESTAMP([scale])

Description

Returns the current date and time based on the specified time zone.

scale specifies the precision of the fractional seconds. Valid values are 0 ~ 6. The default value is 0.

Example:

  1. OceanBase (root@test)> SELECT CURRENT_TIMESTAMP(6);
  2. +----------------------------+
  3. | CURRENT_TIMESTAMP(6) |
  4. +----------------------------+
  5. | 2018-05-05 11:35:39.177764 |
  6. +----------------------------+
  7. 1 row in set (0.01 sec)

CURTIME

Syntax

  1. CURTIME()

Description

It is the same as CURRENT_TIME().

DATE_ADD

Syntax

  1. DATE_ADD(date, INTERVAL expr unit)

Description

Performs arithmetic calculations on datetime.

  • date represents the date and time base. Date is required while the exact time is optional.

  • expr represents the time interval, which can be negative.

  • unit represents the unit of the time interval.

The following table lists all units for time intervals.

Unit Type Description Format
MICROSECOND Independent A microsecond MICROSECONDS
SECOND Independent A second SECONDS
MINUTE Independent A minute MINUTES
HOUR Independent An hour HOURS
DAY Independent A day DAYS
WEEK Independent A week WEEKS
MONTH Independent A month MONTHS
QUARTER Independent A quarter QUARTERS
YEAR Independent A year YEARS
SECOND_MICROSECOND Combined From seconds to microseconds 'SECONDS.MICROSECONDS'
MINUTE_MICROSECOND Combined From minutes to microseconds 'MINUTES:SECONDS.MICROSECONDS'
MINUTE_SECOND Combined From minutes to seconds 'MINUTES:SECONDS'
HOUR_MICROSECOND Combined From hours to microseconds 'HOURS:MINUTES:SECONDS.MICROSECONDS'
HOUR_SECOND Combined From hours to seconds 'HOURS:MINUTES:SECONDS'
HOUR_MINUTE Combined From hours to minutes 'HOURS:MINUTES'
DAY_SECOND Combined From days to seconds 'DAYS HOURS:MINUTES:SECONDS'
DAY_MINUTE Combined From days to minutes 'DDAYSD HOURS:MINUTES'
DAY_HOUR Combined From days to hours 'DAYS HOURS'
YEAR_MONTH Combined From years to months 'YEARS-MONTHS'

Example:

  1. OceanBase (root@test)> SELECT
  2. -> DATE_ADD(NOW(), INTERVAL 5 DAY),
  3. -> DATE_ADD('2014-01-10', INTERVAL 5 MICROSECOND),
  4. -> DATE_ADD('2014-01-10', INTERVAL 5 SECOND),
  5. -> DATE_ADD('2014-01-10', INTERVAL 5 MINUTE),
  6. -> DATE_ADD('2014-01-10', INTERVAL 5 HOUR),
  7. -> DATE_ADD('2014-01-10', INTERVAL 5 DAY),
  8. -> DATE_ADD('2014-01-10', INTERVAL 5 WEEK),
  9. -> DATE_ADD('2014-01-10', INTERVAL 5 MONTH),
  10. -> DATE_ADD('2014-01-10', INTERVAL 5 QUARTER),
  11. -> DATE_ADD('2014-01-10', INTERVAL 5 YEAR),
  12. -> DATE_ADD('2014-01-10', INTERVAL '5.000005' SECOND_MICROSECOND),
  13. -> DATE_ADD('2014-01-10', INTERVAL '05:05.000005' MINUTE_MICROSECOND),
  14. -> DATE_ADD('2014-01-10', INTERVAL '05:05' MINUTE_SECOND),
  15. -> DATE_ADD('2014-01-10', INTERVAL '05:05:05.000005' HOUR_MICROSECOND),
  16. -> DATE_ADD('2014-01-10', INTERVAL '05:05:05' HOUR_SECOND),
  17. -> DATE_ADD('2014-01-10', INTERVAL '05:05' HOUR_MINUTE),
  18. -> DATE_ADD('2014-01-10', INTERVAL '01 05:05:05.000005' DAY_MICROSECOND),
  19. -> DATE_ADD('2014-01-10', INTERVAL '01 05:05:05' DAY_SECOND),
  20. -> DATE_ADD('2014-01-10', INTERVAL '01 05:05' DAY_MINUTE),
  21. -> DATE_ADD('2014-01-10', INTERVAL '01 05' DAY_HOUR),
  22. -> DATE_ADD('2014-01-10', INTERVAL '1-01' YEAR_MONTH)
  23. -> \G
  24. *************************** 1. row ***************************
  25. DATE_ADD(NOW(), INTERVAL 5 DAY): 2018-05-10 14:54:52
  26. DATE_ADD('2014-01-10', INTERVAL 5 MICROSECOND): 2014-01-10 00:00:00.000005
  27. DATE_ADD('2014-01-10', INTERVAL 5 SECOND): 2014-01-10 00:00:05
  28. DATE_ADD('2014-01-10', INTERVAL 5 MINUTE): 2014-01-10 00:05:00
  29. DATE_ADD('2014-01-10', INTERVAL 5 HOUR): 2014-01-10 05:00:00
  30. DATE_ADD('2014-01-10', INTERVAL 5 DAY): 2014-01-15
  31. DATE_ADD('2014-01-10', INTERVAL 5 WEEK): 2014-02-14
  32. DATE_ADD('2014-01-10', INTERVAL 5 MONTH): 2014-06-10
  33. DATE_ADD('2014-01-10', INTERVAL 5 QUARTER): 2015-04-10
  34. DATE_ADD('2014-01-10', INTERVAL 5 YEAR): 2019-01-10
  35. DATE_ADD('2014-01-10', INTERVAL '5.000005' SECOND_MICROSECOND): 2014-01-10 00:00:05.000005
  36. DATE_ADD('2014-01-10', INTERVAL '05:05.000005' MINUTE_MICROSECOND): 2014-01-10 00:05:05.000005
  37. DATE_ADD('2014-01-10', INTERVAL '05:05' MINUTE_SECOND): 2014-01-10 00:05:05
  38. DATE_ADD('2014-01-10', INTERVAL '05:05:05.000005' HOUR_MICROSECOND): 2014-01-10 05:05:05.000005
  39. DATE_ADD('2014-01-10', INTERVAL '05:05:05' HOUR_SECOND): 2014-01-10 05:05:05
  40. DATE_ADD('2014-01-10', INTERVAL '05:05' HOUR_MINUTE): 2014-01-10 05:05:00
  41. DATE_ADD('2014-01-10', INTERVAL '01 05:05:05.000005' DAY_MICROSECOND): 2014-01-11 05:05:05.000005
  42. DATE_ADD('2014-01-10', INTERVAL '01 05:05:05' DAY_SECOND): 2014-01-11 05:05:05
  43. DATE_ADD('2014-01-10', INTERVAL '01 05:05' DAY_MINUTE): 2014-01-11 05:05:00
  44. DATE_ADD('2014-01-10', INTERVAL '01 05' DAY_HOUR): 2014-01-11 05:00:00
  45. DATE_ADD('2014-01-10', INTERVAL '1-01' YEAR_MONTH): 2015-02-10
  46. 1 row in set (0.01 sec)

DATE_FORMAT

Syntax

  1. DATE_FORMAT(date, format)

Description

Outputs the date and time in the specified format.

  • date indicates the date and time.

  • format indicates the output format.

The following table lists all output formats.

Format character Description Format
%a The abbreviation of the days in a week. Sun ... Sat
%b The abbreviations for months. Jan ... Dec
%c The numeric form for months. 1 to 12
%D The abbreviations for dates of a month 1st to 31st
%d The numeric form of the days in a month. 01 to 31
%e The numeric form of the days in a month. 1 to 31
%f Microseconds. 000000 to 999999
%H Hours. `00 to 23
%h Hours. 01 to 12
%I Hours. 01 to 12
%i Minutes. 00 to 59
%j The nth day of the year. 001 to 366
%k Hours. 0 to 23
%l Hours. 0 to 12
%M The month names. January ... December
%m The numeric form for months. 01 to 12
%p Morning or afternoon. AM/PM
%r 12-hour clock. hh:mm:ss AM/PM
%S Seconds. 00 to 59
%s Seconds. 00 to 59
%T 24-hour clock. hh:mm:ss
%U The nth week of the year with Sunday being the first day of the week. 00 to 53
%u The nth week of the year, with Monday being the first day of the week. 00 to 53
%V The nth week of the year, with Sunday being the first day of the week. This option must be used together with %X. 01 to 53
%v The nth week of the year, with Monday being the first day of the week. This option must be used together with %x. 01 to 53
%W The names of the days in a week. Sunday ... Saturday
%w The nth day of the week. 0=Sunday ... 6=Saturday
%X The year of the week, with Sunday being the first day of the week. This option must be used together with %V.
%x The year of the week, with Monday being the first day of the week. This option must be used with %v).
%Y Four-digit years.
%y Two-digit years.
%% Outputs the character %.

Example:

  1. OceanBase (root@test)> SELECT
  2. -> DATE_FORMAT('2014-01-01', '%Y-%M-%d'),
  3. -> DATE_FORMAT('2014-01-01', '%X-%V'),
  4. -> DATE_FORMAT('2014-01-01', '%U')
  5. -> \G
  6. *************************** 1. row ***************************
  7. DATE_FORMAT('2014-01-01', '%Y-%M-%d'): 2014-January-01
  8. DATE_FORMAT('2014-01-01', '%X-%V'): 2013-52
  9. DATE_FORMAT('2014-01-01', '%U'): 00
  10. 1 row in set (0.01 sec)

DATE_SUB

Syntax

  1. DATE_SUB(date, INTERVAL expr unit)

Description

Performs arithmetic calculations on datetime.

For more information, see DATE_ADD().

DATEDIFF

Syntax

  1. DATEDIFF(date1, date2)

Description

Returns the number of days between date1 and date2.

Only the date part of the parameter is used in the calculation, and the time part is ignored.

Example:

  1. OceanBase (root@test)> SELECT DATEDIFF('2015-06-19','1994-12-17');
  2. +-------------------------------------+
  3. | DATEDIFF('2015-06-19','1994-12-17') |
  4. +-------------------------------------+
  5. | 7489 |
  6. +-------------------------------------+
  7. 1 row in set (0.01 sec)

EXTRACT

Syntax

  1. EXTRACT(unit FROM date)

Description

Returns the specified portion of DATE as an integer value. If multiple sections are specified, all values are concatenated in order.

unit is the same as in DATE_ADD(). When the unit is WEEK, refer to the description of %U for theDATE_FORMAT() function.

Example:

  1. SELECT EXTRACT(WEEK FROM '2013-01-01'),
  2. EXTRACT(WEEK FROM '2013-01-06'),
  3. EXTRACT(YEAR_MONTH FROM '2012-03-09'),
  4. EXTRACT(DAY FROM NOW())\G;
  5. * 1. row *
  6. EXTRACT(WEEK FROM '2013-01-01'): 0
  7. EXTRACT(WEEK FROM '2013-01-06'): 1
  8. EXTRACT(YEAR_MONTH FROM '2012-03-09'): 201203
  9. EXTRACT(DAY FROM NOW()): 18
  10. 1 row in set (0.00 sec)

FROM_DAYS

Syntax

  1. FROM_DAYS(N)

Description

Returns the DATE value according to the number of days N. The number of days refers to the number of days that have elapsed since 0000-01-01.

Example:

  1. OceanBase (root@test)> SELECT FROM_DAYS(736271), FROM_DAYS(700000);
  2. +-------------------+-------------------+
  3. | FROM_DAYS(736271) | FROM_DAYS(700000) |
  4. +-------------------+-------------------+
  5. | 2015-11-04 | 1916-07-15 |
  6. +-------------------+-------------------+
  7. 1 row in set (0.00 sec)

FROM_UNIXTIME

Syntax

  1. FROM_UNIXTIME(unix_timestamp)
  2. FROM_UNIXTIME(unix_timestamp, format)

Description

  • If you do not specify the format parameter, the function returns a DATETIME value ignoring the time zone.

  • If you specify the format parameter, a DATETIME string in the specified format is returned.

unix_timestamp refers to the UNIX timestamp. It is the time that has elapsed since 1970-01-01 00:00:00.000000 in microseconds.

format supports formats that are listed in the description of the DATE_FORMAT() function.

Example:

  1. OceanBase (root@test)> SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x');
  2. +---------------------------------------------------------+
  3. | FROM_UNIXTIME(UNIX_TIMESTAMP(), '%Y %D %M %h:%i:%s %x') |
  4. +---------------------------------------------------------+
  5. | 2018 5th May 08:41:26 2018 |
  6. +---------------------------------------------------------+
  7. 1 row in set (0.01 sec)

MONTH

Syntax

  1. MONTH(date)

Description

Returns the month of the date .

Example:

  1. OceanBase (root@test)> SELECT MONTH('2008-02-03');
  2. +---------------------+
  3. | MONTH('2008-02-03') |
  4. +---------------------+
  5. | 2 |
  6. +---------------------+
  7. 1 row in set (0.01 sec)

NOW

Syntax

  1. NOW([scale])

Description

It is the same as CURRENT_TIMESTAMP().

PERIOD_DIFF

Syntax

  1. PERIOD_DIFF(p1, p2)

Description

Returns the interval between two dates in months. The date can only contain year and month information, and the format must be YYYYMM or YYMM.

Example:

  1. OceanBase (root@test)> SELECT PERIOD_DIFF(200802, 200703);
  2. +----------------------------+
  3. | PERIOD_DIFF(200802,200703) |
  4. +----------------------------+
  5. | 11 |
  6. +----------------------------+
  7. 1 row in set (0.01 sec)

STR_TO_DATE

Syntax

  1. STR_TO_DATE(str, format)

Description

Converts str to a DATETIME, DATE, or TIME value based on the specified format. The returned time format depends on the types of time that are included in format.

format supports formats that are listed in the description of the DATE_FORMAT() function.

Example:

  1. OceanBase (root@test)> SELECT STR_TO_DATE('2014-Jan-1st 5:5:5 pm', '%Y-%b-%D %r');
  2. +-----------------------------------------------------+
  3. | STR_TO_DATE('2014-Jan-1st 5:5:5 pm', '%Y-%b-%D %r') |
  4. +-----------------------------------------------------+
  5. | 2014-01-01 05:05:05 |
  6. +-----------------------------------------------------+
  7. 1 row in set (0.01 sec)

TIME

Syntax

  1. TIME(datetime)

Description

Converts a datetime value to the TIME type.

Example:

  1. OceanBase (root@test)> SELECT TIME('2003-12-31 01:02:03');
  2. +-----------------------------+
  3. | TIME('2003-12-31 01:02:03') |
  4. +-----------------------------+
  5. | 01:02:03.000000 |
  6. +-----------------------------+
  7. 1 row in set (0.01 sec)

TIME_TO_USEC

Syntax

  1. TIME_TO_USEC(date)

Description

Converts date to the number of microseconds that have elapsed since 1970-01-01 00: 00: 00.000000 based on the system time zone.

date can represent a date or a datetime.

Example:

  1. OceanBase (root@test)> SELECT TIME_TO_USEC('2014-03-25'), TIME_TO_USEC(NOW());
  2. +----------------------------+---------------------+
  3. | TIME_TO_USEC('2014-03-25') | TIME_TO_USEC(NOW()) |
  4. +----------------------------+---------------------+
  5. | 1395676800000000 | 1525528100000000 |
  6. +----------------------------+---------------------+
  7. 1 row in set (0.01 sec)

TIMEDIFF

Syntax

  1. TIMEDIFF(date1, date2)

Description

Returns a TIME value, which represents the time interval between two datetimes.

Example:

  1. OceanBase (root@test)> SELECT
  2. -> TIMEDIFF('2015-06-06 12:12:12', '2014-06-05 11:11:11'),
  3. -> TIMEDIFF('2015-06-06 12:12:12', '2015-06-05 11:11:11')
  4. -> \G
  5. *************************** 1. row ***************************
  6. TIMEDIFF('2015-06-06 12:12:12', '2014-06-05 11:11:11'): 838:59:59
  7. TIMEDIFF('2015-06-06 12:12:12', '2015-06-05 11:11:11'): 25:01:01
  8. 1 row in set (0.00 sec)

TIMESTAMPDIFF

Syntax

  1. TIMESTAMPDIFF(unit, date1, date2)

Description

Returns the interval between two datetimes in unit. unit can only be one of the independent units provided in the description of the DATE_ADD() function.

Example:

  1. OceanBase (root@test)> SELECT
  2. -> TIMESTAMPDIFF(SECOND, NOW(), '2011-01-01 11:11:11'),
  3. -> TIMESTAMPDIFF(DAY, '2011-01-01 11:11:11', NOW())
  4. -> \G
  5. *************************** 1. row ***************************
  6. TIMESTAMPDIFF(SECOND, NOW(), '2011-01-01 11:11:11'): -231677498
  7. TIMESTAMPDIFF(DAY, '2011-01-01 11:11:11', NOW()): 2681
  8. 1 row in set (0.00 sec)

TIMESTAMPADD

Syntax

  1. TIMESTAMPADD(unit, interval_expr, date)

Description

Performs arithmetic calculations on datetime.

It is the same as DATE_ADD(). However, the unit must be an independent unit.

Example:

  1. OceanBase (root@test)> SELECT
  2. -> TIMESTAMPADD(DAY, -5, '2010-01-01 00:00:00'),
  3. -> DATE_ADD('2010-01-01 00:00:00', INTERVAL -5 DAY)
  4. -> \G
  5. *************************** 1. row ***************************
  6. TIMESTAMPADD(DAY, -5, '2010-01-01 00:00:00'): 2009-12-27 00:00:00
  7. DATE_ADD('2010-01-01 00:00:00', INTERVAL -5 DAY): 2009-12-27 00:00:00
  8. 1 row in set (0.01 sec)

TO_DAYS

Syntax

  1. TO_DAYS(date)

Description

Returns the number of days based on the date value. The number of days refers to the number of days that have elapsed since 0000-01-01.

Example:

  1. OceanBase (root@test)> SELECT TO_DAYS('2015-11-04'), TO_DAYS('20151104');
  2. +-----------------------+---------------------+
  3. | TO_DAYS('2015-11-04') | TO_DAYS('20151104') |
  4. +-----------------------+---------------------+
  5. | 736271 | 736271 |
  6. +-----------------------+---------------------+
  7. 1 row in set (0.01 sec)

USEC_TO_TIME

Syntax

  1. USEC_TO_TIME(usec)

Description

Converts a usec value to a TIMESTAMP value.

usec is the time elapsed since 1970-01-01 00: 00: 00.000000 in microseconds, based on the system time zone.

Example:

  1. OceanBase (root@test)> SELECT USEC_TO_TIME(1);
  2. +----------------------------+
  3. | USEC_TO_TIME(1) |
  4. +----------------------------+
  5. | 1970-01-01 08:00:00.000001 |
  6. +----------------------------+
  7. 1 row in set (0.00 sec)

UNIX_TIMESTAMP

Syntax

  1. UNIX_TIMESTAMP()
  2. UNIX_TIMESTAMP(date)

Description

  • If you do not specify the DATE parameter, the function returns the elapsed time since 1970-01-01 00:00:00 in seconds based on the system time zone.

  • If you specify the date parameter, the function returns the number of seconds between the specified time and 1970-01-01 00:00:00 based on the system time zone.

Example:

  1. OceanBase (root@test)> SELECT UNIX_TIMESTAMP(), TIME_TO_USEC(NOW());
  2. +------------------+---------------------+
  3. | UNIX_TIMESTAMP() | TIME_TO_USEC(NOW()) |
  4. +------------------+---------------------+
  5. | 1525570561 | 1525570561000000 |
  6. +------------------+---------------------+
  7. 1 row in set (0.01 sec)
  8. OceanBase (root@test)> SELECT UNIX_TIMESTAMP('1997-10-04 22:23:00');
  9. +---------------------------------------+
  10. | UNIX_TIMESTAMP('1997-10-04 22:23:00') |
  11. +---------------------------------------+
  12. | 875974980 |
  13. +---------------------------------------+
  14. 1 row in set (0.01 sec)

UTC_TIMESTAMP

Syntax

  1. UTC_TIMESTAMP()

Description

Returns the current UTC time.

Example:

  1. OceanBase (root@test)> SELECT UTC_TIMESTAMP();
  2. +---------------------+
  3. | UTC_TIMESTAMP() |
  4. +---------------------+
  5. | 2018-05-06 01:38:32 |
  6. +---------------------+
  7. 1 row in set (0.01 sec)

YEAR

Syntax

  1. YEAR(date)

Description

Returns the year of the date value.

Example:

  1. OceanBase (root@test)> SELECT YEAR('1987-01-01');
  2. +--------------------+
  3. | YEAR('1987-01-01') |
  4. +--------------------+
  5. | 1987 |
  6. +--------------------+
  7. 1 row in set (0.00 sec)

String functions

CONCAT

Syntax

  1. CONCAT(str1, .., strN)

Description

Concatenates two or more strings into a single string. If a parameter is NULL, the function returns NULL.

Example:

  1. OceanBase (root@test)> SELECT
  2. -> CONCAT('test','OceanBase', '1.0'),
  3. -> CONCAT('test','OceanBase', NULL)
  4. -> \G
  5. *************************** 1. row ***************************
  6. CONCAT('test','OceanBase', '1.0'): testOceanBase1.0
  7. CONCAT('test','OceanBase', NULL): NULL
  8. 1 row in set (0.01 sec)

CONCAT_WS

Syntax

  1. CONCAT_WS(separator, str1, .., strN)

Description

Concatenates multiple strings into one string, and separate adjacent strings with separator. If a parameter is NULL, the function ignores the NULL value.

Example:

  1. OceanBase (root@test)> SELECT
  2. -> CONCAT_WS('_', 'First', 'Second'),
  3. -> CONCAT_WS('_', 'First', NULL, 'Second')
  4. -> \G
  5. *************************** 1. row ***************************
  6. CONCAT_WS('_', 'First', 'Second'): First_Second
  7. CONCAT_WS('_', 'First', NULL, 'Second'): First_Second
  8. 1 row in set (0.00 sec)

SUBSTR

Syntax

  1. SUBSTR(str, pos)
  2. SUBSTR(str, pos, len)
  3. SUBSTR(str FROM pos)
  4. SUBSTR(str FROM pos FOR len)

Description

Returns the substring of length len, starting at pos of str. If a parameter is NULL, the function returns NULL.

  • If you do not specify len, the substring returned starts from pos to the end of str.

  • If pos is negative, the starting position is counted from the tail of str.

  • If len is less than or equal to 0, or pos is an invalid starting position, an empty string is returned.

Example:

  1. OceanBase (root@test)> SELECT
  2. -> SUBSTR('abcdefg', 3),
  3. -> SUBSTR('abcdefg', 3, 2),
  4. -> SUBSTR('abcdefg', -3),
  5. -> SUBSTR('abcdefg', 3, -2),
  6. -> SUBSTR('abcdefg' from -4 for 2)
  7. -> \G
  8. *************************** 1. row ***************************
  9. SUBSTR('abcdefg', 3): cdefg
  10. SUBSTR('abcdefg', 3, 2): cd
  11. SUBSTR('abcdefg', -3): efg
  12. SUBSTR('abcdefg', 3, -2):
  13. SUBSTR('abcdefg' from -4 for 2): de
  14. 1 row in set (0.01 sec)

SUBSTRING

Syntax

  1. SUBSTRING(str, pos)
  2. SUBSTRING(str, pos, len)
  3. SUBSTRING(str FROM pos)
  4. SUBSTRING(str FROM pos FOR len)

Description

It is the same as SUBSTR.

TRIM

Syntax

  1. TRIM([[{BOTH | LEADING | TRAILING}] [remstr] FROM] str)

Description

Remove prefixes, suffixes, or both from a string. The default is BOTH. If a parameter is NULL, the function returns NULL.

Example:

  1. OceanBase (root@test)> SELECT
  2. -> TRIM(' bar '),
  3. -> TRIM(LEADING 'x' FROM 'xxxbarxxx'),
  4. -> TRIM(BOTH 'x' FROM 'xxxbarxxx'),
  5. -> TRIM(TRAILING 'x' FROM 'xxxbarxxx')
  6. -> \G
  7. *************************** 1. row ***************************
  8. TRIM(' bar '): bar
  9. TRIM(LEADING 'x' FROM 'xxxbarxxx'): barxxx
  10. TRIM(BOTH 'x' FROM 'xxxbarxxx'): bar
  11. TRIM(TRAILING 'x' FROM 'xxxbarxxx'): xxxbar
  12. 1 row in set (0.01 sec)

LTRIM

Syntax

  1. LTRIM(str)

Description

Remove the spaces to the left of the string.

RTRIM

Syntax

  1. RTRIM(str)

Description

Remove the spaces to the right of the string.

ASCII

Syntax

  1. ASCII(str)

Description

Returns the ASCII code of the leftmost character of a string.

ORD

Syntax

  1. ORD(str)

Description

Returns the character code of the leftmost character of a string. If the leftmost character is a multibyte character, it uses the following rules to calculate the code:

  1. (1st byte code)
  2. + (2nd byte code * 256)
  3. + (3rd byte code * 256^2) ...

Example:

  1. OceanBase (root@test)> SELECT ORD('china');
  2. +---------------+
  3. | ORD('China') |
  4. +---------------+
  5. | 14989485 |
  6. +---------------+
  7. 1 row in set (0.01 sec)

LENGTH

Syntax

  1. LENGTH(str)

Description

Returns the length of the str in bytes.

Example:

  1. OceanBase (root@test)> SELECT LENGTH('China'), LENGTH('hello');
  2. +------------------+-----------------+
  3. | LENGTH('China') | LENGTH('hello') |
  4. +------------------+-----------------+
  5. | 6 | 5 |
  6. +------------------+-----------------+
  7. 1 row in set (0.01 sec)

CHAR_LENGTH

Syntax

  1. CHAR_LENGTH(str)

Description

Returns the number of characters in a string.

Example:

  1. OceanBase (root@test)> SELECT CHAR_LENGTH('China'), CHAR_LENGTH('hello');
  2. +-----------------------+----------------------+
  3. | CHAR_LENGTH('China') | CHAR_LENGTH('hello') |
  4. +-----------------------+----------------------+
  5. | 2 | 5 |
  6. +-----------------------+----------------------+
  7. 1 row in set (0.00 sec)

UPPER

Syntax

  1. UPPER(str)

Description

Converts lowercase letters in a string to uppercase letters.

Example:

  1. OceanBase (root@test)> SELECT UPPER('Hello, OceanBase!') ;
  2. +-----------------------------+
  3. | UPPER('Hello, OceanBase!') |
  4. +-----------------------------+
  5. | Hello, OCEANBASE! |
  6. +-----------------------------+
  7. 1 row in set (0.01 sec)

LOWER

Syntax

  1. LOWER(str)

Description

Converts uppercase letters in a string to lowercase letters.

Example:

  1. OceanBase (root@test)> SELECT LOWER('Hello, OceanBase!') ;
  2. +-----------------------------+
  3. | LOWER('Hello, OceanBase!') |
  4. +-----------------------------+
  5. | Hello, OceanBase! |
  6. +-----------------------------+
  7. 1 row in set (0.01 sec)

HEX

Syntax

  1. HEX(str)

Description

Converts a number or string into a hexadecimal string.

Example:

  1. OceanBase (root@test)> SELECT HEX(255), HEX('abc');
  2. +----------+------------+
  3. | HEX(255) | HEX('abc') |
  4. +----------+------------+
  5. | FF | 616263 |
  6. +----------+------------+
  7. 1 row in set (0.00 sec)

UNHEX

Syntax

  1. UNHEX(str)

Description

Converts a hexadecimal string to a normal string.

Example:

  1. OceanBase (root@test)> SELECT UNHEX('4f6365616e42617365');
  2. +-----------------------------+
  3. | UNHEX('4f6365616e42617365') |
  4. +-----------------------------+
  5. | OceanBase |
  6. +-----------------------------+
  7. 1 row in set (0.00 sec)

MD5

Syntax

  1. MD5(str)

Description

Returns the MD5 value of a string.

Example:

  1. OceanBase (root@test)> SELECT MD5(1);
  2. +----------------------------------+
  3. | MD5(1) |
  4. +----------------------------------+
  5. | c4ca4238a0b923820dcc509a6f75849b |
  6. +----------------------------------+
  7. 1 row in set (0.00 sec)

INT2IP

Syntax

  1. INT2IP(int_value)

Description

Converts an integer code to an IP address.

Example:

  1. OceanBase (root@test)> SELECT
  2. -> INT2IP(16777216),
  3. -> HEX(16777216),
  4. -> INT2IP(1)
  5. -> \G
  6. *************************** 1. row ***************************
  7. INT2IP(16777216): 1.0.0.0
  8. HEX(16777216): 1000000
  9. INT2IP(1): 0.0.0.1
  10. 1 row in set (0.01 sec)

IP2INT

Syntax

  1. IP2INT('ip_addr')

Description

Convert IP address to an integer code.

Example:

  1. OceanBase (root@test)> SELECT
  2. -> IP2INT('0.0.0.1'),
  3. -> HEX(IP2INT('0.0.0.1')),
  4. -> HEX(IP2INT('1.0.0.0'))
  5. -> \G
  6. *************************** 1. row ***************************
  7. IP2INT('0.0.0.1'): 1
  8. HEX(IP2INT('0.0.0.1')): 1
  9. HEX(IP2INT('1.0.0.0')): 1000000
  10. 1 row in set (0.01 sec)

LIKE

Syntax

  1. str1 [NOT] LIKE str2 [ESCAPE str3]

Description

Allows wildcards to be used for pattern matching. If a parameter is NULL, the function returns NULL.

Wildcards include:

  • %: matches a string of any length.

  • _: matches a single character.

ESCAPE is used to define the escape character. If str3 is included in str2, the characters after str3 will be treated as ordinary characters instead of wildcards during matching.

Example:

  1. OceanBase (root@test)> SELECT 'ab%' LIKE 'abc%' ESCAPE 'c';
  2. +------------------------------+
  3. | 'ab%' LIKE 'abc%' ESCAPE 'c' |
  4. +------------------------------+
  5. | 1 |
  6. +------------------------------+
  7. 1 row in set (0.01 sec)

REGEXP

Syntax

  1. str [NOT] REGEXP | RLIKE pat

Description

Performs pattern matching with regular expressions. If a parameter is NULL, the function returns NULL.

Example:

  1. OceanBase (root@test)> SELECT
  2. -> 1234 REGEXP 1,
  3. -> 'hello' RLIKE 'h%'
  4. -> \G
  5. *************************** 1. row ***************************
  6. 1234 REGEXP 1: 1
  7. 'hello' RLIKE 'h%': 0
  8. 1 row in set (0.01 sec)

REPEAT

Syntax

  1. REPEAT(str, count)

Description

Returns a string by repeating str for count times. If count is less than or equal to 0, an empty string is returned. If a parameter is NULL, the function returns NULL.

Example:

  1. OceanBase (root@test)> SELECT
  2. -> REPEAT('1', -1),
  3. -> REPEAT(null, null),
  4. -> REPEAT('Abc', 4)
  5. -> \G
  6. *************************** 1. row ***************************
  7. REPEAT('1', -1):
  8. REPEAT(null, null): NULL
  9. REPEAT('Abc', 4): AbcAbcAbcAbc
  10. 1 row in set (0.01 sec)

SPACE

Syntax

  1. SPACE(N)

Description

Returns a string containing N spaces.

SUBSTRING_INDEX

Syntax

  1. SUBSTRING_INDEX(str, delim, count)

Description

Returns the substring from the string str before count occurrences of the delimiter delim. If count is positive, it returns everything to the left of the final delimiter (starting from the left). If count is negative, it returns everything to the right of the delimiter (starting from the right). If one of the parameters is NULL, it returns NULL. If str or delim is an empty string, it returns an empty string. If count = 0, it returns an empty string.

str, delim, and count parameters support implicit conversion between numbers and strings.

Example:

  1. Oceanbase>select substring_index('abcdabc', 'abc', 0), substring_index('abcdabc', 'abc', 1), substring_index('abcdabc', 'abc', 2), substring_index('abcdabc', 'abc', 3), substring_index('abcdabc', 'abc', -1), substring_index('abcdabc', 'abc', -2), substring_index('abcdabc', 'abc', -3)\G;
  2. * 1. row *
  3. substring_index('abcdabc', 'abc', 0):
  4. substring_index('abcdabc', 'abc', 1):
  5. substring_index('abcdabc', 'abc', 2): abcd
  6. substring_index('abcdabc', 'abc', 3): abcdabc
  7. substring_index('abcdabc', 'abc', -1):
  8. substring_index('abcdabc', 'abc', -2): dabc
  9. substring_index('abcdabc', 'abc', -3): abcdabc
  10. 1 row in set (0.00 sec)

LOCATE

Syntax

LOCATE(substr,str) , LOCATE(substr,str,pos)

Description

The first syntax returns the first occurrence of the substring substr in str. The second syntax returns the first occurrence of the substring substr in str, starting at pos. If substr is not in str, the return value is 0.

Example:

  1. Oceanbase>SELECT LOCATE('bar', 'foobarbar');
  2. -> 4
  3. Oceanbase>SELECT LOCATE('xbar', 'foobar');
  4. -> 0
  5. Oceanbase>SELECT LOCATE('bar', 'foobarbar',5);
  6. -> 7

POSITION

Syntax

POSITION(substr IN str)

Description

It is the same as the LOCATE() function.

INSTR

Syntax

INSTR(str,substr)

Description

Returns the position of the first occurrence of the substring in str. This is the same as the LOCATE() function supplied with two parameters, except that the order of the parameters is reversed.

Example:

  1. Oceanbase>SELECT INSTR('foobarbar', 'bar');
  2. -> 4
  3. Oceanbase>SELECT INSTR('xbar', 'foobar');
  4. -> 0

REPLACE

Syntax

REPLACE(str, from_str, to_str)

Description

Returns the str string after all occurrences of from_str are replaced by to_str.

Example:

  1. Oceanbase>SELECT REPLACE('abc.efg.gpg.nowdew.abc.dabc.e', 'abc.', 'www');
  2. +---------------------------------------------------------+
  3. | REPLACE('abc.efg.gpg.nowdew.abc.dabc.e', 'abc.', 'www') |
  4. +---------------------------------------------------------+
  5. | wwwefg.gpg.nowdew.wwwdwwwe |
  6. +---------------------------------------------------------+
  7. 1 row in set (0.00 sec)

FIELD

Syntax

FIELD(str,str1,str2,str3,...)

Description

Returns the index position of the str string in the list of stringsstr1, str2, str3,… starting from 1. If str is not found, the return value is 0.

When all parameters of the FIELD() function are strings, they are compared as strings. If all parameters are numbers, they are compared as numbers. Otherwise, all are compared as double.

The function returns 0 if str is NULL, because NULL cannot be compared with any value. FIELD() is the complement of ELT().

Example:

  1. Oceanbase>select field('abc','abc1','abc2','abc','abc4','abc'), field(NULL, 'null1', NULL);
  2. +-----------------------------------------------+----------------------------+
  3. | field('abc','abc1','abc2','abc','abc4','abc') | field(NULL, 'null1', NULL) |
  4. +-----------------------------------------------+----------------------------+
  5. | 3 | 0 |
  6. +-----------------------------------------------+----------------------------+
  7. 1 row in set (0.00 sec)

ELT

Syntax

ELT(N, str1, str2, str3,...)

Description

Returns the Nth string of the list. For example, if N = 1, the return value is str1 and if N = 2, the return value isstr2. If N is less than 1 or greater than the number of the parameters, the return value is NULL. ELT() is the complement of FIELD().

Example:

  1. Oceanbase>select elt(3, 'abc1', 'abc2', 'abc', 'abc4', 'abc'), elt(0, 'null1', NULL);
  2. +----------------------------------------------+-----------------------+
  3. | elt(3, 'abc1', 'abc2', 'abc', 'abc4', 'abc') | elt(0, 'null1', NULL) |
  4. +----------------------------------------------+-----------------------+
  5. | abc | NULL |
  6. +----------------------------------------------+-----------------------+
  7. 1 row in set (0.00 sec)

INSERT

Syntax

INSERT (str1,pos,len,str2)

Description

Replaces the substring of length len starting at the pos position of the str1 with str2. The final string is returned. If pos is greater than the string length, the return value is the original string. If the length len is greater than the length of other strings, the replacement starts from position pos. If one of the parameters is NULL, the return value is NULL. This function supports multibyte characters.

  • str1 and str2 must be a string. pos and len must be an integer. If any parameter is NULL, the return value is NULL.

  • Text characters in str1 and str2 are treated as byte streams.

  • If pos is negative or greater than the length of str1, the function returns str1.

  • When len is less than 0 or greater than the length of str1, the result is a substring that starts from the beginning to the pos position of str1, and then appended with str2.

Example:

  1. Oceanbase>select insert('Quadratic',-2,100,'What'), insert('Quadratic',7,3,'What'),
  2. -> insert('Quadratic',-1,3,'What'), insert('Quadratic',10,3,'What'), insert('Quadratic',5,-1,''),
  3. -> insert('Quadratic',7,-1,'What')\G;
  4. * 1. row *
  5. insert('Quadratic',-2,100,'What'): Quadratic
  6. insert('Quadratic',7,3,'What'): QuadraWhat
  7. insert('Quadratic',-1,3,'What'): Quadratic
  8. insert('Quadratic',10,3,'What'): Quadratic
  9. insert('Quadratic',5,-1,''): Quad
  10. insert('Quadratic',7,-1,'What'): QuadraWhat
  11. 1 row in set (0.01 sec)

LPAD

Syntax

LPAD(str, len, padstr)

Description

Adds the padstr padding string to the left of the str string until the final string reaches length len. If str is longer than len, str is truncated.

Example:

  1. OceanBase > SELECT LPAD('hi',4,'??');
  2. +-------------------+
  3. | LPAD('hi',4,'??') |
  4. +-------------------+
  5. | ??hi |
  6. +-------------------+
  7. 1 row in set (0.01 sec)
  8. OceanBase > SELECT LPAD('hi',1,'??');
  9. +-------------------+
  10. | LPAD('hi',1,'??') |
  11. +-------------------+
  12. | h |
  13. +-------------------+
  14. 1 row in set (0.00 sec)

RPAD

Syntax

RPAD(str, len, padstr)

Description

Adds the padstr padding string to the right of the str string until the final string reaches length len. If str is longer than len, str is truncated.

Example:

  1. OceanBase (root@test)> SELECT RPAD('hi',4,'??');
  2. +-------------------+
  3. | RPAD('hi',4,'??') |
  4. +-------------------+
  5. | hi?? |
  6. +-------------------+
  7. 1 row in set (0.00 sec)
  8. OceanBase (root@test)> SELECT RPAD('hi',1,'??');
  9. +-------------------+
  10. | RPAD('hi',1,'??') |
  11. +-------------------+
  12. | h |
  13. +-------------------+
  14. 1 row in set (0.00 sec)

UUID

Syntax

uuid()

Description

Generates globally unique identifiers (IDs).

Example:

  1. OceanBase (root@test)> select uuid();
  2. +--------------------------------------+
  3. | uuid() |
  4. +--------------------------------------+
  5. | f756a1f6-4de6-11e8-90af-90b11c53e421 |
  6. +--------------------------------------+
  7. 1 row in set (0.00 sec)

BIN

Syntax

bin(N)

Description

Returns the binary form of the number N.

Example:

  1. OceanBase > SELECT BIN(12);
  2. +---------+
  3. | BIN(12) |
  4. +---------+
  5. | 1100 |
  6. +---------+
  7. 1 row in set (0.00 sec)

QUOTE

Syntax

quote(str)

Description

Produces a string which is a properly escaped data value in an SQL statement based on the string str. The final string is enclosed by single quotation marks (‘), and each single quotation marks (‘), backslash (), ASCII NUL and Ctrl+Z is preceded with a backslash (). If the string is NULL, the function returns a word NULL that is not enclosed by single quotation marks(‘).

Example:

  1. OceanBase > SELECT QUOTE('Don\'t!');
  2. +------------------+
  3. | QUOTE('Don\'t!') |
  4. +------------------+
  5. | 'Don\'t!' |
  6. +------------------+
  7. 1 row in set (0.00 sec)
  8. OceanBase > SELECT QUOTE(NULL);
  9. +-------------+
  10. | QUOTE(NULL) |
  11. +-------------+
  12. | NULL |
  13. +-------------+
  14. 1 row in set (0.00 sec)

REGEXP_SUBSTR

Syntax

regexp_substr(str,pattern,[position[,occurrence[,match_param[,subexpr]]]])

Description

Searches for a substring that matches the regular expression pattern in str. If the substring does not exist, it returns NULL. This function supports multibyte characters. Except for match_param, if any other parameter is NULL, the result is NULL.

  • str is the string to be searched. Multibyte characters are supported.

  • pattern is the regular expression, and the regular expression rules are compatible with MySQL.

  • position is an [optional] parameter to specify where to begin the search. The position parameter must be a positive integer. An error is reported if it is less than or equal to 0. If the input is NULL, then the search returns NULL. The default value for the position is 1, which means that the search starts from the first character.

  • occurrence is an [optional] parameter that returns the number of times the string matches the regular expression. The occurrence parameter must be an integer greater than 0. An error is reported if it is less than 0. If the input is NULL, then the search returns NULL. The default value for the position is 1, which means that ApsaraDB for OceanBase searches for the first occurrence of the pattern.

  • match_param is an [optional] parameter that specifies the string type. Only supports two characters ‘i’ and ‘c’. ‘i’ specifies case-insensitive matching. ‘c’ indicates case-sensitive matching. If you specify other characters, an error occurs. The default value is determined by the character set of str. The default value is used if match_param is NULL.

  • subexpr is an [optional] parameter. It specifies which group of the regular expression is used to match the string. subexpr is must be an integer greater than or equal to 0. An error is reported if it is less than 0. The default value is 0, which means that returns a substring that satisfies the entire pattern.

Example:

  1. oceanbase> select regexp_substr('I have 2 apples and 100 bucks!', '[[:blank:]][[:alnum:]]*', 1, 1) from dual;
  2. +----------------------------------------------------------------------------------+
  3. | regexp_substr('I have 2 apples and 100 bucks!', '[[:blank:]][[:alnum:]]*', 1, 1) |
  4. +----------------------------------------------------------------------------------+
  5. | have |
  6. +----------------------------------------------------------------------------------+
  7. 1 row in set (0.00 sec)
  8. oceanbase> select regexp_substr('foothebar', 'foo(.*)(bar)', 1, 1, 'c', 1) from dual;
  9. +----------------------------------------------------------+
  10. | regexp_substr('foothebar', 'foo(.*)(bar)', 1, 1, 'c', 1) |
  11. +----------------------------------------------------------+
  12. | the |
  13. +----------------------------------------------------------+
  14. 1 row in set (0.01 sec)

Conversion functions

CAST

Syntax

CAST(expr AS type)

Description

Explicitly converts an expression of one data type to another data type.

Converts the value of expr to a new type.

Parameter description:

  • expr can be any valid SQL expression.
  • AS is used to separate two parameters. The data to be processed comes before AS, and the target data type comes after AS.
  • type must be a valid data type supported by ApsaraDB for OceanBase. Valid values are as follows:

  • CHAR[(N)] (CHAR[N] makes sure CAST() uses no more than N characters of this parameter)

  • DATE

  • DATETIME

  • DECIMAL

  • SIGNED [INTEGER]

  • TIME

  • UNSIGNED [INTEGER]

The CAST function works when one of the following conditions is met:

  • The data types of the two expressions are the same.

  • Two expressions can be implicitly converted.

  • Two expressions must be explicitly converted.

If an attempt is made to perform an impossible conversion, ApsaraDB for OceanBase displays an error message.

If the length of the data type is not specified, ApsaraDB for OceanBase returns the maximum length supported by the internal system. For example, the length for varchar is 262,143 bytes, and the length for numeric is 65 bits of floating-point precision.

You can use the CAST function for computations of signed and unsigned 64-bit values. If you are using a numeric operator such as a plus sign (+) and one of the operands is an unsigned integer, the result is unsigned. Numeric operators can be overridden using the SIGNED or UNSIGNED cast operators on signed or unsigned 64-bit integers.

If one operand is a floating-point value, the result is a floating-point value.

Example:

  1. Oceanbase>SELECT CAST(123 AS BOOL);
  2. +-------------------+
  3. | CAST(123 AS bool) |
  4. +-------------------+
  5. | 1 |
  6. +-------------------+
  7. 1 row in set (0.00 sec)
  8. Oceanbase>select cast(1-2 as unsigned), cast(cast(1-2 as unsigned) as signed);
  9. +-----------------------+---------------------------------------+
  10. | cast(1-2 as unsigned) | cast(cast(1-2 as unsigned) as signed) |
  11. +-----------------------+---------------------------------------+
  12. | 18446744073709551615 | -1 |
  13. +-----------------------+---------------------------------------+
  14. 1 row in set (0.00 sec)
  15. Oceanbase>SELECT CAST(1 AS UNSIGNED) - 2.0;
  16. +---------------------------+
  17. | CAST(1 AS UNSIGNED) - 2.0 |
  18. +---------------------------+
  19. | -1.0 |
  20. +---------------------------+
  21. 1 row in set (0.00 sec)
  22. Oceanbase>select cast(0 as date);
  23. +-----------------+
  24. | cast(0 as date) |
  25. +-----------------+
  26. | 0000-00-00 |
  27. +-----------------+
  28. 1 row in set (0.00 sec)

Mathematical functions

ROUND

Syntax

ROUND(X), ROUND(X,D)

Description

Returns a number that is rounded to the specified length or precision.

Returns the parameter X, whose value is equal to the nearest integer. In the case of two parameters, X is returned and its value is retained to D digits after the decimal point. The Dth digit is obtained by rounding. To keep D digits of the X value to the left of the decimal point, you can set D to a negative value.

The type of the return value is the same as the first parameter (assuming it is an integer, double, or decimal). This means that for an integer parameter, the result is also an integer with no fractional part.

  • For exact-valued numbers, ROUND() uses the rules of rounding up or rounding to the nearest number. A value with a fractional part of .5 or greater is rounded up to the next integer if positive or down to the next integer if negative. In other words, the value is rounded away from zero on the number axis. A value with a fractional part less than .5 is rounded down to the next integer if positive or up to the next integer if negative.

  • For approximate numbers, ROUND() follows the banker’s rule. A value with a fractional part exactly halfway between two integers is rounded to the nearest even integer.

Example:

  1. Oceanbase>select round(2.15,2);
  2. +---------------+
  3. | round(2.15,2) |
  4. +---------------+
  5. | 2.15 |
  6. +---------------+
  7. 1 row in set (0.00 sec)
  8. Oceanbase>select round(2555e-2,1);
  9. +------------------+
  10. | round(2555e-2,1) |
  11. +------------------+
  12. | 25.6 |
  13. +------------------+
  14. 1 row in set (0.01 sec)
  15. Oceanbase>select round(25e-1), round(25.3e-1),round(35e-1);
  16. +--------------+----------------+--------------+
  17. | round(25e-1) | round(25.3e-1) | round(35e-1) |
  18. +--------------+----------------+--------------+
  19. | 3 | 3 | 4 |
  20. +--------------+----------------+--------------+
  21. 1 row in set (0.00 sec)

CEIL

Syntax

CEIL(expr)

Description

Returns the smallest integer value that is no less than the specified expression.

The function supports comparison operations. The comparison result, which is a BOOL value, is converted to a numeric, 1 (TRUE) or 0 (FALSE).

If expr is NULL, NULL is returned.

If you enter a string of numbers, implicit conversion to numeric types is supported.

The return value is converted to a BIGINT.

Example:

  1. Oceanbase>select ceil(1.2), ceil(-1.2), ceil(1+1.5), ceil(1=1),ceil(1<1),ceil(null);
  2. +-----------+------------+-------------+-----------+-----------+------------+
  3. | ceil(1.2) | ceil(-1.2) | ceil(1+1.5) | ceil(1=1) | ceil(1<1) | ceil(null) |
  4. +-----------+------------+-------------+-----------+-----------+------------+
  5. | 2 | -1 | 3 | 1 | 0 | NULL |
  6. +-----------+------------+-------------+-----------+-----------+------------+
  7. 1 row in set (0.00 sec)
  8. Oceanbase>select ceil(name);
  9. ERROR 1166 (42703): Unkown column name 'name'
  10. Oceanbase>select ceil('2');
  11. +-----------+
  12. | ceil('2') |
  13. +-----------+
  14. | 2 |
  15. +-----------+
  16. 1 row in set (0.00 sec)

FLOOR

Syntax

FLOOR(expr)

Description

Complements the CEIL(expr) function and returns the largest integer value that is no greater than the specified expression.

The function supports comparison operations. The comparison result, which is a BOOL value, is converted to a numeric, 1 (TRUE) or 0 (FALSE).

If expr is NULL, NULL is returned.

If you enter a string of numbers, implicit conversion to numeric types is supported.

The return value is converted to a BIGINT.

Example:

  1. Oceanbase>select floor(1.2), floor(-1.2), floor(1+1.5), floor(1=1),floor(1<1),floor(null);
  2. +------------+-------------+--------------+------------+------------+-------------+
  3. | floor(1.2) | floor(-1.2) | floor(1+1.5) | floor(1=1) | floor(1<1) | floor(null) |
  4. +------------+-------------+--------------+------------+------------+-------------+
  5. | 1 | -2 | 2 | 1 | 0 | NULL |
  6. +------------+-------------+--------------+------------+------------+-------------+
  7. 1 row in set (0.00 sec)
  8. Oceanbase>select floor(name);
  9. ERROR 1166 (42703): Unkown column name 'name'
  10. Oceanbase>select floor('2');
  11. +------------+
  12. | floor('2') |
  13. +------------+
  14. | 2 |
  15. +------------+
  16. 1 row in set (0.00 sec)

ABS

Syntax

ABS(expr)

Description

Finds the absolute value of an expression. The return value type of the function is the same as the data type of the numeric expression.

The function supports comparison operations. The comparison result, which is a BOOL value, is converted to a numeric, 1 (TRUE) or 0 (FALSE).

If expr is NULL, NULL is returned.

If you enter a string of numbers, implicit conversion to numeric types is supported.

The return value is converted to a BIGINT.

Example:

  1. Oceanbase>select abs(5), abs(-5.777), abs(0), abs(1/2), abs(1-5);
  2. +--------+-------------+--------+----------+----------+
  3. | abs(5) | abs(-5.777) | abs(0) | abs(1/2) | abs(1-5) |
  4. +--------+-------------+--------+----------+----------+
  5. | 5 | 5.777 | 0 | 0.5000 | 4 |
  6. +--------+-------------+--------+----------+----------+
  7. 1 row in set (0.00 sec)

NEG

Syntax

NEG(expr)

Description

A negation function that subtracts the operand from zero, and returns the final result.

The function supports comparison operations. The comparison result, which is a BOOL value, is converted to 1 (TRUE) or 0 (FALSE) of the numeric type and then negated.

Example:

  1. Oceanbase>select neg(1), neg(1+1), neg(2*3), neg(1=1), neg(5<1);
  2. +--------+----------+----------+----------+----------+
  3. | neg(1) | neg(1+1) | neg(2*3) | neg(1=1) | neg(5<1) |
  4. +--------+----------+----------+----------+----------+
  5. | -1 | -2 | -6 | -1 | 0 |
  6. +--------+----------+----------+----------+----------+
  7. 1 row in set (0.01 sec)

SIGN

Syntax

SIGN(X)

Description

Returns one of -1, 0, or 1, depending on whether the value of X is negative, zero, or positive.

The function supports comparison operations. The comparison result, which is a BOOL value, is converted to 1 (TRUE) or 0 (FALSE) of the numeric type.

If expr is NULL, NULL is returned.

The function supports floating-point numbers and hexadecimal numbers.

Example:

  1. Oceanbase>SELECT SIGN(-32), SIGN(0), SIGN(234);
  2. +-----------+---------+-----------+
  3. | SIGN(-32) | SIGN(0) | SIGN(234) |
  4. +-----------+---------+-----------+
  5. | -1 | 0 | 1 |
  6. +-----------+---------+-----------+
  7. 1 row in set (0.01 sec)
  8. Oceanbase>select sign(null),sign(false),sign(0x01);
  9. +------------+-------------+------------+
  10. | sign(null) | sign(false) | sign(0x01) |
  11. +------------+-------------+------------+
  12. | NULL | 0 | 1 |
  13. +------------+-------------+------------+
  14. 1 row in set (0.00 sec)

CONV

Syntax

CONV(N, from_base, to_base)

Description

Convert numbers between different number bases. Returns a string representation of the number N, converted from base from_base to base to_base. The input parameter N can be an integer or a string. The minimum base supported is 2 and the maximum base supported is 36. If to_base is a negative number, then N is treated as a signed number. Otherwise, N is treated as an unsigned number. from_base is treated as an integer if it is negative, and the sign is ignored. The N parameter must be an integer or string. The from_base and to_base parameters must be decimal integers that are within the range of [-36, -2] U [2, 36].

Invalid inputs result in an error. The invalid inputs include the following:

  • from_base or to_base is not a valid decimal integer.

  • from_base or to_base is not in the value range of [-36,-2] U [2,36].

  • N is not a valid numeric representation. For example, the value exceeds the character range of 0 to 9, a to z, or A to Z.

  • N is outside the range of the frombase base. For example, _from_base is 2, but N is 3.

  • N exceeds the maximum representation range of int64, which is [-9223372036854775807, 9223372036854775807].

Example:

  1. Oceanbase>select conv(9223372036854775807,10,2);
  2. +-----------------------------------------------------------------+
  3. | conv(9223372036854775807,10,2) |
  4. +-----------------------------------------------------------------+
  5. | 111111111111111111111111111111111111111111111111111111111111111 |
  6. +-----------------------------------------------------------------+
  7. 1 row in set (0.00 sec)
  8. Oceanbase>select conv('-acc',21,-7);
  9. +--------------------+
  10. | conv('-acc',21,-7) |
  11. +--------------------+
  12. | -16425 |
  13. +--------------------+
  14. 1 row in set (0.00 sec)

MOD

Syntax

MOD(N,M)

Description

Returns the remainder. MOD(N, M), N%M, N MOD M are equivalent.

MOD() also works for numbers with a fractional part, it returns the exact remainder after division.

If one of N, M is NULL, the return value is NULL. If M is 0, the return value is NULL.

Example:

  1. Oceanbase>select mod(29,19), 29 mod 19, 29 % 19;
  2. +------------+-----------+---------+
  3. | mod(29,19) | 29 mod 19 | 29 % 19 |
  4. +------------+-----------+---------+
  5. | 10 | 10 | 10 |
  6. +------------+-----------+---------+
  7. 1 row in set (0.00 sec)

POW

Syntax

POW(X,Y)

Description

Returns X to the power of Y.

If one of X and Y is NULL, the return value is NULL.

Example:

  1. Oceanbase>select pow(4,2), pow(4,-2), pow(1,null);
  2. +----------+-----------+-------------+
  3. | pow(4,2) | pow(4,-2) | pow(1,null) |
  4. +----------+-----------+-------------+
  5. | 16 | 0.0625 | NULL |
  6. +----------+-----------+-------------+
  7. 1 row in set (0.00 sec)

POWER

Syntax

POWER(X,Y)

Description

POWER(X,Y) and POW ( X,Y) are identical functions.

RAND

Syntax

rand([N])

Description

The RAND([N]) function accepts zero or one parameter and returns a random floating-point number in the range of [0,1.0). N is called a random number seed. If you want to obtain a random integer in the range in [I j), you can use the FLOOR(I + RAND() * (j - i)) expression.

If N is not specified, a random seed is generated during initialization. RAND() generates a random number based on this random seed. Therefore, the random number generated by RAND() changes each time.

If N is specified, then N is used as the seed to generate random numbers. Two cases are possible, depending on whether N is a constant.

If N is a constant, N is used at the random seed during initialization and RAND(N) generates random numbers based on the value of N. The same values of N generate the same random number sequence.

If N is a variable (such as a column name), the seed is initialized with N for each invocation of RAND() to generate a random number. Same values of N generate the same random number.

Besides SELECT statements, the RAND([N]) function can also be used in WHERE, ORDER BY, and GROUP BY statements. In all cases, the function is executed the same way as described in the preceding rules. For example, if you want to randomly sort a table, you can use the following SQL query: select _ from t1 order by rand(). If you want to randomly sample 100 rows of a table, you can use the following SQL query: select _ from t1 order by rand() limit 100.

Example:

  1. mysql> select a, b, rand() from t3;
  2. +------+------+---------------------+
  3. | a | b | rand() |
  4. +------+------+---------------------+
  5. | 1 | 1 | 0.641815407799385 |
  6. | 2 | 2 | 0.16825051248841966 |
  7. | 3 | 3 | 0.9158063697775886 |
  8. +------+------+---------------------+
  9. 3 rows in set (0.00 sec)
  10. mysql> select a, b, rand() from t3;
  11. +------+------+---------------------+
  12. | a | b | rand() |
  13. +------+------+---------------------+
  14. | 1 | 1 | 0.07428034215632857 |
  15. | 2 | 2 | 0.6239826321825224 |
  16. | 3 | 3 | 0.897072165177271 |
  17. +------+------+---------------------+
  18. 3 rows in set (0.00 sec)
  19. mysql> select a, b, rand(3) from t3;
  20. +------+------+---------------------+
  21. | a | b | rand(3) |
  22. +------+------+---------------------+
  23. | 1 | 1 | 0.9057697559760601 |
  24. | 2 | 2 | 0.37307905813034536 |
  25. | 3 | 3 | 0.14808605345719125 |
  26. +------+------+---------------------+
  27. 3 rows in set (0.00 sec)
  28. mysql> select a, b, rand(3) from t3;
  29. +------+------+---------------------+
  30. | a | b | rand(3) |
  31. +------+------+---------------------+
  32. | 1 | 1 | 0.9057697559760601 |
  33. | 2 | 2 | 0.37307905813034536 |
  34. | 3 | 3 | 0.14808605345719125 |
  35. +------+------+---------------------+
  36. 3 rows in set (0.00 sec)
  37. mysql> select a, b, rand(a), rand(b) from t3;
  38. +------+------+---------------------+---------------------+
  39. | a | b | rand(a) | rand(b) |
  40. +------+------+---------------------+---------------------+
  41. | 1 | 1 | 0.40540353712197724 | 0.40540353712197724 |
  42. | 2 | 2 | 0.6555866465490187 | 0.6555866465490187 |
  43. | 3 | 3 | 0.9057697559760601 | 0.9057697559760601 |
  44. +------+------+---------------------+---------------------+
  45. 3 rows in set (0.00 sec)

Comparison functions

GREATEST

Syntax

GREATEST(value1, ...)

Description

Returns the maximum value of the parameter, opposite of the LEAST() function.

You must input at least two parameters. The function generates an error with only one input parameter. If one of the parameters is NULL, the return value is NULL.

If the parameters comprise a mix of numbers and strings, they are compared as numbers. If the implicit conversion fails, an error is generated.

Example:

  1. Oceanbase>select greatest(2,1), greatest('2',1,0), greatest('a','b','c'), greatest('a', NULL, 'c'), greatest('2014-05-15','2014-06-01')\G
  2. * 1. row *
  3. greatest(2,1): 2
  4. greatest('2',1,0): 2
  5. greatest('a','b','c'): c
  6. greatest('a', NULL, 'c'): NULL
  7. greatest('2014-05-15','2014-06-01'): 2014-06-01
  8. 1 row in set (0.01 sec)
  9. Oceanbase>select greatest(2);
  10. ERROR 1582 (42000): Incorrect parameter count in the call to native function 'greatest'

LEAST

Syntax

LEAST(value1, ...)

Description

Returns the minimum value of the parameter, opposite of the function GREATEST().

You must input at least two parameters. If one of the parameters is NULL, the return value is NULL.

If the parameters comprise a mix of numbers and strings, they are compared as numbers. If the implicit conversion fails, an error is generated.

Example:

  1. Oceanbase>select least(2, null), least('2',4,9), least('a','b','c'), least('a',NULL,'c'), least('2014-05-15','2014-06-01')\G;
  2. * 1. row *
  3. least(2, null): NULL
  4. least('2',4,9): 2
  5. least('a','b','c'): a
  6. least('a',NULL,'c'): NULL
  7. least('2014-05-15','2014-06-01'): 2014-05-15
  8. 1 row in set (0.01 sec)
  9. Oceanbase>select least(2);
  10. ERROR 1582 (42000): Incorrect parameter count in the call to native function 'least'

ISNULL

Syntax

ISNULL(expr)

Description

Returns NULL, if the parameter expr is NULL. Otherwise, the function returns 0.

The ISNULL() function can be used instead of an equal sign (=) to check if an expression is NULL. ( Using an equal sign to check if an expression is NULL often generates a wrong result.)

The ISNULL() function shares some special behaviors with the IS NULL operator.

Example:

  1. Oceanbase>SELECT ISNULL(null), ISNULL('test'), ISNULL(123.456), ISNULL('10:00');
  2. +--------------+----------------+-----------------+-----------------+
  3. | ISNULL(null) | ISNULL('test') | ISNULL(123.456) | ISNULL('10:00') |
  4. +--------------+----------------+-----------------+-----------------+
  5. | 1 | 0 | 0 | 0 |
  6. +--------------+----------------+-----------------+-----------------+
  7. 1 row in set (0.01 sec)
  8. Oceanbase>SELECT ISNULL(null+1);
  9. +----------------+
  10. | ISNULL(null+1) |
  11. +----------------+
  12. | 1 |
  13. +----------------+
  14. 1 row in set (0.00 sec)

Control flow functions

CASE

Syntax

  1. CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END
  2. OR
  3. CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

Description

For the first case, returns the value that is equal to compare-value. For the second case, returns the first value whose preceding condition is true. If there is no matching result value, the result is the ELSE part. If there is no ELSE part, the return value is NULL.

Example:

  1. Oceanbase>select CASE 'b' when 'a' then 1 when 'b' then 2 END;
  2. +----------------------------------------------+
  3. | CASE 'b' when 'a' then 1 when 'b' then 2 END |
  4. +----------------------------------------------+
  5. | 2 |
  6. +----------------------------------------------+
  7. 1 row in set (0.01 sec)
  8. Oceanbase>select CASE concat('a','b') when concat('ab','') then 'a' when 'b' then 'b' end;
  9. +--------------------------------------------------------------------------+
  10. | CASE concat('a','b') when concat('ab','') then 'a' when 'b' then 'b' end |
  11. +--------------------------------------------------------------------------+
  12. | a |
  13. +--------------------------------------------------------------------------+
  14. 1 row in set (0.01 sec)
  15. Oceanbase>select case when 1>0 then 'true' else 'false' end;
  16. +--------------------------------------------+
  17. | case when 1>0 then 'true' else 'false' end |
  18. +--------------------------------------------+
  19. | true |
  20. +--------------------------------------------+
  21. 1 row in set (0.00 sec)

IF

Syntax

IF(expr1,expr2,expr3)

Description

Returns the value of expr2 if the value of expr1 is TRUE (expr1<>0 and expr1<>NULL). Otherwise, the function returns expr3.

The IF() function returns a numeric or a string, depending on how it is used.

If one of expr2 and expr3 is NULL, the return type of the IF() function is the same as the non-NULL expression.

Example:

  1. Oceanbase>select if(5>6, 'T','F'), if (5>6, 1, 0), if(null, 'True', 'False'), if(0, 'True', 'False')\G
  2. * 1. row *
  3. if(5>6, 'T','F'): F
  4. if (5>6, 1, 0): 0
  5. if(null, 'True', 'False'): False
  6. if(0, 'True', 'False'): False
  7. 1 row in set (0.01 sec)

IFNULL

Syntax

IFNULL(expr1, expr2)

Description

Returns expr1 if expr1 is not NULL. Otherwise, the function returns expr2. IFNULL() returns a numeric or a string, depending on how it is used.

The default return types of the IF() function is based on the following rules.

Expression Return values
expr1 or expr2 returns a string. String
expr1 or expr2 returns a floating-point value. Float
expr1 or expr2 returns an integer. Integer

When expr1 and expr2 are strings, the result is case-sensitive if either string is case-sensitive.

Example:

  1. Oceanbase>SELECT IFNULL('abc', null), IFNULL(NULL+1, NULL+2), IFNULL(1/0, 0/1);
  2. +---------------------+------------------------+------------------+
  3. | IFNULL('abc', null) | IFNULL(NULL+1, NULL+2) | IFNULL(1/0, 0/1) |
  4. +---------------------+------------------------+------------------+
  5. | abc | NULL | 0.0000 |
  6. +---------------------+------------------------+------------------+
  7. 1 row in set (0.01 sec)

NULLIF

Syntax

NULLIF(expr1, expr2)

Description

Returns NULL, if expr1 = expr2. Otherwise, the function returns expr1. This is equivalent to CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.
ApsaraDB for OceanBase evaluates expr1 twice if expr1 and expr2 are not equal.

Example:

  1. Oceanbase>SELECT NULLIF('ABC', 123), NULLIF('123',123), NULLIF(NULL, 'abc');
  2. +--------------------+-------------------+---------------------+
  3. | NULLIF('ABC', 123) | NULLIF('123',123) | NULLIF(NULL, 'abc') |
  4. +--------------------+-------------------+---------------------+
  5. | ABC | NULL | NULL |
  6. +--------------------+-------------------+---------------------+
  7. 1 row in set, 1 warning (0.01 sec)

ORA_DECODE

Syntax

ora_decode (condition, value 1, return value 1, value 2, return value 2, ... value n, return value n, default value)

Description

The ORADECODE() function is equivalent to the DECODE() function provided by Oracle. ApsaraDB for OceanBase 1.0 is compatible with MySQL, and some Oracle functions are also added. The names of the added Oracle functions are prefixed with ORA.

The meaning of the function is as follows:

  1. IF condition = value 1
  2. THEN RETURN (return value 1)
  3. ELSIF condition = value 2
  4. THEN RETURN (return value 2)
  5. ......
  6. ELSIF condition = value n
  7. THEN RETURN (return value n)
  8. ELSE RETURN (default)
  9. END IF