Function | Description | Example |
nvl(anyelement, anyelement)
| If the value of the first argument is null, this function returns the value of the second argument. If the value of the first argument is not null, this function returns the value of the first argument.
Note The data types of the two arguments must be the same. | -
The following information is returned:
-
The following information is returned:
-
The following information is returned:
|
add_months(day date, value int)RETURNS date
| This function adds the number of months that is specified by the second argument to the date that is specified by the first argument and returns a date. |
SELECT add_months(current_date, 2);
The following information is returned:
add_months
2019-08-31
(1 row)
|
last_day(value date)
| This function returns the last day of the month for the specified date. The return value is a date. |
SELECT last_day('2018-06-01');
The following information is returned:
last_day
2018-06-30
(1 row)
|
next_day(value date, weekday text)
| This function returns the date that represents a day of the second week since the start date. Example: the date that represents the second Friday. |
SELECT next_day(current_date, 'FRIDAY');
The following information is returned:
next_day
2019-07-05
(1 row)
|
next_day(value date, weekday integer)
| The first argument specifies a start date. The second argument specifies a number that represents a day of a week. The number ranges from 1 to 7. The value 1 represents Sunday, and the value 2 represents Monday. In a similar manner, the value 7 represents Saturday.
This function returns a date that is a specific number of days later than the start date. | |
months_between(date1 date, date2 date)
| This function returns the number of months between date1 and date2. If date1 is later than date2, the return value is positive. If date1 is earlier than date2, the return value is negative.
| |
trunc(value timestamp with time zone, fmt text)
| The first argument specifies the timestamp that you want to truncate. The second argument specifies the precision based on which the timestamp is truncated, such as year, month, day, week, hour, minute, or second. Y: specifies that the timestamp is truncated to the first day of the year that corresponds to the timestamp. Q: specifies that the timestamp is truncated to the first day of the quarter that corresponds to the timestamp.
| |
trunc(value timestamp with time zone)
| This function truncates a timestamp. The hour, minute, and second values of the specified timestamp are truncated by default. |
SELECT TRUNC('2019-12-11'::timestamp);
The following information is returned:
trunc
2019-12-11 00:00:00+08
(1 row)
|
trunc(value date)
| This function truncates a date. |
SELECT TRUNC('2019-12-11'::timestamp,'Y');
The following information is returned:
trunc
2019-01-01 00:00:00+08
|
round(value timestamp with time zone, fmt text)
| This function rounds a timestamp to the nearest value based on units such as week or day. |
SELECT round('2018-10-06 13:11:11'::timestamp, 'YEAR');
The following information is returned:
round
2019-01-01 00:00:00+08
(1 row)
|
round(value timestamp with time zone)
| This function rounds a timestamp to the nearest value based on the unit day. |
SELECT round('2018-10-06 13:11:11'::timestamp);
The following information is returned:
round
2018-10-07 00:00:00+08
(1 row)
|
round(value date, fmt text)
| This function returns a rounded date. | |
round(value date)
| This function returns a rounded date. |
SELECT round(TO_DATE('27-FEB-00','DD-MON-YY'));
The following information is returned:
|
instr(str text, patt text, start int, nth int)
| This function searches for a substring in a string. If a substring is obtained, the function returns the position of the substring. Otherwise, the function returns 0. | |
instr(str text, patt text, start int)
| The nth argument is not specified. This function returns the position of the first occurrence of the substring. |
SELECT instr('Greenplum', 'e',1);
The following information is returned:
|
instr(str text, patt text)
| The start argument is not specified. This function searches for the substring from the start of a string. |
SELECT instr('Greenplum', 'e');
The following information is returned:
|
plvstr.rvrs(str text, start int, end int)
| This function reverses the order of characters in a specified string. The str argument specifies the string, and the start and end argument specify the start position and end position of the characters whose order you want to reverse. |
SELECT plvstr.rvrs('adb4pg', 5,6);
The following information is returned:
|
plvstr.rvrs(str text, start int)
| This function reverses the order of characters from the character that is specified by the start argument to the end of the string. |
SELECT plvstr.rvrs('adb4pg', 4);
The following information is returned:
|
plvstr.rvrs(str text)
| This function reverses the order of an entire string. |
SELECT plvstr.rvrs('adb4pg');
The following information is returned:
|
concat(text, text)
| This function joins two strings together. |
SELECT concat('adb','4pg');
The following information is returned:
|
concat(text, anyarray)/concat(anyarray, text)/concat(anyarray, anyarray)
| This function joins data of the same or different data types together. | -
SELECT concat('adb4pg', 6666);
The following information is returned:
concat
adb4pg6666
(1 row)
-
SELECT concat(6666, 6666);
The following information is returned:
-
SELECT concat(current_date, 6666);
The following information is returned:
concat
2019-06-306666
(1 row)
|
nanvl(float4, float4)/nanvl(float4, float4)/nanvl(numeric, numeric)
| If the first argument is of the NUMERIC data type, this function returns the value of the first argument. Otherwise, this function returns the value of the second argument. | |
bitand(bigint, bigint)
| This function performs an AND operation for two binary numbers of the INTEGER data type. Only one row is returned. | -
The following information is returned:
-
The following information is returned:
-
The following information is returned:
|
listagg(text)
| This function returns a clustered string for texts. |
SELECT listagg(t) FROM (VALUES('abc'), ('def')) as l(t);
The following information is returned:
|
listagg(text, text)
| This function returns a clustered string for texts. The value of the second argument is used as a separator. |
SELECT listagg(t, '.') FROM (VALUES('abc'), ('def')) as l(t);
The following information is returned:
|
nvl2(anyelement, anyelement, anyelement)
| If the value of the first argument is null, this function returns the value of the third argument. Otherwise, this function returns the value of the second argument. | |
lnnvl(bool)
| If the value of the argument is null or false, this function returns true. If the value of the argument is true, this function returns false. | -
The following information is returned:
-
The following information is returned:
-
The following information is returned:
|
dump("any")
| This function returns a text that contains the data type code, length in bytes, and the internal representation of the argument. |
The following information is returned:
dump
Typ=705 Len=7: 97,100,98,52,112,103,0
(1 row)
|
dump("any", integer)
| The second argument specifies the format of the return value. The format can be a decimal notation (specified by 10) or a hexadecimal notation (specified by 16). | -
SELECT dump('adb4pg', 10);
The following information is returned:
dump
Typ=705 Len=7: 97,100,98,52,112,103,0
(1 row)
-
SELECT dump('adb4pg', 16);
The following information is returned:
dump
Typ=705 Len=7: 61,64,62,34,70,67,0
(1 row)
-
SELECT dump('adb4pg', 2);
The following information is returned:
ERROR: unknown format (others.c:430)
|
nlssort(text, text)
| This function sorts data in a specific order. | Execute the following statements to create a table and insert data:
CREATE TABLE t1 (name text);
INSERT INTO t1 VALUES('Anne'), ('anne'), ('Bob'), ('bob');
|
substr(str text, start int)
| This function retrieves a substring from the string that is specified by the first argument. The second argument specifies the start position of the substring. | |
substr(str text, start int, len int)
| The third argument specifies the end position of the substring. The value of this argument must be greater than or equal to the value of the start argument and less than or equal to the length of the string. |
SELECT substr('adb4pg', 5,6);
The following information is returned:
|
pg_catalog.substrb(varchar2, integer, integer)
| This function returns a substring from a string of the VARCHAR2 data type. The second argument specifies the start position of the substring, and the third argument specifies the end position of the substring. |
SELECT substr('adb4pg'::varchar2, 5,6);
The following information is returned:
|
pg_catalog.substrb(varchar2, integer)
| This function returns a substring from a string of the VARCHAR2 data type. The substring starts from the character that is specified by the second argument and continues until the end of the string. |
SELECT substr('adb4pg'::varchar2, 4) ;
The following information is returned:
|
pg_catalog.lengthb(varchar2)
| This function returns the number of bytes for a string of the VARCHAR2 data type. If null is specified, the function returns null. If an empty string is specified, the function returns 0. | |
lpad(string char, length int, fill char)
| This function pads a string on the left to a specified length with a sequence of characters. The first argument specifies the string that you want to pad on the left. The second argument specifies the length of the result string after padding. The third argument specifies the string that is used for padding.
Note For strings of the CHAR type, PostgreSQL removes trailing spaces and Oracle does not. |
SELECT lpad('abc '::char(4),6,'x');
The following information is returned:
|
lpad(string char, length int)
| This function pads a string on the left to a specified length with spaces. |
SELECT lpad('abc '::char(4),6);
The following information is returned:
|
regexp_count(string text, pattern text, startPos int, flags text)
| This function returns the number of times a pattern occurs in a source string from the start position. The return value must be an integer. If no pattern is found, the function returns 0. The first argument specifies the source string. The second argument specifies the pattern. The third argument specifies the position from which the search starts. It must be a positive integer. You cannot search for the pattern from the end of the source string. The fourth argument specifies the character expression that you can use to change the default matching behavior of the function. It can include one or more of the following characters: 'i': case-insensitive matching. By default, case-sensitive and accent-sensitive matching is used.
'c': case-sensitive and accent-sensitive matching.
'n': allows a period (.) to match the line feed. By default, a period (.) does not match the line feed.
'm': treats the source string as multiple lines. By default, the source string is treated as a single line.
'x': ignores whitespace characters. By default, whitespace characters match themselves.
| |
regexp_count(string text, pattern text, startPos int)
| This function returns the number of times a pattern occurs in a source string from the start position of the string. The return value must be an integer. If no pattern is found, the function returns 0. |
SELECT regexp_count('abc', '[a-z]',3);
The following information is returned:
|
regexp_count(string text, pattern text)
| This function returns the number of times a pattern occurs in a source string from the beginning of the string. The return value must be an integer. If no pattern is found, the function returns 0. |
SELECT regexp_count('abc', '[a-z]');
The following information is returned:
|
regexp_instr(string text, pattern text, startPos int, occurence int, return_opt int, flags text, group int)
| This function returns the start or end position of a pattern in a source string. The return value must be an integer. The first argument specifies the source string. The second argument specifies the pattern. The third argument specifies the position from which the search starts. It must be a positive integer. You cannot search for the pattern from the end of the source string. The fourth argument specifies the occurrence of the pattern in the source string from which the search starts. It must be a positive integer. The default value is 1, which indicates that the search starts from the first occurrence of the pattern. The fifth argument specifies the start or end position of the pattern in the source string. Valid values: The sixth argument specifies the character expression that you can use to change the default matching behavior of the function. For more information, see REGEXP_COUNT. The seventh argument specifies the serial number of the capturing group in the pattern. It must be a positive integer. Capturing groups can be nested. Capturing groups are numbered based on the order in which their left parentheses appear in the pattern. If the value is 0, the position of the entire substring that matches the pattern is returned. If the value is greater than the number of capturing groups in the pattern, the function returns 0. Default value: 0.
|
SELECT regexp_instr('abxxabc','(a)(b)(c)',1,1,0,'',2);
The following information is returned:
|
regexp_instr(string text, pattern text, startPos int, occurence int, return_opt int, flags text)
| This function returns the start or end position of the specified occurrence of a pattern in a source string from the start position of the string. The return value must be an integer. return_opt specifies the start or end position of the pattern in the source string. Valid values: flags specifies the character expression that you can use to change the default matching behavior of the function. For more information, see REGEXP_COUNT.
|
SELECT regexp_instr('abxxabc','(a)(b)(c)',1,1,0,'');
The following information is returned:
|
regexp_instr(string text, pattern text, startPos int, occurence int, return_opt int)
| This function returns the start or end position of the specified occurrence of a pattern in a source string from the start position of the string. The return value must be an integer. return_opt specifies the start or end position of the pattern in the source string. Valid values: |
SELECT regexp_instr('abc','[a-z]{3}',1,1,1);
The following information is returned:
|
regexp_instr(string text, pattern text, startPos int, occurence int)
| This function returns the position of the specified occurrence of a pattern in a source string from the start position of the string. The return value must be an integer. |
SELECT regexp_instr('abcd','[a-z]{2}',1,2);
The following information is returned:
|
regexp_instr(string text, pattern text, startPos int)
| This function returns the position of a pattern in a source string from the start position of the string. The return value must be an integer. |
SELECT regexp_instr('abc','[a-z]',2);
The following information is returned:
|
regexp_instr(string text, pattern text)
| This function returns the position of a pattern in a source string from the beginning of the string. The return value must be an integer. |
SELECT regexp_instr('abc','[a-z]');
The following information is returned:
|
regexp_like(string text, pattern text, flags text)
| If a substring of a source string matches a pattern, this function returns true. Otherwise, this function returns false. The first argument specifies the source string. The second argument specifies the pattern. The third argument specifies the character expression that you can use to change the default matching behavior of the function. For more information, see REGEXP_COUNT.
|
SELECT regexp_like('a'||CHR(10)||'d', 'a.d', 'n');
The following information is returned:
|
regexp_like(string text, pattern text)
| If a substring of a source string matches a pattern, this function returns true. Otherwise, this function returns false. |
SELECT regexp_like('abc', '[a-z]');
The following information is returned:
|
regexp_substr(string text, pattern text, startPos int, occurence int, flags text)
| This function returns the substring of a source string that matches a pattern. The first argument specifies the source string. The second argument specifies the pattern. The third argument specifies the position from which the search starts. It must be a positive integer. You cannot search for the pattern from the end of the source string. The fourth argument specifies the occurrence of the pattern in the source string from which the search starts. It must be a positive integer. The default value is 1, which indicates that the search starts from the first occurrence of the pattern. The fifth argument specifies the character expression that you can use to change the default matching behavior of the function. For more information, see REGEXP_COUNT.
|
SELECT regexp_substr('a,bc,def', '[^,]+',1,2,'');
The following information is returned:
|
regexp_substr(string text, pattern text, startPos int, occurence int)
| This function returns the substring of a source string that matches a pattern for the specified occurrence from the start position of the string. |
SELECT regexp_substr('a,bc,def', '[^,]+',4,2);
The following information is returned:
regexp_substr
def
(1 row)
|
regexp_substr(string text, pattern text, startPos int)
| This function returns the substring of a source string that matches a pattern from the start position of the string. |
SELECT regexp_substr('a,bc,def', '[^,]+',4);
The following information is returned:
|
regexp_substr(string text, pattern text)
| This function returns the substring of a source string that matches a pattern from the beginning of the string. |
SELECT regexp_substr('a,bc,def', '[^,]+');
The following information is returned:
|
Elastic Compute Service (ECS)
Container Compute Service (ACS)
