In ADB_PG_SQL nodes, string functions allow for flexible string manipulation. This topic outlines the command syntax, parameters, and provides examples for the string functions supported by ADB_PG_SQL nodes, aiding developers in their use.
AnalyticDB for PostgreSQL supports the following string functions:
Function |
Feature |
Calculates the number of bits in a string. |
|
Calculates the number of characters in a string. |
|
Converts a string to lowercase. |
|
Calculates the number of bytes in a string. |
|
Replaces a substring within a string. |
|
Finds the position of a substring within a string. |
|
Extracts a substring from a string. |
|
Extracts a substring matching a POSIX regular expression. |
|
Extracts a substring matching an SQL regular expression. |
|
Trims leading and trailing spaces from a string. |
|
Converts a string to uppercase. |
bit_length
Command format
bit_length(string)Command description: Calculates the number of bits in a string.
Parameter description:
string: Required. Specifies the string to calculate the bit length for.
Return value description: Returns an integer representing the bit length of the specified string.
Example:
SELECT bit_length('Dataphin') -- Returns 64
char_length
Command format
char_length(string) or character_length(string)Command description: Calculates the number of characters in a string.
Parameter description:
string: Required. Specifies the string to calculate the character length for.
Return value description: Returns the character count of the specified string.
Example:
SELECT char_length('Dataphin') or character_length('Dataphin') -- Returns 8
lower
Command format
lower(string)Command description: Converts a string to lowercase.
Parameter description:
string: Required. Specifies the string to convert to lowercase.
Return value description: Returns the lowercase version of the input string.
Example:
SELECT lower('DATAPHIN') -- Returns dataphin
octet_length
Command format
octet_length(string)Command description: Calculates the number of bytes in a string.
Parameter description:
string: Required. Specifies the string to calculate the byte length for.
Return value description: Returns the byte count of the specified string.
Example:
SELECT octet_length('Dataphin') -- Returns 8
overlay
Command format
overlay(string placing string from int [for int])Command description: Replaces a substring within a string.
Parameter description:
string: Required. The original string.
string: Required. The string to insert.
int: Required. The starting index for the replacement.
for int: Optional. The number of characters to replace.
Return value description: Returns a string with the specified substring replaced starting at the given index.
Example:
SELECT overlay('Hello Dataphin' PLACING 'Hi' FROM 1 FOR 5) -- Returns Hi Dataphin
position
Command format
position(substring in string)Command description: Finds the position of a substring within a string.
Parameter description:
substring: Required. The substring to locate.
string: Required. The string within which to search for the substring.
Return value description: Returns the starting index of the first occurrence of the substring within the string. If the substring is not found, the function returns 0.
Example:
SELECT position('Dataphin' IN 'hello Dataphin') -- Returns 7
substring
Command format
substring(string [from int] [for int])Command description: Extracts a substring from a string.
- Parameter description:
string: Required. The string from which to extract the substring.
from int: Optional. The starting position for the extraction.
for int: Optional. The length of the substring to extract.
Return value description: Returns the extracted substring, starting at the specified position and extending for the specified length.
Example:
SELECT substring('hello Dataphin' FROM 6) -- Returns Dataphin
substring
Command format
substring(string from pattern)Command description: Extracts a substring that matches a POSIX regular expression.
Parameter description:
pattern: Required. The POSIX regular expression to match against.
Return value description: Returns the substring that matches the specified POSIX regular expression.
Example:
SELECT substring('hello Dataphin' FROM '^.{6}') -- Returns hello
substring
Command format
substring(string from pattern for escape)Command description: Extracts a substring that matches an SQL regular expression.
Parameter description:
pattern: Required. The SQL regular expression to match against.
escape: Required. The escape character for the regular expression.
Return value description: Returns the substring that matches the specified SQL regular expression, considering the escape character.
Example:
SELECT substring('hello Dataphin' from '%#"__l#"%' for '#') -- Returns ell
trim
Command format
trim(string)Command description: Removes leading and trailing spaces from a string.
Parameter description:
string: Required. The string from which to remove spaces.
Return value description: Returns the string with leading and trailing spaces removed.
Example:
SELECT trim(' Dataphin ') -- Returns Dataphin
upper
Command format
upper(string)Command description: Converts a string to uppercase.
Parameter description:
string: Required. The string to convert to uppercase.
Return value description: Returns the uppercase version of the specified string.
Example:
SELECT upper('dataphin') -- Returns DATAPHIN