Description

BIN_TO_NUM converts a bit vector to its equivalent number.

Syntax

      BIN_TO_NUM(VARIADIC integer []) returns int
      

General rules

Note

See

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

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

Note

See

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

Note

See

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

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

Note

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

Note

See

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

Note

See

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

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

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

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)
      

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

Note

Information

The general rule for BTRIM of LightDB is as follows:

See

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

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

Note

See

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

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:

See

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

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

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:

See

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

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:

See

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

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

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

The value of flags can include one or more of the following characters:

If you omit flags then:

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

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

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

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

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

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:

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

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:

See

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

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:

See

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

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

Note

Information

The general rules for SUBSTRING of LightDB are as follows:

See

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

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

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

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)
      

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

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

Note

See

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

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

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

Values that can be specified for the day

Note

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

Values that can be specified for the format model

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

Note

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

Note

See

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

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

Note

See

Example

  SELECT TZ_OFFSET('Asia/Shanghai') FROM DUAL;
  tz_offset
  -----------
  08:00:00
  (1 row)
      (1 row)
      

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

Note

See

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

Note

See

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

See

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

Note

See

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

Note

Information

The general rule for TO_DATE for specifying the data type format of LightDB is as follows:

See

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

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

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

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

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

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

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

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

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

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)
      

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

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:

Y: Can be converted

N: Cannot be converted

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

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

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

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

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)
      

Description

ANY_VALUE returns a single non-deterministic value of expr.

Syntax

      ANY_VALUE(expr anyelement) returns anyelement
      

General rules

Note

See

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

Note

See

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

Note

See

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

Note

See

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

Note

See

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

Note

See

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

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

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

Note

See

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

Note

See

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

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)
      

Description

Returns internal information of a value.

Syntax

      DUMP(expr TEXT) returns VARCHAR
      DUMP(expr TEXT, fmt INTEGER) returns VARCHAR
      

General rules

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

Note

See

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

Note

See

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

Note

LightDB provides the following built-in namespaces:

Note

See

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

Note

See

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)
      

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

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

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

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

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

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)
      

Description

Return an empty CLOB

Syntax

      EMPTY_CLOB() RETURNS clob
      

General rules

Note

See

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

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

Note

See

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

Note

See

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

Note

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

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