Binary String Functions and Operators
String Operators
SQL defines some string functions that use keywords, rather than commas, to separate arguments.
- octet_length(string)
Description: Specifies the number of bytes in a binary string.
Return type: int
Example:
1 2 3 4 5
gaussdb=# SELECT octet_length(E'jo\\000se'::bytea) AS RESULT; result -------- 5 (1 row)
- overlay(string placing string from int [for int])
Description: Replaces substrings.
Return type: bytea
Example:
1 2 3 4 5
gaussdb=# 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: Specifies the position of a specified substring.
Return type: int
Example:
1 2 3 4 5
gaussdb=# SELECT position(E'\\000om'::bytea in E'Th\\000omas'::bytea) AS RESULT; result -------- 3 (1 row)
- substring(string [from int] [for int])
Description: Truncates a substring.
Return type: bytea
Example:
1 2 3 4 5
gaussdb=# SELECT substring(E'Th\\000omas'::bytea from 2 for 3) AS RESULT; result ---------- \x68006f (1 row)
- substr(bytea [from int] [for int])
Description: Truncates a substring.
Return type: bytea
Example:
1 2 3 4 5
gaussdb=# SELECT substr(E'Th\\000omas'::bytea,2, 3) as result; result ---------- \x68006f (1 row)
- trim([both] bytes from string)
Description: Removes the longest string containing only bytes from the start and end of string.
Return type: bytea
Example:
1 2 3 4 5
gaussdb=# SELECT trim(E'\\000'::bytea from E'\\000Tom\\000'::bytea) AS RESULT; result ---------- \x546f6d (1 row)
Other Binary String Functions
GaussDB provides common syntax used for calling functions.
- btrim(string bytea,bytes bytea)
Description: Removes the longest string containing only bytes from the start and end of string.
Return type: bytea
Example:
1 2 3 4 5
gaussdb=# 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: int
Example:
1 2 3 4 5
gaussdb=# 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: int
Example:
1 2 3 4 5
gaussdb=# 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
gaussdb=# 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
gaussdb=# SELECT set_byte(E'Th\\000omas'::bytea, 4, 64) AS RESULT; result ------------------ \x5468006f406173 (1 row)
- rawcmp(raw, raw)
Description: Specifies the raw data type comparison function.
Parameters: raw, raw
Return type: integer
- raweq(raw, raw)
Description: Specifies the raw data type comparison function.
Parameters: raw, raw
Return type: Boolean
- rawge(raw, raw)
Description: Specifies the raw data type comparison function.
Parameters: raw, raw
Return type: Boolean
- rawgt(raw, raw)
Description: Specifies the raw data type comparison function.
Parameters: raw, raw
Return type: Boolean
- rawin(cstring)
Description: Specifies the raw data type parsing function.
Parameter: cstring
Return type: bytea
- rawle(raw, raw)
Description: Specifies the raw data type parsing function.
Parameters: raw, raw
Return type: Boolean
- rawlike(raw, raw)
Description: Specifies the raw data type parsing function.
Parameters: raw, raw
Return type: Boolean
- rawlt(raw, raw)
Description: Specifies the raw data type parsing function.
Parameters: raw, raw
Return type: Boolean
- rawne(raw, raw)
Description: Compares whether the raw types are the same.
Parameters: raw, raw
Return type: Boolean
- rawnlike(raw, raw)
Description: Checks whether the raw type matches the pattern.
Parameters: raw, raw
Return type: Boolean
- rawout(bytea)
Description: Specifies the RAW output API.
Parameter: bytea
Return type: cstring
- rawsend(raw)
Description: Converts the bytea type to the binary type.
Parameter: raw
Return type: bytea
- rawtohex(text)
Description: Converts the raw format to the hexadecimal format.
Parameter: text
Return type: text
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
