SQL defines some string functions that use keywords, rather than commas, to separate arguments.
Description: Number of bytes in binary string
Return type: int
For example:
1 2 3 4 5 | SELECT octet_length(E'jo\\000se'::bytea) AS RESULT; result -------- 5 (1 row) |
Description: Replaces substring.
Return type: bytea
For example:
1 2 3 4 5 | SELECT overlay(E'Th\\000omas'::bytea placing E'\\002\\003'::bytea from 2 for 3) AS RESULT; result ---------------- \x5402036d6173 (1 row) |
Description: Location of specified substring
Return type: int
For example:
1 2 3 4 5 | SELECT position(E'\\000om'::bytea in E'Th\\000omas'::bytea) AS RESULT; result -------- 3 (1 row) |
Description: Truncates substring.
Return type: bytea
For example:
1 2 3 4 5 | SELECT substring(E'Th\\000omas'::bytea from 2 for 3) AS RESULT; result ---------- \x68006f (1 row) |
Truncate the time and obtain the number of hours.
1 2 3 4 5 | select substring('2022-07-18 24:38:15',12,2)AS RESULT; result ----------- 24 (1 row) |
Description: Removes the longest string containing only bytes from bytes from the start and end of string.
Return type: bytea
For example:
1 2 3 4 5 | SELECT trim(E'\\000'::bytea from E'\\000Tom\\000'::bytea) AS RESULT; result ---------- \x546f6d (1 row) |
GaussDB(DWS) also provides the common syntax used for invoking functions.
Description: Removes the longest string containing only bytes from bytes from the start and end of string.
Return type: bytea
For example:
1 2 3 4 5 | SELECT btrim(E'\\000trim\\000'::bytea, E'\\000'::bytea) AS RESULT; result ------------ \x7472696d (1 row) |
Description: Extracts bit from string.
Return type: int
For example:
1 2 3 4 5 | SELECT get_bit(E'Th\\000omas'::bytea, 45) AS RESULT; result -------- 1 (1 row) |
Description: Extracts byte from string.
Return type: int
For example:
1 2 3 4 5 | SELECT get_byte(E'Th\\000omas'::bytea, 4) AS RESULT; result -------- 109 (1 row) |
Description: Sets bit in string.
Return type: bytea
For example:
1 2 3 4 5 | SELECT set_bit(E'Th\\000omas'::bytea, 45, 0) AS RESULT; result ------------------ \x5468006f6d4173 (1 row) |
Description: Sets byte in string.
Return type: bytea
For example:
1 2 3 4 5 | SELECT set_byte(E'Th\\000omas'::bytea, 4, 64) AS RESULT; result ------------------ \x5468006f406173 (1 row) |