The following mathematical functions are supported:
BIN_TO_NUM
BITAND
BITOR
BITXOR
COSH
MOD
REMAINDER
ROUND_TIES_TO_EVEN
SINH
TANH
Description
BIN_TO_NUM converts a bit vector to its equivalent number.
Syntax
BIN_TO_NUM(VARIADIC integer []) returns int
General rules
Each argument to this function represents a bit in the bit vector.
This function takes as arguments any integer data type, or any noninteger data type that can be implicitly converted to integer.
BIN_TO_NUM is useful in data warehousing applications for selecting groups of interest from a materialized view using grouping sets.
Note
If using BIN_TO_NUM for converting, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.
Example
SELECT BIN_TO_NUM(1,0,1,0) FROM DUAL;
bin_to_num
------------
10
(1 row)
Description
Performs a bitwise AND operation.
Syntax
BITAND(int1 integers, int2 integers) returns bigint
General rules
BITAND performs an AND operation on each bit of two integers, and returns the result.
Specify integer type values.
The data type of the return value is BIGINT.
Example
In the following example, the result of the AND operation on numeric literals 5 and 3 is returned.
SELECT BITAND(5,3) FROM DUAL;
bitand
-------
1
(1 row)
Description
Performs a bitwise OR operation.
Syntax
BITOR(int1 bigint, int2 bigint) returns bigint
General rules
BITOR performs an OR operation on each bit of two bigint, and returns the result.
Specify bigint type values.
The data type of the return value is BIGINT.
Note
If using BITOR, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.
Example
In the following example, the result of the OR operation on numeric literals 5 and 3 is returned.
SELECT BITOR(5,3) FROM DUAL;
bitor
-------
7
(1 row)
Description
Performs a bitwise XOR operation.
Syntax
BITXOR(int1 bigint, int2 bigint) returns bigint
General rules
BITXOR performs an XOR operation on each bit of two integers, and returns the result.
Specify bigint type values.
The data type of the return value is BIGINT.
Note
If using BITXOR, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.
Example
In the following example, the result of the XOR operation on numeric literals 5 and 3 is returned.
SELECT BITXOR(5,3) FROM DUAL;
bitxor
--------
6
(1 row
Description
Calculates the hyperbolic cosine of a number.
Syntax
COSH(num numeric) returns double precision
General rules
COSH returns the hyperbolic cosine of the specified number.
The number must be a numeric data type.
The data type of the return value is DOUBLE PRECISION.
Example
In the following example, the hyperbolic cosine of the numeric literal 2.236 is returned.
SELECT COSH(2.236) FROM DUAL;
cosh
-----------------
4.7313591000247
(1 row)
Description
MOD returns the remainder of n2 divided by n1. Returns n2 if n1 is 0.
Syntax
MOD(n2 bigint, n1 bigint) returns bigint
MOD(n2 integer, n1 integer) returns integer
MOD(n2 numeric, n1 numeric) returns numeric
MOD(n2 smallint, n1 smallint) returns smallint
General rules
This function takes as arguments any numeric datatype.
Note
It is not supported yet if one of the arguments is decimal number in string form and the other is an integer.
Example
SELECT mod(5,2) FROM DUAL;
mod
-----------
1
Description
Returns the remainder of n2 divided by n1.
Syntax
REMAINDER(n2 numeric, n1 numeric) returns numeric
General rules
This function takes as arguments any numeric data type or any nonnumeric data type that can be implicitly converted to a numeric data type.
The MOD function is similar to REMAINDER except that it uses FLOOR in its formula, whereas REMAINDER uses ROUND.
If n1 != 0, then the remainder is n2 - (n1*N) where N is the integer nearest n2/n1. If n2/n1 equals x.5, then N is the nearest even integer.
Note
If using REMAINDER, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.
Example
SELECT REMAINDER(5.5,2) FROM DUAL;
remainder
-----------
-0.5
(1 row)
Description
The function returns n rounded to integer places.
Syntax
ROUND_TIES_TO_EVEN(n NUMERIC,integer int4 DEFAULT 0) returns numeric
General rules
If integer is positive, n is rounded to integer places to the right of the decimal point.
If integer is not specified, then n is rounded to 0 places.
If integer is negative, then n is rounded to integer places to the left of the decimal point.
Note
If using ROUND_TIES_TO_EVEN, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.
Example
The following example rounds a number to one decimal point to the right:
SELECT ROUND_TIES_TO_EVEN(45.177,1) "ROUND_EVEN" FROM DUAL;
ROUND_EVEN
------------
45.2
(1 row)
The following example rounds a number to one decimal point to the left:
SELECT ROUND_TIES_TO_EVEN(45.177,-1) "ROUND_EVEN" FROM DUAL;
ROUND_EVEN
------------
50
(1 row)
Description
Calculates the hyperbolic sine of a number.
Syntax
SINH(num numeric) returns double precision
General rules
SINH returns the hyperbolic sine of the specified number.
The number must be a numeric data type.
The data type of the return value is DOUBLE PRECISION.
Example
In the following example, the hyperbolic sine of the numeric literal 1.414 is returned.
SELECT SINH(1.414) FROM DUAL;
sinh
-----------------
1.93460168824956
(1 row)
Description
Calculates the hyperbolic tangent of a number.
Syntax
TANH(num numeric) returns double precision
General rules
TANH returns the hyperbolic tangent of the specified number.
The number must be a numeric data type.
The data type of the return value is DOUBLE PRECISION.
Example
In the following example, the hyperbolic tangent of the numeric literal 3 is returned.
SELECT TANH(3) FROM DUAL;
tanh
-----------------
0.995054753686731
(1 row)
Description
Returns the smallest integer(type interval will extract the days to number first) that is greater than or equal to a number.
Syntax
CEIL(i interval) returns number
CEIL(n numeric) returns numeric
CEIL(d double precision) returns numeric
General rules
CEIL returns the smallest integer that is greater than or equal to a number.
The number may be an interval type or numeric data type or double precision type.
The data type of the return value is number or numeric.
Example
In the following example, returns the smallest integer that is greater than or equal to that number. If the argument is of type interval, extract days from an interval first which is of type numeric, and then returns the smallest integer that is greater than or equal to that number
select ceil(sysdate - '2023-01-01'::date) FROM dual ;
ceil
------
335
(1 row)
select ceil(sysdate - to_date('2023-11-27','yyyy-mm-dd')) FROM dual;
ceil
------
5
(1 row)
select ceil(sysdate - to_date('2023-01-01','yyyy-mm-dd')) FROM dual;
ceil
------
335
(1 row)
The following string functions are supported:
BTRIM
INSTR
INSTRB
LENGTH
LENGTHB
LPAD
LTRIM
NLSSORT
REGEXP_COUNT
REGEXP_INSTR
REGEXP_LIKE
REGEXP_SUBSTR
REGEXP_REPLACE
REPLACE
RPAD
RTRIM
SOUNDEX
SUBSTR
SUBSTRB
CONCAT
UPPER
Description
Removes the specified characters from the beginning and end of a string.
Syntax
BTRIM(str text) returns text
BTRIM(str text, trimChars text) returns text
BTRIM(str clob) returns clob
BTRIM(str clob, trimChars clob) returns clob
General rules
BTRIM returns a string with trimChars removed from the beginning and end of string str.
If multiple trim characters are specified, all characters matching the trim characters are removed. If trimChars is omitted, all leading and trailing halfwidth spaces are removed.
The data type of the return value is TEXT if the first parameter is of `text` data type, CLOB if the first parameter is of `clob` data type.
Note
BTRIM does not exist for Oracle databases.
The CHAR type specification for BTRIM uses orafce for its behavior, which is different to that of BTRIM of LightDB. The search_path parameter must be modified for it to behave the same as the specification described above.
Information
The general rule for BTRIM of LightDB is as follows:
If the string is CHAR type, trailing spaces are removed and then the trim characters are removed.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "The SQL Language" > "Functions and Operators" > "String Functions and Operators" in the LightDB Documentation for information on BTRIM.
Example
In the following example, a string that has had "a" removed from both ends of "aabcaba" is returned.
SELECT BTRIM('aabcaba','a') FROM DUAL;
btrim
-------
bcab
(1 row)
Description
Returns the position of a substring in a string.
Syntax
INSTR(str1 text, str2 text) returns integer
INSTR(str1 text, str2 text, startPos integer) returns integer
INSTR(str1 text, str2 text, startPos integer, occurrences integer) returns integer
General rules
INSTR searches for substring str2 in string str1 and returns the position (in characters) in str1 of the first character of the occurrence.
The search starts from the specified start position startPos in str1.
When startPos is 0 or negative, the start position will be the specified number of characters from the left of the end of str1, and INSTR will search backward from that point.
If the start position is not specified, the search will be performed from the beginning of str1.
If occurrences is specified, the position in str1 of the nth occurrence of str2 is returned. Only positive numbers can be specified.
If occurrences is not specified, the start position of the first occurrence that is found is returned.
If str2 is not found in str1, 0 is returned.
For startPos and occurrences, specify a SMALLINT or INTEGER type.
The data type of the return value is INTEGER.
Example
In the following example, characters "BC" are found in string "ABCACBCAAC", and the position of those characters is returned.
SELECT INSTR('ABCACBCAAC','BC') FROM DUAL;
instr
-------
2
(1 row)
SELECT INSTR('ABCACBCAAC','BC',-1,2) FROM DUAL;
instr
-------
2
(1 row)
Description
The INSTRB function searches a string for a substring using bytes and returns the position in the string that is the first byte of a specified occurrence of the substring.
Syntax
INSTRB( p_source text, p_search text, p_position int4 default 1, p_occurrence int4 default 1) RETURNS integer
General rules
INSTRB searches for substring p_search in string p_source and returns the position (in bytes) in p_source of the first byte of the occurrence.
The search starts from the specified start position p_position in p_source.
When p_position is 0 or negative, it will raise an error. It is different from ORACLE, in oracle , p_position will be the specified number of bytes from the left of the end of p_source, and INSTRB will search backward from that point.
If the start position is not specified, the search will be performed from the beginning of p_source.
If p_occurrence is specified, the position in p_source of the nth occurrence of p_search is returned. Only positive numbers can be specified.
If occurrences is not specified, the start position of the first occurrence that is found is returned.
If p_search is not found in p_source, 0 is returned.
The data type of the return value is integer.
Note
If using INSTRB, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.
Example
In the following example, characters "on" are found in string "hhh测试onhx", and the position of those bytes is returned.
SELECT INSTRB('hhh测试onhx', 'on') FROM DUAL;
instrb
--------
10
(1 row)
SELECT INSTRB('hhh测试onhx on xxxx', 'on', 7, 2) FROM DUAL;
instrb
--------
15
(1 row)
Description
Returns the length of a string in number of characters.
Syntax
LENGTH(str text) returns integer
General rules
LENGTH returns the number of characters in string str.
If the string is CHAR type, trailing spaces are included in the length.
The data type of the return value is INTEGER.
Note
The LENGTH specification above uses orafce for its behavior, which is different to that of LENGTH of LightDB. The search_path parameter must be modified for it to behave according to the orafce specification.
Information
The general rule for LENGTH of LightDB is as follows:
If the string is CHAR type, trailing spaces are not included in the length.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "The SQL Language" > "Functions and Operators" > "String Functions and Operators" in the LightDB Documentation for information on LENGTH.
Example
In the following example, the number of characters in column col2 (defined using CHAR(10)) in table t1 is returned.
SELECT col2,LENGTH(col2) FROM t1 WHERE col1 = '1001';
col2 | length
------------+--------
AAAAA | 10
(1 row)
Description
Returns the length of a string in number of bytes.
Syntax
LENGTHB(str text) returns integer
General rules
LENGTHB returns the number of bytes in string str.
If the string is CHAR type, trailing spaces are included in the length.
The data type of the return value is INTEGER.
Example
In the following example, the number of bytes in column col2 (defined using CHAR(10)) in table t1 is returned. Note that, in the second SELECT statement, each character in "*" has a length of 3 bytes, for a total of 9 bytes, and 7 bytes are added for the 7 trailing spaces. This gives a result of 16 bytes.
SELECT col2,LENGTHB(col2) FROM t1 WHERE col1 = '1001';
col2 | lengthb
---------------+---------
AAAAA | 10
(1 row)
SELECT col2,LENGTHB(col2) FROM t1 WHERE col1 = '1004';
col2 | lengthb
---------------+---------
*** | 16
(1 row)
Description
Left-pads a string to a specified length with a sequence of characters.
Syntax
LPAD(x bigint, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | integer) returns varchar2
LPAD(x integer, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | integer) returns varchar2
LPAD(x smallint, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | integer) returns varchar2
LPAD(x numeric, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | integer) returns varchar2
LPAD(str text, len integer) returns text
LPAD(str text, len integer, paddingStr text | char | varchar2 | nvarchar2) returns text
LPAD(str clob, len integer) returns clob
LPAD(str clob, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | bigint | integer | smallint | numeric) returns clob
LPAD(str char, len integer) returns varchar2
LPAD(str char, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | bigint | integer | smallint | numeric) returns varchar2
LPAD(str varchar, len integer) returns varchar2
LPAD(str varchar, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | bigint | integer | smallint | numeric) returns varchar2
LPAD(str varchar2, len integer) returns varchar2
LPAD(str varchar2, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | bigint | integer | smallint | numeric) returns varchar2
LPAD(str nvarchar2, len integer) returns nvarchar2
LPAD(str nvarchar2, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | bigint | integer | smallint | numeric) returns nvarchar2
General rules
LPAD returns the result after repeatedly padding the beginning of string str with padding characters paddingStr until the string reaches length len.
If the string is CHAR type, the padding characters are added to the string without removing trailing spaces.
In the resultant string, fullwidth characters are recognized as having a length of 2, and halfwidth characters having a length of 1. If a fullwidth character cannot be included in the resultant string because there is only space available for one halfwidth character, the string is padded with a single-byte space.
The data type of the return value is text if the first parameter is of `text` data type, clob if the first parameter is of `clob` data type, nvarchar2 if the first parameter is of `nvarchar2` data type. The data type of the return value is varchar2 if the first parameter is of `bigint` or `character` or `character varying` or `integer` or `numeric` or `smallint` or `varchar2` data type.
Note
The LPAD specification above uses orafce for its behavior, which is different to that of LPAD of LightDB. The search_path parameter must be modified for it to behave according to the orafce specification.
Information
The general rules for LPAD of LightDB are as follows:
If the string is CHAR type, trailing spaces are removed and then the padding characters are added to the string.
The result length is the number of characters.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "The SQL Language" > "Functions and Operators" > "String Functions and Operators" in the LightDB Documentation for information on LPAD.
Example
In the following example, a 10-character string that has been formed by left-padding the string "abc" with "a" is returned.
SELECT LPAD('abc',10,'a') FROM DUAL;
lpad
------------
aaaaaaaabc
(1 row)
Description
Removes the specified characters from the beginning of a string.
Syntax
LTRIM(str text) returns text
LTRIM(str text, trimChars text) returns text
LTRIM(str clob) returns clob
LTRIM(str clob, trimChars clob) returns clob
General rules
LTRIM returns a string with trimChars removed from the beginning of string str.
If multiple trim characters are specified, all characters matching the trim characters are removed. If trimChars is omitted, all leading halfwidth spaces are removed.
The data type of the return value is TEXT if the first parameter is of `text` data type, CLOB if the first parameter is of `clob` data type.
Note
The LTRIM specification above uses orafce for its behavior, which is different to that of LTRIM of LightDB. The search_path parameter must be modified for it to behave according to the orafce specification.
Information
The general rules for LTRIM of LightDB are as follows:
If the string is CHAR type, trailing spaces are removed and then the trim characters are removed.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "The SQL Language" > "Functions and Operators" > "String Functions and Operators" in the LightDB Documentation for information on LTRIM.
Example
In the following example, a string that has had "ab" removed from the beginning of "aabcab" is returned.
SELECT LTRIM('aabcab','ab') FROM DUAL;
ltrim
-------
cab
(1 row)
Description
Returns a byte string that denotes the lexical order of the locale (COLLATE).
Syntax
NLSSORT(str text) returns bytea
NLSSORT(str text, locale text) returns bytea
NLSSORT(str text, 'NLS_SORT=SCHINESE_PINYIN_M') returns bytea
General rules
NLSSORT is used for comparing and sorting in the collating sequence of a locale (COLLATE) that differs from the default locale.
Values that can be specified for the locale differ according to the operating system of the database server.
If the locale is omitted, it is necessary to use set_nls_sort to set the locale in advance. To set the locale using set_nls_sort, execute a SELECT statement.
The data type of the return value is BYTEA.
Example of setting set_nls_sort using a SELECT statement
SELECT set_nls_sort('en_US.UTF8');
Note
If specifying locale encoding, ensure it matches the database encoding.
See
Refer to "Server Administration" > "Localization" > "Locale Support" in the LightDB Documentation for information on the locales that can be specified.
Example
Table 9.100. Composition of table (t3)
|
col1 |
col2 |
|---|---|
|
1001 |
aabcababc |
|
2001 |
abcdef |
|
3001 |
aacbaab |
In the following example, the result of sorting column col2 in table t3 by "da_DK.UTF8" is returned.
SELECT col1,col2 FROM t3 ORDER BY NLSSORT(col2,'da_DK.UTF8');
col1 | col2
------+------------
2001 | abcdef
1001 | aabcababc
3001 | aacbaab
(3 row)
Description
Searches a string for a regular expression, and returns a count of the matches.
Syntax
REGEXP_COUNT(string text, pattern text) returns integer
REGEXP_COUNT(string text, pattern text, startPos integer) returns integer
REGEXP_COUNT(string text, pattern text, startPos integer, flags text) returns integer
General rules
REGEXP_COUNT returns the number of times pattern occurs in a source string. It returns an integer indicating the number of occurrences of pattern. If no match is found, then the function returns 0. Returns NULL if one of the parameters is NULL, but the 'flags' parameter could be NULL.
The search starts from the specified start position startPos in string, default starts from the beginning of string.
startPos is a positive integer, negative values to search from the end of string are not allowed.
flags is a character expression that lets you change the default matching behavior of the function.
The value of flags can include one or more of the following characters:
'i': case-insensitive matching.
'c': case-sensitive and accent-sensitive matching.
'n': the period (.) match the newline character. By default the period does not match the newline character.
'm': treats the source string as multiple lines.
'x': ignores whitespace characters. By default, whitespace characters match themselves.
If you omit flags then:
The default is case and accent sensitivity.
A period (.) does not match the newline character.
The source string is treated as a single line.
Note
If using the REGEXP_COUNT function, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Example
SELECT REGEXP_COUNT('a'||CHR(10)||'d', 'a.d') FROM DUAL;
regexp_count
--------------
0
(1 row)
SELECT REGEXP_COUNT('a'||CHR(10)||'d', 'a.d', 1, 'm') FROM DUAL;
regexp_count
--------------
0
(1 row)
SELECT REGEXP_COUNT('a'||CHR(10)||'d', 'a.d', 1, 'n') FROM DUAL;
regexp_count
--------------
1
(1 row)
SELECT REGEXP_COUNT('a'||CHR(10)||'d', '^d$', 1, 'm') FROM DUAL;
regexp_count
--------------
1
(1 row)
Description
Returns the beginning or ending position within the string where the match for a pattern was located.
Syntax
REGEXP_INSTR(string text, pattern text) returns integer
REGEXP_INSTR(string text, pattern text, startPos integer) returns integer
REGEXP_INSTR(string text, pattern text, startPos integer, occurrence integer) returns integer
REGEXP_INSTR(string text, pattern text, startPos integer, occurrence integer) returns integer
REGEXP_INSTR(string text, pattern text, startPos integer, occurrence integer, return_opt integer) returns integer
REGEXP_INSTR(string text, pattern text, startPos integer, occurrence integer, return_opt integer, flags text) returns integer
REGEXP_INSTR(string text, pattern text, startPos integer, occurrence integer, return_opt integer, flags text) returns integer
REGEXP_INSTR(string text, pattern text, startPos integer, occurrence integer, return_opt integer, flags text, group integer) returns integer
General rules
REGEXP_INSTR returns an integer indicating the beginning or ending position of the matched substring, depending on the value of the return_opt argument. If no match is found, then the function returns 0.
The search starts from the specified start position startPos in string, default starts from the beginning of string.
startPos is a positive integer, negative values to search from the end of string are not allowed.
occurrence is a positive integer indicating which occurrence of pattern in string should be search for. The default is 1, meaning the first occurrence of pattern in string.
return_opt lets you specify what should be returned in relation to the occurrence:
return_opt = 0, the position of the first character of the occurrence is returned. This is the default.
return_opt = 1, the position of the character following the occurrence is returned.
flags is a character expression that lets you change the default matching behavior of the function. See REGEXP_COUNT for detailed information.
For a pattern with capture group, group is a positive integer indicating which capture group in pattern shall be returned by the function. Capture groups can be nested, they are numbered in order in which their left parentheses appear in pattern. If group is zero, then the position of the entire substring that matches the pattern is returned. If group value exceed the number of capture groups in pattern, the function returns zero. A null group value returns NULL. The default value for group is zero.
Note
If using the REGEXP_INSTR function, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Example
SELECT REGEXP_INSTR('1234567890', '(123)(4(56)(78))') FROM DUAL;
regexp_instr
--------------
1
(1 row)
SELECT REGEXP_INSTR('1234567890', '(4(56)(78))', 3) FROM DUAL;
regexp_instr
--------------
4
(1 row)
SELECT REGEXP_INSTR('123 123456 1234567, 1234567 1234567 12', '[^ ]+', 1, 6) FROM DUAL;
regexp_instr
--------------
37
(1 row)
SELECT REGEXP_INSTR('199 Oretax Prayers, Riffles Stream, CA', '[S|R|P][[:alpha:]]{6}', 3, 2, 1) FROM DUAL;
regexp_instr
--------------
28
(1 row)
Description
Condition in the WHERE clause of a query, causing the query to return rows that match the given pattern.
Syntax
REGEXP_LIKE(string text, pattern text) returns boolean
REGEXP_LIKE(string text, pattern text, flags text) returns boolean
General rules
REGEXP_LIKE is similar to the LIKE condition, except it performs regular expression matching instead of the simple pattern matching performed by LIKE.
Returns a boolean, true when pattern match in string, false otherwise. Returns NULL if one of the parameters is NULL, but the 'flags' parameter could be NULL.
flags is a character expression that lets you change the default matching behavior of the function. See REGEXP_COUNT for detailed information.
Note
If using the REGEXP_LIKE function, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Example
SELECT REGEXP_LIKE('a'||CHR(10)||'d', 'a.d', 'm') FROM DUAL;
regexp_like
-------------
f
(1 row)
SELECT REGEXP_LIKE('a'||CHR(10)||'d', 'a.d', 'n') FROM DUAL;
regexp_like
-------------
t
(1 row)
Description
Returns the string that matches the pattern specified in the call to the function.
Syntax
REGEXP_SUBSTR(string text, pattern text) returns text
REGEXP_SUBSTR(string text, pattern text, startPos integer) returns text
REGEXP_SUBSTR(string text, pattern text, startPos integer, occurrence numeric) returns text
REGEXP_SUBSTR(string text, pattern text, startPos integer, occurrence numeric, flags text) returns text
REGEXP_SUBSTR(string text, pattern text, startPos integer, occurrence numeric, flags text, group int) returns text
REGEXP_SUBSTR(string CLOB, pattern text) returns clob
General rules
REGEXP_SUBSTR returns the matched substring resulting from matching a POSIX regular expression pattern to a string. If no match is found, then the function returns NULL. Returns NULL if one of the parameters is NULL, but the 'flags' parameter could be NULL. The function returns the string as TEXT or CLOB data in the same character set as the first argument.
The search starts from the specified start position startPos in string, default starts from the beginning of string.
startPos is a positive integer, negative values to search from the end of string are not allowed.
occurrence is a positive integer indicating which occurrence of pattern in string should be search for. The default is 1, meaning the first occurrence of pattern in string. If the input is a floating point number, it will be rounded down.
flags is a character expression that lets you change the default matching behavior of the function. See REGEXP_COUNT for detailed information.
For a pattern with capture group, *group* is a positive integer indicating which capture group in pattern shall be returned by the function. Capture groups can be nested, they are numbered in order in which their left parentheses appear in pattern. If group is zero, then the position of the entire substring that matches the pattern is returned. If group value exceed the number of capture groups in pattern, the function returns NULL. A null group value returns NULL. The default value for group is zero.
Note
If using the REGEXP_SUBSTR function, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Example
SELECT REGEXP_SUBSTR('number of your street, zipcode town, FR', ',[^,]+') FROM DUAL;
regexp_substr
----------------
, zipcode town
(1 row)
SELECT regexp_substr('number of your street, zipcode town, FR', ',[^,]+', 24) FROM DUAL;
regexp_substr
---------------
, FR
(1 row)
SELECT regexp_substr('number of your street, zipcode town, FR', ',[^,]+', 1, 2) FROM DUAL;
regexp_substr
---------------
, FR
(1 row)
SELECT regexp_substr('1234567890 1234567890', '(123)(4(56)(78))', 1, 1, 'i', 0) FROM DUAL;
regexp_substr
---------------
12345678
(1 row)
SELECT regexp_substr('1234567890 1234567890', '(123)(4(56)(78))', 1, 1.9, 'i', 0) FROM DUAL;
regexp_substr
---------------
12345678
(1 row)
Description
Returns the string that matches the pattern specified in the call to the function.
Syntax
REGEXP_REPLACE(string text, pattern text, replace_string text, startPos integer,
occurrence integer, flags text) returns text
REGEXP_REPLACE(string clob, pattern text, replace_string text) returns clob
General rules
REGEXP_REPLACE returns a modified version of the source string where occurrences of a POSIX regular expression pattern found in the source string are replaced with the specified replacement string. If no match is found or the occurrence queried exceed the number of match, then the source string untouched is returned. The function returns TEXT if the first parameter is not a CLOB and returns CLOB if the first parameter is a CLOB.
The search and replacement starts from the specified start position startPos in string, default starts from the beginning of string.
startPos is a positive integer, negative values to search from the end of string are not allowed.
occurrence is a positive integer indicating which occurrence of pattern in string should be search for and replaced. The default is 0, meaning all occurrences of pattern in string.
flags is a character expression that lets you change the default matching behavior of the function. See REGEXP_COUNT for detailed information.
Note
If using the REGEXP_REPLACE function, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Example
SELECT regexp_replace('512.123.4567 612.123.4567', '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})', '(\1) \2-\3') FROM DUAL;
regexp_replace
-------------------------------
(512) 123-4567 (612) 123-4567
(1 row)
SELECT oracle.REGEXP_REPLACE('number your street, zipcode town, FR', '( ){2,}', ' ', 9);
regexp_replace
----------------------------------------
number your street, zipcode town, FR
(1 row)
SELECT oracle.REGEXP_REPLACE('number your street, zipcode town, FR', '( ){2,}', ' ', 9, 2);
regexp_replace
---------------------------------------------
number your street, zipcode town, FR
(1 row)
Description
Returns string with every occurrence of search_string replaced with replacement_string.
Syntax
REPLACE(string text, pattern text) returns text
REPLACE(string text, pattern text, replace_string text) returns text
REPLACE(string uuid, pattern text, replace_string text) returns text
REPLACE(string CLOB, pattern text) returns CLOB
REPLACE(string CLOB, pattern text, replace_string text) returns CLOB
General rules
If replace_string is omitted or null, then all occurrences of search_string are removed. If search_string is null, then string is returned.
The function returns TEXT if the first argument is not a CLOB and returns CLOB if the first argument is a CLOB.
Note
The above REPLACE specification uses orafce for its behavior, which is different to that of REPLACE of LightDB. The search_path parameter must be modified for it to behave according to the orafce specification.
Information
The general rules for REPLACE of LightDB are as follows:
If replace_string or search_string is null, then null is returned.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Example
SELECT REPLACE('abcdAbcdasd', 'a') FROM DUAL;
replace
-----------
bcdAbcdsd
(1 row)
SELECT REPLACE('abcdAbcdasd', 'a','c') FROM DUAL;
replace
-------------
cbcdAbcdcsd
(1 row)
SELECT REPLACE('abcdAbcdasd', NULL,'c') FROM DUAL;
replace
-------------
abcdAbcdasd
(1 row)
SELECT REPLACE('abcdAbcdasd', 'a',NULL) FROM DUAL;
replace
-----------
bcdAbcdsd
(1 row)
Description
Right-pads a string to a specified length with a sequence of characters.
Syntax
RPAD(x bigint, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | integer) returns varchar2
RPAD(x integer, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | integer) returns varchar2
RPAD(x smallint, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | integer) returns varchar2
RPAD(x numeric, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | integer) returns varchar2
RPAD(str text, len integer) returns text
RPAD(str text, len integer, paddingStr text | char | varchar2 | nvarchar2) returns text
RPAD(str clob, len integer) returns clob
RPAD(str clob, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | bigint | integer | smallint | numeric) returns clob
RPAD(str char, len integer) returns varchar2
RPAD(str char, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | bigint | integer | smallint | numeric) returns varchar2
RPAD(str varchar, len integer) returns varchar2
RPAD(str varchar, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | bigint | integer | smallint | numeric) returns varchar2
RPAD(str varchar2, len integer) returns varchar2
RPAD(str varchar2, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | bigint | integer | smallint | numeric) returns varchar2
RPAD(str nvarchar2, len integer) returns nvarchar2
RPAD(str nvarchar2, len integer, paddingStr text | clob | char | varchar | varchar2 | nvarchar2 | bigint | integer | smallint | numeric) returns nvarchar2
General rules
RPAD returns the result after repeatedly padding the end of string str with padding characters paddingStr until the string reaches length len.
If the string is CHAR type, the padding characters are added to the string without removing trailing spaces.
In the resultant string, fullwidth characters are recognized as having a length of 2, and halfwidth characters having a length of 1. If a fullwidth character cannot be included in the resultant string because there is only space available for one halfwidth character, the string is padded with a single-byte space.
The data type of the return value is text if the first parameter is of `text` data type, clob if the first parameter is of `clob` data type, nvarchar2 if the first parameter is of `nvarchar2` data type. The data type of the return value is varchar2 if the first parameter is of `bigint` or `character` or `character varying` or `integer` or `numeric` or `smallint` or `varchar2` data type.
Note
The RPAD specification above uses orafce for its behavior, which is different to that of RPAD of LightDB. The search_path parameter must be modified for it to behave according to the orafce specification.
Information
The general rules for RPAD of LightDB are as follows:
If the string is CHAR type, trailing spaces are removed and then the padding characters are added to the string.
The result length is the number of characters.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "The SQL Language" > "Functions and Operators" > "String Functions and Operators" in the LightDB Documentation for information on RPAD.
Example
In the following example, a 10-character string that has been formed by right-padding the string "abc" with "a" is returned.
SELECT RPAD('abc',10,'a') FROM DUAL;
rpad
------------
abcaaaaaaa
(1 row)
Description
Removes the specified characters from the end of a string.
Syntax
RTRIM(str text) returns text
RTRIM(str text, trimChars text) returns text
RTRIM(str clob) returns clob
RTRIM(str clob, trimChars text) returns clob
General rules
RTRIM returns a string with trimChars removed from the end of string str.
If multiple trim characters are specified, all characters matching the trim characters are removed. If trimChars is omitted, all trailing halfwidth spaces are removed.
The string returned is of TEXT data type if the first parameter is a character data type, and a CLOB if the first parameter is a CLOB data type.
Note
The RTRIM specification above uses orafce for its behavior, which is different to that of RTRIM of LightDB. The search_path parameter must be modified for it to behave the same as the orafce specification.
Information
The general rules for RTRIM of LightDB are as follows:
If the string is CHAR type, trailing spaces are removed and then the trim characters are removed.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "The SQL Language" > "Functions and Operators" > "String Functions and Operators" in the LightDB Documentation for information on RTRIM.
Example
In the following example, a string that has had "ab" removed from the end of "aabcab" is returned.
SELECT RTRIM('aabcab','ab') FROM DUAL;
rtrim
-------
aabc
(1 row)
Description
SOUNDEX returns a character string containing the phonetic representation of char. This function lets you compare words that are spelled differently, but sound alike in English.
Syntax
SOUNDEX(str text) returns text
General rules
The phonetic representation is defined in The Art of Computer Programming, Volume 3: Sorting and Searching, by Donald E. Knuth.
Retain the first letter of the string and remove all other occurrences of the following letters: a, e, h, i, o, u, w, y.
Assign numbers to the remaining letters as follows:
b, f, p, v = 1
c, g, j, k, q, s, x, z = 2
d, t = 3
l = 4
m, n = 5
r = 6
If two or more letters with the same number were adjacent in the original name (before step 1), or adjacent except for any intervening h and w, then retain the first letter and omit rest of all the adjacent letters with same number.
Return the first four bytes padded with 0.
Note
If using the SOUNDEX function, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Example
In the following example, a string that has had "ab" removed from the end of "aabcab" is returned.
SELECT SOUNDEX('SMYTHE')=SOUNDEX('Smith') as same FROM DUAL;
same
------
t
(1 row)
Description
Extracts part of a string using characters to specify position and length.
Syntax
SUBSTR(str text, startPos integer) returns text
SUBSTR(str text, startPos numeric) returns text
SUBSTR(str text, startPos integer, len integer) returns text
SUBSTR(str text, startPos numeric, len numeric) returns text
SUBSTR(str CLOB, startPos number) returns CLOB
SUBSTR(str CLOB, startPos number, len number) returns CLOB
General rules
SUBSTR extracts and returns a substring of string str, beginning at position startPos, for number of characters len.
When startPos is positive, it will be the number of characters from the beginning of the string.
When startPos is 0, it will be treated as 1.
When startPos is negative, it will be the number of characters from the end of the string.
When len is not specified, all characters to the end of the string are returned. NULL is returned when len is less than 1.
For startPos and len, specify an integer or NUMERIC type. If numbers including decimal places are specified, they are truncated to integers.
The string returned is of TEXT data type if the first parameter is a character data type, and a CLOB if the first parameter is a CLOB data type.
Note
There are two types of SUBSTR. One that behaves as described above and one that behaves the same as SUBSTRING. The search_path parameter must be modified for it to behave the same as the specification described above.
If the change has not been implemented, SUBSTR is the same as SUBSTRING.
Information
The general rules for SUBSTRING of LightDB are as follows:
The start position will be from the beginning of the string, whether the start position is positive, 0, or negative.
When len is not specified, all characters to the end of the string are returned.
An empty string is returned if no string is extracted or len is less than 1.
See
Refer to "The SQL Language" > "Functions and Operators" > "String Functions and Operators" in the LightDB Documentation for information on SUBSTRING.
Example
In the following example, part of the string "ABCDEFG" is extracted.
SELECT SUBSTR('ABCDEFG',3,4) "Substring" FROM DUAL;
Substring
-----------
CDEF
(1 row)
SELECT SUBSTR('ABCDEFG',-5,4) "Substring" FROM DUAL;
Substring
-----------
CDEF
(1 row)
Description
Extracts part of a string using bytes to specify position and length.
Syntax
SUBSTRB(str text, startPos integer) returns varchar2
SUBSTRB(str text, startPos integer, len integer) returns varchar2
General rules
SUBSTR extracts and returns a substring of string str, beginning at byte position startPos, for number of byte len.
When startPos is 0 or negative, extraction starts at the position found by subtracting 1 from the start position and shifting by that number of positions to the left.
When len is not specified, all bytes to the end of the string are returned.
An empty string is returned if no string is extracted or len is less than 1.
For startPos and len, specify a SMALLINT or INTEGER type.
The data type of the return value is VARCHAR2.
Note
The external specification of SUBSTRB is different to that of SUBSTR added by orafce, conforming with SUBSTRING of LightDB.
Example
In the following example, part of the string "aaabbbccc" is extracted.
SELECT SUBSTRB('aaabbbccc',4,3) FROM DUAL;
substrb
-----------
bbb
(1 row)
SELECT SUBSTRB('aaabbbccc',-2,6) FROM DUAL;
substrb
-----------
aaa
(1 row)
Description
concatenate two strings
Syntax
CONCAT(str1 TEXT, str2 TEXT) returns TEXT
CONCAST(str1 CLOB, str2 CLOB) returns CLOB
General rules
In concatenations of two different data types, the function returns the data type that results in a lossless conversion.
This function is equivalent to the concatenation operator (||).
The string returned is of TEXT data type if the first parameter is a character data type, and a CLOB if the first parameter is a CLOB data type.
Example
SELECT concat('a', 'b') FROM DUAL;
concat
--------
ab
(1 row)
SELECT concat(to_clob('a'), 'b') FROM DUAL;
concat
--------
ab
(1 row)
Description
UPPER returns string with all letters uppercase.
Syntax
UPPER(string TEXT) returns TEXT
UPPER(string CLOB) returns CLOB
General rules
The string returned is of TEXT data type if the first parameter is a character data type, and a CLOB if the first parameter is a CLOB data type.
Example
SELECT UPPER('hello world') FROM DUAL;
upper
-------------
HELLO WORLD
(1 row)
SELECT UPPER(to_clob('hello world')) FROM DUAL;
upper
-------------
HELLO WORLD
(1 row)
The following date/time functions are supported:
ADD_MONTHS
DBTIMEZONE
LAST_DAY
MONTHS_BETWEEN
NEXT_DAY
ROUND
SESSIONTIMEZONE
SYSDATE
TRUNC
TZ_OFFSET
Note
If the DATE type only is shown in the date/time functions, these functions can be used in both orafce and LightDB.
Description
Adds months to a date.
Syntax
ADD_MONTHS(date DATE, months INTEGER) returns DATE
ADD_MONTHS(date TIMESTAMP WITH TIME ZONE, months INTEGER) returns TIMESTAMP
General rules
ADD_MONTHS returns date plus months.
For date, specify a DATE or TIMESTAMP WITH TIME ZONE type.
For months, specify a SMALLINT or INTEGER type.
If a negative value is specified for months, the number of months is subtracted from the date.
The data type of the return value is DATE or TIMESTAMP.
Note
If using the DATE type of orafce, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Example
The example below shows the result of adding 3 months to the date May 1, 2016.
SELECT ADD_MONTHS(DATE'2016/05/01',3) FROM DUAL;
add_months
---------------------
2016-08-01 00:00:00
(1 row)
Description
Returns the value of the database time zone.
Syntax
DBTIMEZONE() returns text
General rules
DBTIMEZONE returns the time zone value of the database.
The data type of the return value is TEXT.
Note
If using DBTIMEZONE, it is necessary to specify "oracle" for search_path in advance.
The time zone of the database is set to "GMT" by default. To change the time zone, change the "orafce.timezone" parameter. An example using the SET statement is shown below.
SET orafce.timezone = 'Japan';
The orafce.timezone settings can be set using any of the methods for setting server parameters.
If the SQL statement is executed with orafce.timezone set, the following message may be displayed, however, the parameter settings are enabled, so you can ignore this.
WARNING: unrecognized configuration parameter "orafce.timezone"
The time zones that can be set in "orafce.timezone" are the same as for the "TimeZone" server parameter.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "The SQL Language" > "Data Types" > "Date/Time Types" in the LightDB Documentation for information on the time zone.
Example
In the following example, the DBTIMEZONE result is returned.
SELECT DBTIMEZONE() FROM DUAL;
dbtimezone
------------
GMT
(1 row)
Description
Returns the last day of the month in which the specified date falls.
Syntax
LAST_DAY(date DATE) returns DATE
LAST_DAY(date TIMESTAMPTZ) returns TIMESTAMPTZ
General rules
LAST_DAY returns the last day of the month in which the specified date falls.
For date, specify a DATE or TIMESTAMPTZ type.
The data type of the return value is DATE or TIMESTAMPTZ.
Note
If using the DATE type of orafce, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Example
In the example below, the last date of "February 01, 2016" is returned.
SELECT LAST_DAY(DATE'2016/02/01') FROM DUAL;
last_day
---------------------
2016-02-29 00:00:00
(1 row)
Description
Returns the number of months between two dates.
Syntax
MONTHS_BETWEEN(date1 DATE, date2 DATE) returns NUMERIC
MONTHS_BETWEEN(date1 TIMESTAMP WITH TIME ZONE, date2 TIMESTAMP WITH TIME ZONE) returns NUMERIC
General rules
MONTHS_BETWEEN returns the difference in the number of months between date1 and date2.
For date1 and date2, specify a DATE or TIMESTAMPTZ type.
If date2 is earlier than date1, the return value will be negative.
If two dates fall on the same day, or each of the two dates are the last day of the month to which they belong, an integer is returned. If the days are different, one month is considered to be 31 days, and a value with the difference in the number of days divided by 31 added is returned.
The data type of the return value is DATE or NUMERIC.
Note
If using the DATE type of orafce, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Example
In the following example, the difference between the months of March 15, 2016 and November 15, 2015 is returned.
SELECT MONTHS_BETWEEN(DATE'2016/03/15', DATE'2015/11/15') FROM DUAL;
months_between
----------------
4
(1 row)
Description
Returns the date of the first instance of a particular day of the week that follows the specified date.
Syntax
NEXT_DAY(date DATE, dayOfWk TEXT) returns DATE
NEXT_DAY(date DATE, dayOfWk INTEGER) returns DATE
NEXT_DAY(date TIMESTAMP WITH TIME ZONE, dayOfWk TEXT) returns TIMESTAMP
NEXT_DAY(date TIMESTAMP WITH TIME ZONE, dayOfWk INTEGER) returns TIMESTAMP
General rules
NEXT_DAY returns the date matching the first instance of dayOfWk that follows date.
For date, specify a DATE or TIMESTAMPTZ WITH TIME ZONE type.
Specify a numeric value or string indicating the day of the week.
Values that can be specified for the day
Table 9.101. Setting dayOfWk for NEXT_DAY Function
|
Setting example |
Overview |
|---|---|
|
1 |
1 (Sunday) to 7 (Saturday) can be specified |
|
'Sun', or 'Sunday' |
English display of the day |
|
'*' |
Japanese display of the day |
The data type of the return value is DATE or TIMESTAMP.
Note
If using the DATE type of orafce, it is necessary to specify "oracle" for search_path in advance.
The ability to use Japanese for entering days is provided by the orafce proprietary specification. Japanese cannot be used for entering days when using date/time functions other than NEXT_DAY (such as TO_DATE).
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Example
In the example below, the date of the first Friday on or after "May 1, 2016" is returned.
SELECT NEXT_DAY(DATE'2016/05/01', 'Friday') FROM DUAL;
next_day
---------------------
2016-05-06 00:00:00
(1 row)
Description
Rounds a date.
Syntax
ROUND(date DATE) returns DATE
ROUND(date DATE, fmt TEXT) returns DATE
ROUND(date TIMESTAMP WITH TIME ZONE) returns TIMESTAMP WITH TIME ZONE
ROUND(date TIMESTAMP WITH TIME ZONE, fmt TEXT) returns TIMESTAMP WITH TIME ZONE
ROUND(date TIMESTAMP WITHOUT TIME ZONE) returns TIMESTAMP WITHOUT TIME ZONE
ROUND(date TIMESTAMP WITHOUT TIME ZONE, fmt TEXT) returns TIMESTAMP WITHOUT TIME ZONE
General rules
ROUND returns a date rounded to the unit specified by format model fmt.
For date, specify a DATE or TIMESTAMPTZ type.
Specify the format model as a string.
Values that can be specified for the format model
Table 9.102. Setting Format Model for ROUND Function
|
Format model |
Rounding unit |
|---|---|
|
Y,YY,YYY,YYYY, SYYYY,YEAR,SYEAR |
Year |
|
I,IY,IYY,IYYY |
Year (values including calendar weeks, in compliance with the ISO standard) |
|
Q |
Quarter |
|
WW |
Week (first day of the year) |
|
IW |
Week (Monday of that week) |
|
W |
Week (first weekday on which the first day of the month falls) |
|
DAY,DY,D |
Week (Sunday of that week) |
|
MONTH,MON,MM,RM |
Month |
|
CC,SCC |
Century |
|
DDD,DD,J |
Day |
|
HH,HH12,HH24 |
Hour |
|
MI |
Minute |
If decimal places are rounded: for year, the boundary for rounding is July 1; for month, the day is 16; and for week, the weekday is Thursday.
If fmt is omitted, the date is rounded by day.
If the DATE type of LightDB is specified for the date, that DATE type will be the data type of the return value. If the TIMESTAMP type is specified for the date, the data type will be TIMESTAMP WITH TIME ZONE, irrespective of whether a time zone is used.
Example
In the example below, the result of "June 20, 2016 18:00:00" rounded by Sunday of the week is returned.
SELECT ROUND(TIMESTAMP'2016/06/20 18:00:00','DAY') FROM DUAL;
round
------------------------
2016-06-19 00:00:00+09
(1 row)
Description
Returns the time zone of the session.
Syntax
SESSIONTIMEZONE() returns text
General rules
SESSIONTIMEZONE returns the time zone value between sessions.
The data type of the return value is TEXT.
Note
If using SESSIONTIMEZONE, it is necessary to specify "oracle" for search_path in advance.
The value returned by SESSIONTIMEZONE becomes the value set in the "TimeZone" server parameter.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Example
In the following example, the time zone of the session is returned.
SELECT SESSIONTIMEZONE() FROM DUAL;
sessiontimezone
-----------------
Japan
(1 row)
Description
Returns the system date.
Syntax
SYSDATE() returns oracle.date
General rules
SYSDATE returns the system date.
The data type of the return value is the DATE type of orafce.
Note
If using SYSDATE, it is necessary to specify "oracle" for search_path in advance.
The date returned by SYSDATE depends on the time zone value of the orafce database.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "DBTIMEZONE" for information on the time zone values of the database.
Refer to "The SQL Language" > "Data Types" > "Date/Time Types" in the LightDB Documentation for information on the time zone.
Example
In the following example, the system date is returned.
SELECT SYSDATE() FROM DUAL;
sysdate
---------------------
2016-06-22 08:06:51
(1 row)
Description
Truncates a date.
Syntax
TRUNC(date DATE) returns DATE
TRUNC(date DATE, fmt TEXT) returns DATE
TRUNC(date TIMESTAMP WITH TIME ZONE) returns TIMESTAMP WITH TIME ZONE
TRUNC(date TIMESTAMP WITH TIME ZONE, fmt TEXT) returns TIMESTAMP WITH TIME ZONE
TRUNC(date TIMESTAMP WITHOUT TIME ZONE) returns TIMESTAMP WITHOUT TIME ZONE
TRUNC(date TIMESTAMP WITHOUT TIME ZONE, fmt TEXT) returns TIMESTAMP WITHOUT TIME ZONE
General rules
TRUNC returns a date truncated to the unit specified by format model fmt.
For date, specify a DATE or TIMESTAMP type.
Specify the format model as a string. The values that can be specified are the same as for ROUND.
If fmt is omitted, the date is truncated by day.
If the DATE type of LightDB is specified for the date, that DATE type will be the data type of the return value. If the TIMESTAMP type is specified for the date, the data type will be TIMESTAMP WITH TIME ZONE, irrespective of whether a time zone is used.
See
Refer to "ROUND" for information on the values that can be specified for the format model.
Example
In the example below, the result of "August 10, 2016 15:30:00" truncated by the day is returned.
SELECT TRUNC(TIMESTAMP'2016/08/10 15:30:00','DDD') FROM DUAL;
trunc
------------------------
2016-08-10 00:00:00+09
(1 row)
Description
Returns the time zone offset.
Syntax
TZ_OFFSET(text) returns text
General rules
You can enter a valid time zone region name, a time zone offset from UTC (which simply returns itself).
For a listing of valid values for time_zone_name, query the name column of the pg_timezone_names.
Note
If using TZ_OFFSET, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.
Example
SELECT TZ_OFFSET('Asia/Shanghai') FROM DUAL;
tz_offset
-----------
08:00:00
(1 row)
(1 row)
The following data type formatting functions are supported:
CONVERT
NUMTOYMINTERVAL
TO_BLOB(raw)
TO_CHAR
TO_DATE
TO_MULTI_BYTE
TO_NUMBER
TO_SINGLE_BYTE
ASCIISTR
ASCII
SQLCODE_TO_SQLSTATE
TO_CLOB
RAWTOHEX
ROWIDTOCHAR
Description
CONVERT converts a character string from one character set to another.
Syntax
CONVERT(str TEXT,dest_char_set TEXT,source_char_set TEXT DEFAULT 'utf8') returns TEXT
General rules
TThe str argument is the value to be converted.
The dest_char_set argument is the name of the character set to which str is converted.
The source_char_set argument is the name of the character set in which str is stored in the database. The default value is the database character set.
Both the destination and source character set arguments can be either literals or columns containing the name of the character set.
For complete correspondence in character conversion, it is essential that the destination character set contains a representation of all the characters defined in the source character set. Where a character does not exist in the destination character set, a replacement character appears. Replacement characters can be defined as part of a character set definition.
Note
If using CONVERT for converting string, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.
Example
SELECT CONVERT('娴嬭瘯', 'gbk', 'utf8') from dual; FROM DUAL;
convert
---------
测试
(1 row)
Description
NUMTOYMINTERVAL converts number to an INTERVAL YEAR TO MONTH literal.
Syntax
NUMTOYMINTERVAL(n numeric, interval_unit text) returns interval
General rules
The value for interval_unit specifies the unit of n and must resolve to one of the following string values: 'YEAR', 'MONTH'.
interval_unit is case insensitive.
Note
If using NUMTOYMINTERVAL for specifying date/time values, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.
Example
SELECT NUMTOYMINTERVAL(1,'YEAR') FROM DUAL;
numtoyminterval
-----------------
1 year
(1 row)
Description
TO_BLOB(raw) converts RAW values to BLOB values.
Syntax
TO_BLOB(raw) returns blob
Note
If using the TO_BLOB(raw) function, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.
Example
SELECT TO_BLOB(UTL_RAW.CAST_TO_RAW('测试')) FROM DUAL;
to_blob
----------------
\xe6b58be8af95
(1 row)
Description
Converts a value to a string.
Syntax
TO_CHAR(num SMALLINT) returns TEXT
TO_CHAR(num INTEGER) returns TEXT
TO_CHAR(num BIGINT) returns TEXT
TO_CHAR(num REAL) returns TEXT
TO_CHAR(num DOUBLE PRECISION) returns TEXT
TO_CHAR(num NUMERIC) returns TEXT
TO_CHAR(date TIMESTAMP) returns TEXT
TO_CHAR(TEXT) returns TEXT
TO_CHAR(date TIMESTAMP, fmt TEXT) returns TEXT
TO_CHAR(date TIMESTAMP WITH TIME ZONE, fmt TEXT) returns TEXT
TO_CHAR(date TIMESTAMP, fmt TEXT, nls_date_language TEXT) returns TEXT
TO_CHAR(date TIMESTAMP WITH TIME ZONE, fmt TEXT, nls_date_language TEXT) returns TEXT
General rules
TO_CHAR converts the specified number or date/time value to a string.
For num, specify a numeric data type.
For date, please specify the DATE or TIMESTAMP type. Additionally, you can set the date/time format for the nls_date_format variable in advance, so you don't need to specify it when calling TO_CHAR. Here is an example of how to set it using the SET statement.
SET nls_date_format = 'YYYY/MM/DD HH24:MI:SS';
Whether it is specified at display time or at call time, the format specifier works the same regardless of case.
For fmt, it is used to set the data format. When the first argument is a numeric type, see numeric value formatting functions. When the first argument is a date type, see date value formatting functions.
For nls_date_language, it is used to set the display language environment for dates.
The data type of the return value is TEXT.
Note
If using TO_CHAR for specifying date/time values, it is necessary to specify "oracle" for search_path in advance.
The nls_date_format settings can be set using any of the methods for setting server parameters.
If nls_date_format is set, the following message may be displayed when an SQL statement is executed, however, the parameter settings are enabled, so you can ignore this.
WARNING: unrecognized configuration parameter "nls_date_format"
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.
Example
In the following example, the numeric value "123.45" is returned as a string.
SELECT TO_CHAR(123.45) FROM DUAL;
to_char
---------
123.45
(1 row)
SELECT to_char (sysdate, 'YYYY-MM-DD', 'NLS_DATE_LANGUAGE=''SIMPLIFIED CHINESE''' );
to_char
------------
2023-12-04
(1 row)
Description
Converts a string to a date in accordance with the specified format.
Syntax
TO_DATE(str TEXT) returns TIMESTAMP
TO_DATE(str TEXT, fmt TEXT) returns TIMESTAMP
TO_DATE(oracle.date) returns TIMESTAMP
General rules
TO_DATE converts string str to a date in accordance with the specified format fmt.
Specify a string indicating the date/time.
Specify the required date/time format. If omitted, the format specified in the nls_date_format variable is used. If the nls_date_format variable has not been set, the existing date/time input interpretation is used. A setting example using the SET statement is shown below.
SET nls_date_format = 'YYYY/MM/DD HH24:MI:SS';
The data type of the return value is TIMESTAMP.
Note
The above TO_DATE specification uses orafce for its behavior, which is different to that of TO_DATE of LightDB. The search_path parameter must be modified for it to behave according to the orafce specification.
The nls_date_format settings can be set using any of the methods for setting server parameters.
If nls_date_format is set, the following message may be displayed when an SQL statement is executed, however, the parameter settings are enabled, so you can ignore this.
WARNING: unrecognized configuration parameter "nls_date_format"
Information
The general rule for TO_DATE for specifying the data type format of LightDB is as follows:
The data type of the return value is the DATE type of LightDB.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "The SQL Language" > "Functions and Operators" > "Data Type Formatting Functions" in the LightDB Documentation for information on TO_DATE of LightDB.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.
Refer to "Date/Time Support" > "Date/Time Input Interpretation" in the LightDB Documentation for information on the interpretation of existing date/time input.
Example
In the following example, the string "2016/12/31" is converted to a date and returned.
SELECT TO_DATE('2016/12/31','YYYY/MM/DD') FROM DUAL;
to_date
---------------------
2016-12-31 00:00:00
(1 row)
SELECT to_date('2009-01-02'::oracle.date);
to_date
---------------------
2009-01-02 00:00:00
(1 row)
Description
Converts a single-byte string to a multibyte string.
Syntax
TO_MULTI_BYTE(str TEXT) returns TEXT
General rules
TO_MULTI_BYTE converts halfwidth characters in string str to fullwidth characters, and returns the converted string.
Specify a string indicating the date/time.
Specify the required date/time format. If omitted, the format specified in the nls_date_format variable is used. If the nls_date_format variable has not been set, the existing date/time input interpretation is used. A setting example using the SET statement is shown below.
Only halfwidth alphanumeric characters, spaces and symbols can be converted.
The data type of the return value is TEXT.
Example
In the following example, "abc123" is converted to fullwidth characters and returned.
SELECT TO_MULTI_BYTE('abc123') FROM DUAL;
to_multi_byte
---------------
******
(1 row)
"******" is multibyte "abc123".
Description
Converts a value to a number in accordance with the specified format.
Syntax
TO_NUMBER(str TEXT) returns NUMERIC
TO_NUMBER(num NUMERIC) returns NUMERIC
TO_NUMBER(num NUMERIC, fmt NUMERIC) returns NUMERIC
General rules
TO_NUMBER converts the specified value to a numeric value in accordance with the specified format fmt.
For num, specify a numeric data type.
For str, specify a string indicating the numeric value. Numeric values must comprise only of convertible characters.
Specify the required numeric data format. The specified numeric value is handled as is as a data type expression.
The data type of the return value is NUMERIC.
See
Refer to "The SQL Language" > "Functions and Operators" > "Data Type Formatting Functions" in the LightDB Documentation for information on numeric value formats.
Example
In the following example, the numeric literal "-130.5" is converted to a numeric value and returned.
SELECT TO_NUMBER(-130.5) FROM DUAL;
to_number
-----------
-130.5
(1 row)
Description
Converts a multibyte string to a single-byte string.
Syntax
TO_SINGLE_BYTE(str TEXT) returns TEXT
General rules
TO_SINGLE_BYTE converts fullwidth characters in string str to halfwidth characters, and returns the converted string.
Only fullwidth alphanumeric characters, spaces and symbols that can be displayed in halfwidth can be converted.
The data type of the return value is TEXT.
Example
In the following example, "******" is converted to halfwidth characters and returned. "******" is multibyte "xyz999".
SELECT TO_SINGLE_BYTE('******') FROM DUAL;
to_single_byte
----------------
xyz999
(1 row)
Description
ASCIISTR takes as its argument a string, or an expression that resolves to a string, in any character set and returns an ASCII version of the string in the database character set.
Syntax
ASCIISTR(str TEXT) returns TEXT
General rules
Non-ASCII characters are converted to the form \xxxx, where xxxx represents a UTF-16 code unit.
The data type of the return value is TEXT.
Example
In the following example, "Ä" is converted to "\00C4".
SELECT ASCIISTR('ABÄCDE') FROM DUAL;
asciistr
----------------
AB\00C4CDE
(1 row)
Description
ASCII takes as its argument a string, or an expression that resolves to a string, in any character set and returns an value in the database character set of the first character.
Syntax
ASCIISTR(str TEXT) returns BIGINT
General rules
If your database character set is 7-bit ASCII, then this function returns an ASCII value. If your database character set is UTF-8 Code, then this function returns an UTF-8 value.
The data type of the return value is BIGINT.
Example
In the following example, "Ä" is converted to "50052" in UTF-8.
SELECT ORACLE.ASCII('Ä') FROM DUAL;
ascii
-------
50052
(1 row)
Description
Convert integer error codes to string error codes of length 5.
Syntax
SQLCODE_TO_SQLSTATE(code INTEGER) returns TEXT
General rules
Can only convert integer error codes between 0 and 715827882.
The data type of the return value is TEXT.
Example
In the following example, Conversion error code 2.
select SQLCODE_TO_SQLSTATE(2);
sqlcode_to_sqlstate
---------------------
20000
(1 row)
Description
Converts a string or a number to clob.
Syntax
TO_CLOB(str TEXT) returns CLOB
General rules
TO_CLOB converts the specified value to a clob.
Example
CREATE TABLE testorafce_to_clob (
col_char CHAR(10),
col_varchar2 VARCHAR2(20),
col_varchar VARCHAR(20),
col_nchar NCHAR(10),
col_nvarchar2 NVARCHAR2(20),
col_smallint smallint,
col_integer integer,
col_bigint bigint,
col_decimal decimal,
col_numeric numeric,
col_real real,
col_double double precision,
col_clob CLOB,
col_raw raw(10)
);
INSERT INTO testorafce_to_clob (col_char, col_varchar2, col_varchar, col_nchar, col_nvarchar2, col_smallint, col_integer, col_bigint, col_decimal, col_numeric, col_real, col_double, col_clob, col_raw)
VALUES ('ABC1', 'Hello', 'World', 'hello', 'world', 1, 2, 3, 4, 5, 6, 7, 'This is a CLOB', 'AB');
INSERT INTO testorafce_to_clob (col_char, col_varchar2, col_varchar, col_nchar, col_nvarchar2, col_smallint, col_integer, col_bigint, col_decimal, col_numeric, col_real, col_double, col_clob, col_raw)
VALUES ('ABC2', 'Hello', 'World', 'hello', 'world', 1, 2, 3, 4, 5, 6, 7, 'This is a CLOB', '1');
INSERT INTO testorafce_to_clob (col_char, col_varchar2, col_varchar, col_nchar, col_nvarchar2, col_smallint, col_integer, col_bigint, col_decimal, col_numeric, col_real, col_double, col_clob, col_raw)
VALUES ('ABC3', 'Hello', 'World', 'hello', 'world', 1, 2, 3, 4, 5, 6, 7, to_clob('This is a CLOB'), '1AB456789');
SELECT to_clob(col_char) AS clob_char,
to_clob(col_varchar2) AS clob_varchar2,
to_clob(col_varchar) AS col_varchar,
to_clob(col_nchar) AS clob_nchar,
to_clob(col_nvarchar2) AS clob_nvarchar2,
to_clob(col_clob) AS clob_clob,
to_clob(col_smallint) AS col_smallint,
to_clob(col_integer) AS col_integer,
to_clob(col_bigint) AS col_bigint,
to_clob(col_decimal) AS col_decimal,
to_clob(col_numeric) AS col_numeric,
to_clob(col_real) AS col_real,
to_clob(col_double) AS col_double,
to_clob(col_raw) AS clob_nclob
FROM testorafce_to_clob order by col_char asc;
clob_char | clob_varchar2 | col_varchar | clob_nchar | clob_nvarchar2 | clob_clob | col_smallint | col_integer | col_bigint | col_decimal | col_numeric | col_real | col_double | clob_nclob
------------+---------------+-------------+------------+----------------+----------------+--------------+-------------+------------+-------------+-------------+----------+------------+------------
ABC1 | Hello | world | hello | world | This is a CLOB | 1 | 2 | 3 | 4 | 5 | 6 | 7 | AB
ABC2 | Hello | world | hello | world | This is a CLOB | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 01
ABC3 | Hello | world | hello | world | This is a CLOB | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 01AB456789
(3 rows)
Description
RAWTOHEX CONVERTS THE INCOMING VALUE TO A CHARACTER VALUE THAT CONTAINS ITS HEXADECIMAL REPRESENTATION.
Syntax
RAWTOHEX(test_raw RAW) returns TEXT
RAWTOHEX(test_text TEXT) returns TEXT
RAWTOHEX(test_uuid UUID) returns VARCHAR2
General rules
This function returns a value that has a hexadecimal representation of the bytes that make up the incoming value. Each byte is represented by two hexadecimal numbers.
Example
select rawtohex(sys_guid());
rawtohex
----------------------------------
6466c654a5dc4755902b70d0e6ea6eb6
(1 row)
Description
ROWIDTOCHAR CONVERTS THE INCOMING VALUE ROWID of tid type TO a varchar2 type。
Syntax
ROWIDTOCHAR(test_rowid TID) returns VARCHAR2
General rules
This function returns a value that has a varchar2 type,the max length is 18.
Example
drop TABLE if EXISTS rowidtochar_t1;
create table rowidtochar_t1
(
id int not null
);
insert into rowidtochar_t1 values(1);
select rowidtochar(rowid) from rowidtochar_t1;
rowidtochar
-------------
(0,1)
(1 row)
The following functions for making comparisons are supported:
DECODE
LNNVL
NANVL
NVL
NVL2
Description
Compares values and if they match, returns a corresponding value.
Syntax
DECODE(expr, srch, result)
DECODE(expr, srch, result, default)
DECODE(expr, srch1, result1, srch2, result2)
DECODE(expr, srch1, result1, srch2, result2, default)
DECODE(expr, srch1, result1, srch2, result2, srch3, result3)
DECODE(expr, srch1, result1, srch2, result2, srch3, result3, default)
General rules
DECODE compares values of the value expression to be converted and the search values one by one. If the values match, a corresponding result value is returned. If no values match, the default value is returned if it has been specified. A NULL value is returned if a default value has not been specified.
If the same search value is specified more than once, then the result value returned is the one listed for the first occurrence of the search value.
The following data types can be used in result values and in the default value:
CHAR
VARCHAR
VARCHAR2
NCHAR
NCHAR VARYING
NVARCHAR2
TEXT
INTEGER
BIGINT
NUMERIC
DATE
TIME WITHOUT TIME ZONE
TIMESTAMP WITHOUT TIME ZONE
TIMESTAMP WITH TIME ZONE
The same data type must be specified for the values to be converted and the search values. However, note that different data types may also be specified if a literal is specified in the search value, and the value expression to be converted contains data types that can be converted.
If the result values and default value are all literals, the data types for these values will be as shown below:
If all values are string literals, all will become character types.
If there is one or more numeric literal, all will become numeric types.
If there is one or more literal cast to the datetime/time types, all will become datetime/time types.
If the result values and default value contain a mixture of literals and non-literals, the literals will be converted to the data types of the non-literals.
The same data type must be specified for all result values and for the default value. However, different data types can be specified if the data type of any of the result values or default value can be converted - these data types are listed below:
Table 9.103. Data type combinations that can be converted by DECODE (summary)
| Other result values or default value | ||||
|---|---|---|---|---|
| Numeric type | Character type | Date/time type | ||
| Result value (any) | Numeric type | Y | N | N |
| Character type | N | Y | N | |
| Date/time type | N | N | S(*1) | |
Y: Can be converted
S: Some data types can be converted
N: Cannot be converted
*1: The data types that can be converted for date/time types are listed below:
Table 9.104. Result value and default value date/time data types that can be converted by DECODE
| Other result values or default value | |||||
|---|---|---|---|---|---|
| DATE | TIME WITHOUT TIME ZONE | TIMESTAMP WITHOUT TIME ZONE | TIMESTAMP WITH TIME ZONE | ||
| Result value (any) | DATE | Y | N | Y | Y |
| TIME WITHOUT TIME ZONE | N | Y | N | N | |
| TIMESTAMP WITHOUT TIME ZONE | Y | N | Y | Y | |
| TIMESTAMP WITH TIME ZONE | Y | N | Y | Y | |
Y: Can be converted
N: Cannot be converted
The data type of the return value will be the data type within the result or default value that is longest and has the highest precision.
Example
In the following example, the value of col3 in table t1 is compared and converted to a different value. If the col3 value matches search value 1, the result value returned is "one". If the col3 value does not match any of search values 1, 2, or 3, the default value "other number" is returned.
SELECT col1,
DECODE(col3, 1, 'one',
2, 'two',
3, 'three',
'other number') "num-word"
FROM t1;
col1 | num-word
------+----------
1001 | one
1002 | two
1003 | three
(3 rows)
Description
Determines if a value is TRUE or FALSE for the specified condition.
Syntax
LNNVL(cond BOOLEAN) returns BOOLEAN
General rules
LNNVL determines if a value is TRUE or FALSE for the specified condition. If the result of the condition is FALSE or NULL, TRUE is returned. If the result of the condition is TRUE, FALSE is returned.
The expression for returning TRUE or FALSE is specified in the condition.
The data type of the return value is BOOLEAN.
Example
In the following example, col1 and col3 of table t1 are returned when col3 has a value of 2000 or less, or null values.
SELECT col1,col3 FROM t1 WHERE LNNVL( col3 > 2000 );
col1 | col3
------+------
1001 | 1000
1002 | 2000
2002 |
(3 row)
Description
Returns a substitute value when a value is not a number (NaN).
Syntax
NANVL(expr FLOAT4, substitute FLOAT4) returns FLOAT4
NANVL(expr FLOAT8, substitute FLOAT8) returns FLOAT8
NANVL(expr NUMERIC, substitute NUMERIC) returns NUMERIC
NANVL(expr FLOAT4, substitute VARCHAR) returns FLOAT4
NANVL(expr FLOAT8, substitute VARCHAR) returns FLOAT8
NANVL(expr NUMERIC, substitute VARCHAR) returns NUMERIC
General rules
NANVL returns a substitute value when the specified value is not a number (NaN). The substitute value can be either a number or a string that can be converted to a number.
For expr and substitute, specify a numeric data type. If expr and substitute have different data types, they will be converted to the data type with greater length or precision, and that is the data type that will be returned.
For substitute, you can also specify a string indicating the numeric value.
The data type used for the return value if a string is specified for the substitute value will be the same as the data type of expr.
Example
In the following example, "0" is returned if the value of col1 in table t1 is a NaN value.
SELECT col1, NANVL(col3,0) FROM t1;
col1 | nanvl
------+-------
2001 | 0
(1 row)
Description
Returns a substitute value when a value is NULL.
Syntax
NVL(expr1 anyelement, expr2 anyelement) return anyelement
General rules
NVL returns a substitute value when the specified value is NULL. When expr1 is NULL, expr2 is returned. When expr1 is not NULL, expr1 is returned.
Specify the same data types for expr1 and expr2. However, if a constant is specified in expr2, and the data type can also be converted by expr1, different data types can be specified. When this happens, the conversion by expr2 is done to suit the data type in expr1, so the value of expr2 returned when expr1 is a NULL value will be the value converted in the data type of expr1. This is not necessary for types (numeric, int) and (bigint, int).
Example
In the following example, "IS NULL" is returned if the value of col1 in table t1 is a NULL value.
SELECT col2, NVL(col1,'IS NULL') "nvl" FROM t1;
col2 | nvl
------+---------
aaa | IS NULL
(1 row)
Description
Returns a substitute value based on whether a value is NULL or not NULL.
Syntax
NVL2(expr anyelement, substitute1 anyelement, substitute2 anyelement) return anyelement
General rules
NVL2 returns a substitute value based on whether the specified value is NULL or not NULL. When expr is NULL, substitute2 is returned. When it is not NULL, substitute1 is returned.
Specify the same data types for expr, substitute1, and substitute2. However, if a literal is specified in substitute1 or substitute2, and the data type can also be converted by expr, different data types can be specified. When this happens, substitute1 or substitute2 is converted to suit the data type in expr, so the value of substitute2 returned when expr is a NULL value will be the value converted to the data type of expr.
Example
In the following example, if a value in column col1 in table t1 is NULL, "IS NULL" is returned, and if not NULL, "IS NOT NULL" is returned.
SELECT col2, NVL2(col1,'IS NOT NULL','IS NULL') FROM t1;
col2 | nvl2
------+---------
aaa | IS NULL
bbb | IS NOT NULL
(2 row)
The following aggregation functions are supported:
ANY_VALUE
BIT_AND_AGG
BIT_OR_AGG
BIT_XOR_AGG
KURTOSIS_POP
KURTOSIS_SAMP
LISTAGG
MEDIAN
SKEWNESS_POP
SKEWNESS_SAMP
WY_CONCAT
Description
ANY_VALUE returns a single non-deterministic value of expr.
Syntax
ANY_VALUE(expr anyelement) returns anyelement
General rules
Use ANY_VALUE to optimize a query that has a GROUP BY clause. ANY_VALUE returns a value of an expression in a group. It is optimized to return the first value.
It ensures that there are no comparisons for any incoming row and also eliminates the necessity to specify every column as part of the GROUP BY clause.
Because it does not compare values, ANY_VALUE returns a value more quickly than MIN or MAX in a GROUP BY query.
Returns any value within each group based on the GROUP BY specification. Returns NULL if all rows in the group have NULL expression values.
The result of ANY_VALUE is not deterministic.
Note
If using ANY_VALUE, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.
Example
create table test_any_value(key1 int, key2 int);
insert into test_any_value values(1, 1);
select any_value(key2) from test_any_value group by key1;
any_value
-----------
1
(1 row)
Description
BIT_AND_AGG is a bitwise aggregation function that returns the result of a bitwise AND operation.
Syntax
BIT_AND_AGG(numeric) returns int
General rules
You can use BIT_AND_AGG as part of a GROUP BY query, window function, or as an analytical function.
For a given set of values, the result of a bitwise aggregate is always deterministic and independent of ordering.
The data type of the return value is int.
Note
If using BIT_AND_AGG, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.
Example
select bit_and_agg(column1) from (values (1),(2),(4),(8)) x;
bit_and_agg
-------------
0
(1 row)
Description
BIT_OR_AGG is a bitwise aggregation function that returns the result of a bitwise OR operation.
Syntax
BIT_OR_AGG(numeric) returns int
General rules
You can use BIT_OR_AGG as part of a GROUP BY query, window function, or as an analytical function.
For a given set of values, the result of a bitwise aggregate is always deterministic and independent of ordering.
The data type of the return value is int.
Note
If using BIT_OR_AGG, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.
Example
select bit_or_agg(column1) from (values (1),(2),(4),(8)) x;
bit_or_agg
------------
15
(1 row)
Description
BIT_XOR_AGG is a bitwise aggregation function that returns the result of a bitwise XOR operation.
Syntax
BIT_XOR_AGG(numeric) returns int
General rules
You can use BIT_XOR_AGG as part of a GROUP BY query, window function, or as an analytical function.
For a given set of values, the result of a bitwise aggregate is always deterministic and independent of ordering.
The data type of the return value is int.
Note
If using BIT_XOR_AGG, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.
Example
select bit_xor_agg(column1) from (values (1),(3),(4),(8)) x;
bit_xor_agg
-------------
14
(1 row)
Description
The population kurtosis function KURTOSIS_POP is primarily used to determine the characteristics of outliers in a given distribution.
Syntax
KURTOSIS_POP(numeric) returns numeric
General rules
NULL values in expr are ignored.
Returns NULL if all rows in the group have NULL expr values.
Returns 0 if there are one or two rows in expr.
For a given set of values, the result of population kurtosis (KURTOSIS_POP) and sample kurtosis (KURTOSIS_SAMP) are always deterministic. However, the values of KURTOSIS_POP and KURTOSIS_SAMP differ. As the number of values in the data set increases, the difference between the computed values of KURTOSIS_SAMP and KURTOSIS_POP decreases.
Note
If using the KURTOSIS_POP function, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.
Example
select kurtosis_pop(column1) from (values (1),(2),(4),(8)) x;
kurtosis_pop
-------------------------
-1.09897920604914942667
(1 row)
Description
The sample kurtosis function KURTOSIS_SAMP is primarily used to determine the characteristics of outliers in a given distribution.
Syntax
KURTOSIS_SAMP(numeric) returns numeric
General rules
NULL values in expr are ignored.
Returns NULL if all rows in the group have NULL expr values.
Returns 0 if there are one or two rows in expr.
For a given set of values, the result of sample kurtosis (KURTOSIS_SAMP) and population kurtosis (KURTOSIS_POP) are always deterministic. However, the values of KURTOSIS_SAMP and KURTOSIS_POP differ. As the number of values in the data set increases, the difference between the computed values of KURTOSIS_SAMP and KURTOSIS_POP decreases.
Note
If using the KURTOSIS_SAMP function, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.
Example
select kurtosis_samp(column1) from (values (1),(2),(4),(8)) x;
kurtosis_samp
--------------------
0.7576559546313793
(1 row)
Description
Returns a concatenated, delimited list of string values.
Syntax
LISTAGG(strExpr TEXT) returns TEXT
LISTAGG(strExpr TEXT, delimiter TEXT) []returns TEXT
LISTAGG(strExpr TEXT, delimiter TEXT ON OVERFLOW TRUNCATE) []returns TEXT
LISTAGG(strExpr TEXT, delimiter TEXT ON OVERFLOW ERROR) []returns TEXT
General rules
LISTAGG concatenates and delimits a set of string values and returns the result.
For delimiter, specify a string. If the delimiter is omitted, a list of strings without a delimiter is returned.
In LightDB, LISTAGG support using distinct in function with within group clauses. e.g: LISTAGG(distinct c1) within group(order by c1)
In LightDB, LISTAGG support using on overflow truncate。 e.g: LISTAGG(c1, ':' on overflow truncate)
In LightDB, LISTAGG support using on overflow error。 e.g: LISTAGG(c1, ':' on overflow error)
In LightDB, LISTAGG support using within group clauses with over clauses. In this scenario we cannot use distinct in function yet. e.g: LISTAGG(c1) within group(order by c1) over(partition by c2)
The data type of the return value is TEXT.
Example
In the following example, the result with values of column col2 in table t1 delimited by ':' is returned.
SELECT LISTAGG(col2,':') FROM t1;
listagg
-------------------
AAAAA:BBBBB:CCCCC
(1 row)
In the following example, using within group with over.
CREATE TABLE EMP
( EMPNO NUMBER(4, 0),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4, 0),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2, 0),
DNAME VARCHAR2(100),
CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)
);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7369, 'SMITH', 'CLERK', 7902, to_date('1980-12-17','yyyy-mm-dd'), 800.00, null, 20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('1981-02-20','yyyy-mm-dd'), 1600.00, 300.00, 30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7521, 'WARD', 'SALESMAN', 7698, to_date('1981-02-22','yyyy-mm-dd'), 1250.00, 500.00, 30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7566, 'JONES', 'MANAGER', 7839, to_date('1981-04-02','yyyy-mm-dd'), 2975.00, null, 20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('1981-09-28','yyyy-mm-dd'), 1250.00, 1400.00, 30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7698, 'BLAKE', 'MANAGER', 7839, to_date('1981-05-01','yyyy-mm-dd'), 2850.00, null, 30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7782, 'CLARK', 'MANAGER', 7839, to_date('1981-06-09','yyyy-mm-dd'), 2450.00, null, 10, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7788, 'SCOTT', 'ANALYST', 7566, to_date('1987-04-19','yyyy-mm-dd'), 3000.00, null, 20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7839, 'KING', 'PRESIDENT',null, to_date('1981-11-17','yyyy-mm-dd'), 5000.00, null, 10, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7844, 'TURNER', 'SALESMAN', 7698, to_date('1981-09-08','yyyy-mm-dd'), 1500.00, 0.00, 30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7876, 'ADAMS', 'CLERK', 7788, to_date('1987-05-23','yyyy-mm-dd'), 1100.00, null, 20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7900, 'JAMES', 'CLERK', 7698, to_date('1981-12-03','yyyy-mm-dd'), 950.00, null, 30, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7902, 'FORD', 'ANALYST', 7566, to_date('1981-12-03','yyyy-mm-dd'), 3000.00, null, 20, null);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME) values (7934, 'MILLER', 'CLERK', 7782, to_date('1982-01-23','yyyy-mm-dd'), 1300.00, null, 10, null);
select listagg(ename, ',') within group(order by ename) over(partition by deptno) as enames,
deptno,
ename
from EMP;
enames | deptno | ename
--------------------------------------+--------+--------
CLARK,KING,MILLER | 10 | CLARK
CLARK,KING,MILLER | 10 | KING
CLARK,KING,MILLER | 10 | MILLER
ADAMS,FORD,JONES,SCOTT,SMITH | 20 | ADAMS
ADAMS,FORD,JONES,SCOTT,SMITH | 20 | FORD
ADAMS,FORD,JONES,SCOTT,SMITH | 20 | JONES
ADAMS,FORD,JONES,SCOTT,SMITH | 20 | SCOTT
ADAMS,FORD,JONES,SCOTT,SMITH | 20 | SMITH
ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD | 30 | ALLEN
ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD | 30 | BLAKE
ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD | 30 | JAMES
ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD | 30 | MARTIN
ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD | 30 | TURNER
ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD | 30 | WARD
(14 rows)
Description
Calculates the median of a set of numbers.
Syntax
MEDIAN(numExpr REAL) returns REAL
MEDIAN(numExpr DOUBLE PRECISION) returns DOUBLE PRECISION
General rules
MEDIAN returns the median of a set of numbers.
The numbers must be numeric data type.
The data type of the return value will be REAL if the numbers are REAL type, or DOUBLE PRECISION if any other type is specified.
Example
In the following example, the median of column col3 in table t1 is returned.
SELECT MEDIAN(col3) FROM t1;
median
--------
2000
(1 row)
Description
SKEWNESS_POP is an aggregate function that is primarily used to determine symmetry in a given distribution.
Syntax
SKEWNESS_POP(numeric) returns numeric
General rules
NULL values in expr are ignored.
Returns NULL if all rows in the group have NULL expr values.
Returns 0 if there are one or two rows in expr.
For a given set of values, the result of population skewness (SKEWNESS_POP) and sample skewness (SKEWNESS_SAMP) are always deterministic. However, the values of SKEWNESS_POP and SKEWNESS_SAMP differ. As the number of values in the data set increases, the difference between the computed values of SKEWNESS_SAMP and SKEWNESS_POP decreases.
Note
If using the SKEWNESS_POP function, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.
Example
SELECT SKEWNESS_POP(column1) from (values (1),(2),(4),(8)) x;
skewness_pop
------------------------
0.65680773449969915746
(1 row)
Description
SKEWNESS_SAMP is an aggregate function that is primarily used to determine symmetry in a given distribution.
Syntax
SKEWNESS_SAMP(numeric) returns numeric
General rules
NULL values in expr are ignored.
Returns NULL if all rows in the group have NULL expr values.
Returns 0 if there are one or two rows in expr.
For a given set of values, the result of population skewness (SKEWNESS_POP) and sample skewness (SKEWNESS_SAMP) are always deterministic. However, the values of SKEWNESS_POP and SKEWNESS_SAMP differ. As the number of values in the data set increases, the difference between the computed values of SKEWNESS_SAMP and SKEWNESS_POP decreases.
Note
If using the SKEWNESS_SAMP function, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.
Example
SELECT SKEWNESS_SAMP(column1) from (values (1),(2),(4),(8)) x;
skewness_samp
------------------------
1.13762436695768880892
(1 row)
Description
Returns a concatenated, delimited list of string values.
Syntax
WY_CONCAT(strExpr TEXT) returns CLOB
General rules
WY_CONCAT concatenates and delimits a set of string values and returns the result.
The data type of the return value is CLOB.
Example
In the following example, the result with values of column col2 in table t1 delimited by ',' is returned.
SELECT WY_CONCAT(col2) FROM t1;
wy_concat
-------------------
AAAAA,BBBBB,CCCCC
(1 row)
The following functions that return internal information are supported:
DUMP
NLS_CHARSET_ID
NLS_CHARSET_NAME
SYS_CONTEXT
USERENV
Description
Returns internal information of a value.
Syntax
DUMP(expr TEXT) returns VARCHAR
DUMP(expr TEXT, fmt INTEGER) returns VARCHAR
General rules
DUMP returns the internal information of the values specified in expressions in a display format that is in accordance with the output format.
The internal code (Typ) of the data type, the data length (Len) and the internal expression of the data are output as internal information.
Any data type can be specified for the expressions.
The display format (base n ) of the internal expression of the data is specified for the output format. The base numbers that can be specified are 8, 10, and 16. If omitted, 10 is used as the default.
The data type of the return value is VARCHAR.
Note
The information output by DUMP will be the complete internal information. Therefore, the values may change due to product updates, and so on.
Example
In the following example, the internal information of column col1 in table t1 is returned.
SELECT col1, DUMP(col1) FROM t1;
col1 | dump
------+------------------------------------
1001 | Typ=25 Len=8: 32,0,0,0,49,48,48,49
1002 | Typ=25 Len=8: 32,0,0,0,49,48,48,50
1003 | Typ=25 Len=8: 32,0,0,0,49,48,48,51
(3 row)
Description
NLS_CHARSET_ID returns the character set ID number corresponding to character set name string.
Syntax
NLS_CHARSET_ID(str text) returns integer
General rules
Invalid character set names return null.
Note
The character set name is from Oracle, use this function only for compatibility.
If using the NLS_CHARSET_ID function, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.
Example
The following example returns the character set ID of a character set:
SELECT NLS_CHARSET_ID('AL32UTF8') FROM DUAL;
nls_charset_id
----------------
873
(1 row)
Description
NLS_CHARSET_NAME returns the name of the character set corresponding to ID number number.
Syntax
NLS_CHARSET_NAME(str text) returns integer
General rules
If number is not recognized as a valid character set ID, then this function returns null.
Note
The character set name is from Oracle, use this function only for compatibility.
If using the NLS_CHARSET_NAME function, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.
Example
The following example returns the character set corresponding to character set ID number 873:
SELECT NLS_CHARSET_NAME(873) FROM DUAL;
nls_charset_name
------------------
AL32UTF8
(1 row)
Description
Returns the value of parameter associated with the context namespace at the current instant
Syntax
SYS_CONTEXT(namespace text, parameter text, length int4 default 256) returns text
General rules
For namespace and parameter, you can specify either a string or an expression that resolves to a string designating a namespace or an attribute.
The context namespace must already have been created, and the associated parameter and its value must also have been set.
The namespace must be a valid identifier. The parameter name can be any string. It is not case sensitive, but it cannot exceed 4000 bytes in length.
Note
LightDB provides the following built-in namespaces:
USERENV - Describes the current session. The predefined parameters of namespace USERENV are listed in table Table 9.105
SYS_SESSION_ROLES - Indicates whether a specified role is currently enabled for the session.
Table 9.105. Predefined Parameters of Namespace USERENV
|
Parameters |
Overview |
|---|---|
|
CLIENT_IDENTIFIER |
The name of the program used for the database session |
|
CLIENT_INFO |
The name of the program used for the database session |
|
CLIENT_PROGRAM_NAME |
The name of the program used for the database session |
|
CDB_NAME |
Current database |
|
CON_ID |
Always 1 |
|
CON_NAME |
Current database |
|
CURRENT_SCHEMA |
Current schema |
|
CURRENT_SCHEMAID |
Current schema id |
|
CURRENT_USER |
Current user |
|
CURRENT_USERID |
Current user id |
|
DATABASE_ROLE |
The role is one of the following: PRIMARY, PHYSICAL STANDBY |
|
DB_NAME |
Current database |
|
DB_UNIQUE_NAME |
Current database |
|
HOST |
Name of the host machine from which the client has connected |
|
INSTANCE |
Always 1 |
|
INSTANCE_NAME |
Always 'LightDB' |
|
IP_ADDRESS |
IP address of the machine from which the client is connected |
|
ISDBA |
Returns TRUE if the user has been authenticated as having DBA privileges |
|
LANG |
The abbreviated name for the language, a shorter form than the existing 'LANGUAGE' parameter |
|
LANGUAGE |
The language and territory currently used by your session, along with the database character set |
|
MODULE |
The name of the program used for the database session |
|
NETWORK_PROTOCOL |
Network protocol being used for communication |
|
NLS_DATE_FORMAT |
The date format for the session |
|
ORACLE_HOME |
The full path name for the data home directory |
|
PID |
Current process ID |
|
SERVER_HOST |
listen_addresses |
|
SERVICE_NAME |
Current database |
|
SESSION_USER |
Current user |
|
SESSION_USERID |
Current user id |
|
SESSIONID |
Current session process id |
|
SID |
Current session process id |
Note
If using SYS_CONTEXT, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.
Example
The following statement returns the name of the user who logged onto the database:
SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') FROM DUAL;
sys_context
-------------
lightdb
(1 row)
Description
Returns information about the current session
Syntax
USERENV(parameter text) returns text
General rules
This information can be useful for writing an application-specific audit trail table or for determining the language-specific characters currently used by your session.
Table Table 9.105 describes the values for the parameter argument.
Note
If using USERENV, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.
Example
The following example returns the LANGUAGE parameter of the current session:
SELECT USERENV('LANGUAGE') "Language" FROM DUAL;
Language
-------------
en_US.UTF-8
(1 row)
The following datetime operators are supported for the DATE type of orafce.
Table 9.106. Datetime operator
| Operation | Example | Result |
|---|---|---|
| + | DATE'2016/01/01' + 10 | 2016-01-11 00:00:00 |
| - | DATE'2016/03/20' - 35 | 2016-02-14 00:00:00 |
| - | DATE'2016/09/01' - DATE'2015/12/31' | 245 |
Note
If using datetime operators for the DATE type of orafce, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Oracle compatibility mode supports the following JSON functions.
JSON_OBJECT
JSON_SERIALIZE
JSON_VALUE
JSON_TABLE
JSON_MERGEPATCH
Description
Constructs a JSON document from a list of keys and values separated by the VALUE keyword.
Syntax
JSON_OBJECT(expr VALUE expr, ...) returns json
General rules
The data type of the return value is json. Compatible with varchar2.
Note
When calling the json_object(expr VALUE expr, ...) function, you cannot add a schema qualifier before the function.
The number of key-value pairs cannot exceed 100.
Example
In the following example, a JSON document consisting of key-value pairs is returned.
SELECT JSON_OBJECT('name' VALUE 'John', 'age' VALUE 30) AS json_obj FROM dual;
json_obj
-------------------------------
{"name" : "John", "age" : 30}
(1 row)
Description
Takes JSON data as input and returns its textual representation.
Syntax
JSON_SERIALIZE(varchar2) returns json
General rules
The data type of the return value is json. Compatible with varchar2.
Note
The function JSON_SERIALIZE(varchar2) checks the format of the input JSON data and returns null if the format is incorrect.
Example
In the following example, takes JSON data as input and returns its textual representation.
lightdb@oraddd=# SELECT JSON_SERIALIZE ('{"a": {"b":"foo"}}') as json_string FROM dual;
json_string
--------------------
{"a": {"b":"foo"}}
(1 row)
Description
The SQL/JSON function JSON_VALUE looks for the specified scalar JSON value in the JSON data.
Syntax
JSON_VALUE(expr, ...) returns text
General rules
For expr, specify an expression that evaluates to text.
This function evaluates expr using the path expression path_expression to find a scalar JSON value that matches or satisfies the path expression. The path expression must be a text literal.
Note
When calling the JSON_VALUE(expr, path_expression) function, you cannot add a schema qualifier before the function.
Example
In the following example, finding the specified scalar JSON value in the JSON data.
select JSON_VALUE('{"key1":"v1","key2":"v2"}', '$.key1');
json_value
------------
v1
(1 row)
Description
The SQL/JSON function JSON_TABLE creates a relational view of JSON data. It maps the result of a JSON data evaluation into relational rows and columns. You can query the result returned by the function as a virtual relational table using SQL. The main purpose of JSON_TABLE is to create a row of relational data for each object inside a JSON array and output JSON values from within that object as individual SQL column values.
Syntax
JSON_TABLE(expr, path_expression COLUMNS( column_name Typename [ FORMAT JSON ] PATH path_expression [, column_name [ FORMAT JSON ] PATH path_expression] ))
General rules
The function first applies a path expression, called a SQL/JSON row path expression, to the supplied JSON data.
The JSON value that matches the row path expression is called a row source in that it generates a row of relational data. The COLUMNS clause evaluates the row source, finds specific JSON values within the row source, and returns those JSON values as SQL values in individual columns of a row of relational data.
Note
You must specify JSON_TABLE only in the FROM clause of a SELECT statement.
Example
In the following example, querying JSON data for a specific JSON value.
SELECT * FROM JSON_TABLE(
'[{"name":"John","age":30},{"name":"Jane","age":25}]',
'$[*]' COLUMNS (
name VARCHAR2(100) PATH '$.name',
age NUMBER PATH '$.age'
)
);
name | age
------+-----
John | 30
Jane | 25
(2 rows)
Description
JSON_MERGEPATCH is a standardized functionality for modifying a target JSON document using a JSON document called a merge patch. The function is described in RFC 7396.
Syntax
JSON_MERGEPATCH(target_expr, patch_expr)
General rules
target_expr evaluates to the JSON value target document.
patch_expr evaluates to the JSON value patch document.
Note
JSON_MERGEPATCH evalutes the patch document against the target document to produce the result document. If the target or the patch document is NULL, then the result is also NULL.
Example
In the following example, the patch document is merged into the target document to generate the resulting document.
SELECT JSON_MERGEPATCH('{"name":"John","age":30}', '{"age":31,"city":"New York"}') AS json_merged FROM dual;
json_merged
-------------------------------------------------
{"age": 31, "city": "New York", "name": "John"}
(1 row)
The following functions is used for other purpose:
EMPTY_CLOB
EMPTY_BLOB
ORA_HASH
VSIZE
DEPS_SAVE_AND_DROP_DEPENDENCIES/DEPS_RESTORE_DEPENDENCIES
PLVSTR.IS_PREFIX
PLVDATE.ISLEAPYEAR
TIMESTAMP_TO_SCN
Description
Return an empty CLOB
Syntax
EMPTY_CLOB() RETURNS clob
General rules
can be used to initialize a CLOB variable, or, in an INSERT or UPDATE statement, to initialize a CLOB column or attribute to EMPTY. EMPTY means that the CLOB is initialized, but not populated with data.
has a length of zero but is not null.
Note
If using the EMPTY_CLOB function, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.
Example
select length(empty_clob()) FROM DUAL;
length
--------
0
(1 row)
select count(*) from dual where empty_clob() is null;
count
-------
0
(1 row)
create table foo (a int, b clob default empty_clob());
insert into foo(a) values(1);
insert into foo values (2, 'hello');
select count(*) from foo where b is not null;
count
-------
2
(1 row)
select count(*) from foo where length(b) > 0;
count
-------
1
(1 row)
Description
Return an empty BLOB
Syntax
EMPTY_BLOB() RETURNS blob
General rules
can be used to initialize a BLOB variable, or, in an INSERT or UPDATE statement, to initialize a BLOB column or attribute to EMPTY. EMPTY means that the BLOB is initialized, but not populated with data.
has a length of zero but is not null.
Example
select length(empty_blob()) FROM DUAL;
length
--------
0
(1 row)
select count(*) from dual where empty_blob() is null;
count
-------
0
(1 row)
create table foo (a int, b blob default empty_blob());
insert into foo(a) values(1);
insert into foo values (2, to_blob('616263'));
select count(*) from foo where b is not null;
count
-------
2
(1 row)
select count(*) from foo where length(b) > 0;
count
-------
1
(1 row)
Description
ORA_HASH is a function that computes a hash value for a given expression. This function is useful for operations such as analyzing a subset of data and generating a random sample.
Syntax
ORA_HASH(p_data anyelement,p_buckets int4, p_seed int4) RETURNS int
General rules
The p_data argument determines the data for which you want LightDB Database to compute a hash value. There are no restrictions on the length of data represented by expr, which commonly resolves to a column name.
The optional p_buckets argument determines the maximum bucket value returned by the hash function. You can specify any value between 0 and 2147483647. The default is 0.
The optional p_seed argument enables LightDB to produce many different results for the same set of data. LightDB applies the hash function to the combination of expr and p_seed. You can specify any value between 0 and 2147483647. The default is 0.
The data type of the return value is int.
Note
If using ORA_HASH, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.
Example
select ora_hash('abcdAbcdasd'::text) FROM DUAL;
ora_hash
-----------
303228277
(1 row)
Description
VSIZE returns the number of bytes in the internal representation of expr. If expr is null, then this function returns null.
Syntax
VSIZE(anyelement) RETURNS integer
General rules
Same with pg_column_size.
Note
If using VSIZE, it is necessary to specify "oracle" for search_path in advance.
See
Refer to "Notes on Using orafce" for information on how to edit search_path.
Refer to "Server Administration" > "Server Configuration" > "Setting Parameters" in the LightDB Documentation for information on how to set the server parameters.
Example
select vsize(123456789) FROM DUAL;
vsize
-------
4
(1 row)
Description
These functions are used to modify tables that have view dependencies. Recursively backup all dependent views, then modify base tables, then recreate all backuped views.
DEPS_SAVE_AND_DROP_DEPENDENCIES save and drop dependencies for table.
DEPS_RESTORE_DEPENDENCIES restore dependencies for table.
Syntax
DEPS_SAVE_AND_DROP_DEPENDENCIES(name, name default current_schema()::name, jsonb default '{}'::jsonb) RETURNS void
DEPS_RESTORE_DEPENDENCIES(name, name default current_schema()::name, jsonb default '{}'::jsonb)
General rules
Create under lt_catalog.
dependencies is saved in lt_catalog.deps_saved_ddl table.
dry_run Run without actually dropping dependencies, default false.
verbose Show debug log, , default false.
populate_materialized_view Enable or disable materialized view refresh-on-create via WITH [NO] DATA flag, default false.
Note
If using DEPS_SAVE_AND_DROP_DEPENDENCIES/DEPS_RESTORE_DEPENDENCIES, it is not necessary to specify "oracle" for search_path in advance.
Example
create table test_t(key1 int);
create view test_t_v as select * from test_t;
begin;
select deps_save_and_drop_dependencies('test_t');
alter table test_t modify key1 number;
select * from deps_restore_dependencies('test_t');
commit;
Description
PLVSTR.IS_PREFIX return true if a string or number is the prefix of another string or number.
Syntax
PLVSTR.IS_PREFIX(p bigint, prefix bigint) RETURNS boolean
PLVSTR.IS_PREFIX(p integer, prefix integer) RETURNS boolean
PLVSTR.IS_PREFIX(p text, prefix text) RETURNS boolean
General rules
This function can be useful for testing whether a string or number begins with something.
Example
select plvstr.is_prefix(111, 11) FROM DUAL;
is_prefix
-----------
t
(1 row)
select plvstr.is_prefix('abc', 'AB') from dual;
is_prefix
-----------
f
(1 row)
Description
PLVDATE.ISLEAPEAR return true if the date is a leap year.
Syntax
PLVDATE.ISLEAPYEAR(date) RETURNS boolean
Example
select plvdate.isleapyear(date '2023-01-01') FROM DUAL;
isleapyear
-----------
f
select plvdate.isleapyear(date '2020-01-01') from dual;
isleapyear
-----------
t
Description
Thetimestamp_to_scnfunction returns a transaction ID
whose corresponding commit timestamp is less than or equal to the given timestamp.
Syntax
TIMESTAMP_TO_SCN(timestamptz) RETURNS bigint
Example
set orafce.timezone = 'Asia/Shanghai';
select timestamp_to_scn(sysdate) FROM DUAL;
timestamp_to_scn
-----------
12345