Character / Binary Functions
All string functions support Unicode characters. Functions that transform character case are locale sensitive.
These functions support the UTF-8 format:
- RTRIM
- LTRIM
- LEFT
- RIGHT
- TRIM
- TRANSLATE
- RPAD
- LPAD
- SUBSTRING
Returns the ASCII code value of the leftmost character of the character value.
The ASCII function only supports ASCII characters in the input argument.
Syntax
Example
Output: 97
Example
Output: 98
Returns the length of the character value in bits.
Syntax
Example
Output: 8
Example
Output: 16
Example
Output: 24
Alias for TRIM.
Converts an integer value to a string.
The value is first sign-extended to 8 bytes. Then, if it can be represented as 1 byte, a string is returned with that one byte.
Otherwise, a string is returned of length 2 bytes, 4 bytes, or 8 bytes with the bytes set to the big-endian representation of the integer, depending on how many high order zero bytes there are in the integer.
Syntax
Example
Output: a
Alias for LENGTH.
Alias for LENGTH.
Concatenates two values, which must both be either binary, hash, or string data types.
For strings, as long as one argument is a character value, the other argument is implicitly cast to a character value.
This function is equivalent to the || operator.
Syntax
Parameter | Data Type | Description |
---|---|---|
value1 | BINARY, HASH or CHAR | A value to concatenate with value2. If one argument is a character value, the other is also cast to a string. |
value2 | BINARY, HASH or CHAR | A value to concatenate with value1. If one argument is a character value, the other is also cast to a string. |
Example
Output: ocient data warehouse
Example
Output: ocient data warehouse
Returns true if x ends with y and false otherwise.
Syntax
Parameter | Data Type | Description |
---|---|---|
char1 | CHAR | A string to evaluate for whether it ends with char2. |
char2 | CHAR | A string to evaluate for whether char1 ends with it as a substring. |
Example
Output: true
Example
Output: false
Example
Output: true
For each word in the provided string, capitalize the first character if it is alphabetic. Words are defined as alphanumeric strings separated by non-alphanumeric characters. All other alphabetic characters are made lowercase.
Syntax
Example
Output: Ocient
Returns the index position of the first occurrence where the character value char_substring appears in the character value char by ignoring case.
Syntax
Parameter | Data Type | Description |
---|---|---|
char | CHAR | A string to evaluate for the first index position where char_substring appears. |
char_substring | CHAR | A string to evaluate for where it first appears in char. |
Example
Output: 1
Output: 8
Alias for LOWER.
Return the number of characters in the string equal of the value integer. If integer is negative, the function returns all but the last integer characters.
Syntax
Parameter | Data Type | Description |
---|---|---|
char | CHAR | A string to be modified by returning the number of characters equal to integer. |
integer | INT | The number of characters to return from the char string. If integer is negative, the function returns all but the last integer characters. |
Example
Output: ocient d
Example
Output: ocient data wareho
Alias for CHAR_LENGTH and CHARACTER_LENGTH.
Returns the length of the value. For character data types, this value is in terms of characters. For binary data types, this value is in terms of bytes.
Syntax
Example
Output: 21
Alias for POSITION.
Returns the index position of the first occurrence of the character value substring in character value string.
Optionally, you can also include an additional value offset to offset the LOCATE function by the specified number of spaces.
Syntax
Parameter | Data Type | Description |
---|---|---|
string | CHAR | A string to be evaluated for the first position of substring. |
substring | CHAR | A substring to be found for its first position in string. |
offset | INT | Optional. A number of index positions in string to offset the search for the substring value. This index position must be a positive integer, and it starts from the left of string. |
Example
Output: 13
Example
Output: 4
Example
In this example, the index starts at position 5. This means the function skips the initial "e" in the string, and instead it returns the second e at index position 15.
Output: 15
Alias for LCASE.
Convert string to lowercase.
Syntax
Example
Output: ocient
Example
Output: ocient
Pad the input text to the specified length with the pad string on the left side. If text is longer than length, it is truncated to length characters. If parameter pad is not provided, the space character is used.
Syntax
Parameter | Data Type | Description |
---|---|---|
string | CHAR | A string to be padded on its left side with additional characters, so that it equals the length value. |
length | INT | The number of characters for string to total. If the string is already longer than length, the function truncates string to equal the length value. |
pad_character | CHAR | Optional. A string of one or more characters to use to pad string in order to equal the length value. If not provided, pad_character uses a whitespace character to pad. |
Example
Output: ababababaocient data warehouse
Example
Output: ocient
Removes leading blanks from the string value string.
Alternatively, you can specify a second string value trim_character. If trim_character value is provided, the LTRIM function removes all leading instances of the trim_character value from the string.
Syntax
Parameter | Data Type | Description |
---|---|---|
string | CHAR | A string to be trimmed on its left side. |
trim_character | CHAR | Optional. A string of one or more characters that all are to be trimmed from the left side of string. Each character is trimmed individually, not as a cohesive substring. If unspecified, this value defaults to removing all whitespace from the left side. |
Example
Output: ocient
Example
Output: ocient
Example
In this example, all characters "abeo " are removed from the left side of the string.
Output: cient
Returns the hexadecimal string (all lowercase) representing the md5 hash of char.
Syntax
Example
Output: 438f03cf6e9ddf8793e02db25f2d2f88
Alias for SUBSTRING.
Returns the length in bytes of a character or binary value.
Syntax
Example
Output: 1
Example
Output: 6
Alias for LOCATE.
Repeats the character value char a number of times equal to num.
Syntax
Example
Output: aaaaa
Replaces all occurrences of substr_to_remove in the character value string with substr_to_replace.
Syntax
Parameter | Data Type | Description |
---|---|---|
string | CHAR | A string to be altered by replacing all instances of substr_to_remove with substr_to_replace. |
substr_to_remove | CHAR | A substring to remove from string, replacing all instances with the substr_to_replace value. |
substr_to_replace | CHAR | A substring to replace all instances of substr_to_remove. |
Example
Output: $c$c$c$c
Reverse the input string.
Syntax
Example
Output: dcba
Return the number of trailing characters in the string equal to the value integer.
Syntax
Parameter | Data Type | Description |
---|---|---|
char | CHAR | A string to be evaluated to return the number of trailing characters equal to integer. |
integer | INT | The number of characters of return from the end of the char string. If integer is negative, the function returns all but the first integer characters. |
Example
Output: arehouse
Example
Output: ent data warehouse
Pad the input text to the specified length with the pad string on the right side. If text is longer than length, it is truncated to length characters. If parameter pad is not provided, the space character is used.
Syntax
Parameter | Data Type | Description |
---|---|---|
string | CHAR | A string to be padded on its right side with additional characters, so that it equals the length value. |
length | INT | The number of characters for string to total. If the string is already longer than length, the function truncates string to equal the length value. |
pad_character | CHAR | Optional. A string of one or more characters to use to pad string in order to equal the length value. If not provided, pad_character uses a whitespace character to pad. |
Example
Output: ocient data warehouse
Example
Output: ocient data warehouseababababa
Returns the substring from the right side of a string, based on a specified length.
Syntax
Parameter | Data Type | Description |
---|---|---|
string | CHAR | A string to be evaluated to return a substring, based on the integer value. |
integer | INT | The number of characters to return from the right side of the string value. This value must be positive. |
Example
Output: data warehouse
Removes leading blanks from the string value string.
Alternatively, you can specify a second string value trim_character. If trim_character value is provided, the LTRIM function removes all leading instances of the trim_character value from the string.
Syntax
Parameter | Data Type | Description |
---|---|---|
string | CHAR | A string to be trimmed on its right side. |
trim_character | CHAR | Optional. A string of one or more characters that all are to be trimmed from the right side of string. Each character is trimmed individually, not as a cohesive substring. If unspecified, this value defaults to removing all whitespace from the right side. |
Example
Output: ocient
Example
Output: ocient
Returns a string of repeated spaces equal to the number value, repeat. You can join this to another string by using the CONCAT function.
Syntax
Example
Output: end
Split the value string based on the delimiter value. The function returns a substring from the split operation based on the index value (starting from 1).
Syntax
Parameter | Data Type | Description |
---|---|---|
string | CHAR | A string to be split based on the delimiter value. |
delimiter | CHAR | The character to use as a delimiter in string. |
index | INT | The index of the substring to return. The first index position starts at 1. |
Example
Output: id
Example
Output: name
Example
Output: address
Returns true if string starts with substring and false otherwise.
Syntax
Parameter | Data Type | Description |
---|---|---|
string | CHAR | A string to search. |
substring | CHAR | A substring to find at the beginning of string. |
Example
Output: true
Example
Output: false
Equivalent to using LOCATE as LOCATE(substring, string). Note the reversed argument order.
Syntax
Parameter | Data Type | Description |
---|---|---|
string | CHAR | A string to be evaluated for the first position of substring. |
substring | CHAR | A substring to be found for its first position in string. |
Example
Output: 17
Alias for SUBSTRING.
Alias for SUBSTR and MID.
Returns the substring of a character or binary value that starts with the position specified by the second argument and that ends with the position specified by one less than the sum of the second and third arguments. When the sum of the second and third arguments is less than two, the function returns the empty string.
Syntax
Parameter | Data Type | Description |
---|---|---|
string | CHAR | A string to be truncated based on the start_position and end_position values. |
start_position | INT | The starting position to return a substring. The first index position starts at 1. |
end_position | INT | Optional. The number of character spaces from the start_position to include in the returned substring. If unspecified, SUBSTRING returns all characters after the start_position. |
Example
Output: data warehouse
Example
Output: data
Converts a numeric, date, or timestamp value into a CHAR date type.
Parameter | Data Type | Description |
---|---|---|
value | DATE, TIMESTAMP, or all numeric types | A numeric, date, or timestamp value to be converted into a CHAR date type. |
format | CHAR | The format used for the CHAR conversion. For information on data type formats, see the Formatting Functions page. |
Example
Output: 2020-06-10
Example
Output: 13:25:14
Replaces specified characters in a provided string with a separate set of characters.
Characters specified in the char_to_remove set are replaced with characters in the char_to_replace set based on the corresponding index position.
Syntax
Parameter | Data Type | Description |
---|---|---|
string | CHAR | A string to have specific characters transposed, based on the char_to_remove and char_to_replace values. |
char_to_remove | CHAR | The characters to be removed from string. Characters specified in the char_to_remove set are replaced with characters in the char_to_replace set based on the corresponding index position. If char_to_remove is longer than char_to_replace, occurrences of the extra characters are removed. |
char_to_replace | CHAR | The characters to replace the removed characters in char_to_remove. |
Example
Output: gbhdif
Alias for BTRIM.
Trim leading and trailing blanks from the string.
Alternatively, you can specify a second string value trim_char. If a trim_char value is provided, the TRIM function removes all leading and trailing instances of the trim_char value from the string.
Syntax
Parameter | Data Type | Description |
---|---|---|
string | CHAR | A string to be trimmed of leading and trailing whitespace, or any other characters specified by the trim_char argument. |
trim_char | CHAR | Optional. If specified, this removes an alternative substring from the start or end of the string value. |
Example
Output: trimmed string
Example
Output: trimmed string
Alias for UPPER.
Alias for UCASE.
Convert string to upper case.
Syntax
Example
Output: OCIENT