Binary String Functions and Operators
SQL defines some binary string functions that use keywords rather than commas to separate arguments. Additionally, DataArts Fabric SQL provides commonly used syntax for invoking these functions.
octet_length(string)
Description: The number of bytes in a binary string.
Return type: integer.
Example:
1 2 3 4 5 |
postgres=#SELECT octet_length(E'jo\\000se'::bytea) AS RESULT; result -------- 5 (1 row) |
overlay(string placing string from int [for int])
Description: Replaces a substring.
Return type: bytea
Example:
1 2 3 4 5 |
postgres=#SELECT overlay(E'Th\\000omas'::bytea placing E'\\002\\003'::bytea from 2 for 3) AS RESULT; result ---------------- \x5402036d6173 (1 row) |
position(substring in string)
Description: The position of a specific substring.
Return type: integer.
Example:
1 2 3 4 5 |
postgres=#SELECT position(E'\\000om'::bytea in E'Th\\000omas'::bytea) AS RESULT; result -------- 3 (1 row) |
substring(string [from int] [for int])
Description: Extracts a substring.
Return type: bytea
Example:
1 2 3 4 5 |
postgres=#SELECT substring(E'Th\\000omas'::bytea from 2 for 3) AS RESULT; result ---------- \x68006f (1 row) |
Extract time and get the hour value:
1 2 3 4 5 |
postgres=#SELECT substring('2022-07-18 24:38:15',12,2)AS RESULT; result ----------- 24 (1 row) |
trim([both] bytes from string)
Description: Remove the longest string consisting only of bytes from the start and end of the string.
Return type: bytea
Example:
1 2 3 4 5 |
postgres=#SELECT trim(E'\\000'::bytea from E'\\000Tom\\000'::bytea) AS RESULT; result ---------- \x546f6d (1 row) |
substring_index(string, delim, count)
Description: Performs a case-sensitive search for the delimiter and returns the substring preceding the count-th occurrence of the delimiter in the string. If count is negative, search backward from the end. If the parameter contains NULL, NULL is returned. This function is only supported by clusters of version 8.2.0 or later.
Return type: text.
Example: In the string www.wWw.cloud.wWw.com, find the delimiter .wWw. that appears the second time in a case-sensitive manner. Return the substring before it: www.wWw.cloud.
1 2 3 4 5 |
postgres=#SELECT SUBSTRING_INDEX('www.wWw.cloud.wWw.com', '.wWw.', 2) AS RESULT; result --------------- www.wWw.cloud (1 row) |
btrim(string bytea,bytes bytea)
Description: Removes the longest string consisting only of bytes from the start and end of the string.
Return type: bytea
Example:
1 2 3 4 5 |
postgres=#SELECT btrim(E'\\000trim\\000'::bytea, E'\\000'::bytea) AS RESULT; result ------------ \x7472696d (1 row) |
get_bit(string, offset)
Description: Extracts bits from a string.
Return type: integer.
Example:
1 2 3 4 5 |
postgres=#SELECT get_bit(E'Th\\000omas'::bytea, 45) AS RESULT; result -------- 1 (1 row) |
get_byte(string, offset)
Description: Extracts bytes from a string.
Return type: integer.
Example:
1 2 3 4 5 |
postgres=#SELECT get_byte(E'Th\\000omas'::bytea, 4) AS RESULT; result -------- 109 (1 row) |
set_bit(string,offset, newvalue)
Description: Sets bits in a string.
Return type: bytea
Example:
1 2 3 4 5 |
postgres=#SELECT set_bit(E'Th\\000omas'::bytea, 45, 0) AS RESULT; result ------------------ \x5468006f6d4173 (1 row) |
set_byte(string,offset, newvalue)
Description: Sets bytes in a string.
Return type: bytea
Example:
1 2 3 4 5 |
postgres=#SELECT set_byte(E'Th\\000omas'::bytea, 4, 64) AS RESULT; result ------------------ \x5468006f406173 (1 row) |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot