Character and Binary Functions - Ocient Documentation

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

Index position values for character and string functions begin at position 1. For example, the "H" in the string "Hello" is at position 1.

ASCII

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

SQL

ASCII(char)

Example

SQL

SELECT ASCII('a');

*Output: *97 Example

SQL

SELECT ASCII('bc');

*Output: *98

BIT_LENGTH

Returns the length of the character value in bits. Syntax

SQL

BIT_LENGTH(char)

Example

SQL

SELECT BIT_LENGTH('a');

*Output: *8 Example

SQL

SELECT BIT_LENGTH('ab');

*Output: *16 Example

SQL

SELECT BIT_LENGTH('ab4');

*Output: *24

BTRIM

Alias for TRIM.

CHR

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

SQL

CHR(integer)

Example

SQL

SELECT CHR(97);

*Output: *a

CHAR_LENGTH

Alias for LENGTH.

CHARACTER_LENGTH

Alias for LENGTH.

CONCAT

Concatenates two or more values, all of which must be binary, hash, or string data types. For strings, as long as one argument is a character value, the other arguments are implicitly cast to a character value. Syntax

SQL

CONCAT(value1, value2 [, ...])

Arguments	Data Type	Description
`value1, value2 [, ...]`	`BINARY`, `HASH`, or `CHAR`	Two or more values to concatenate. If any argument is a character value, the others are also cast to a character value.

Example

SQL

SELECT CONCAT('ocient',' ','data', ' ', 'warehouse');

*Output: *ocient data warehouse Example

SQL

SELECT 'ocient'||' data warehouse';

*Output: *ocient data warehouse

ENDSWITH

Returns true if x ends with y and false otherwise. Syntax

SQL

ENDSWITH(char1, char2)

Argument	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

SQL

SELECT ENDSWITH('ocient data warehouse','warehouse');

*Output: *true Example

SQL

SELECT ENDSWITH('ocient data warehouse','db');

*Output: *false Example

SQL

SELECT ENDSWITH('tamaño','o');

*Output: *true

INITCAP

For each word in the specified string, capitalize the first character if it is alphabetic. The system defines words as alphanumeric strings separated by non-alphanumeric characters. The system converts all other alphabetic characters to lowercase. Syntaxes

SQL

INITCAP(char_string)
INITCAP(char_string, delimiter)

Argument	Data Type	Description
`char_string`	`CHAR`	A string to evaluate for initial capitalization.
`delimiter`	`CHAR`	Optional. One or more non-alphanumeric characters that specify the delimiter between words. The characters include all the ASCII printable non-alphanumeric characters except backquotes ` and equals `=`. Non-ASCII characters are not included. ℹ️If you specify alphabetic characters for the delimiter, the function converts them to lowercase. Example `sql SQL<br />SELECT INITCAP('ocient');<br />` Output: `Ocient`

INSTR

Returns the index position of the first occurrence where the character value char_substring appears in the character value char by ignoring the case. Syntax

SQL

INSTR(char, char_substring)

Argument	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

SQL

SELECT INSTR('ocient dw dw', 'ocient');

*Output: *1

SQL

SELECT INSTR('ocient dw dw', 'dw');

*Output: *8

JSON_EXTRACT_PATH_TEXT

Returns the value for the key-value pair referenced by a series of path elements in a JSON string. Syntax

SQL

JSON_EXTRACT_PATH_TEXT (
    json_string, path [, path2 [, ...] ] [, null_if_invalid ] )

Argument	Data Type	Description
`json_string`	`CHAR`	A JSON string.
`path [, path2 [, ...] ]`	`CHAR`	One or more path elements in a JSON string. You must include at least one path element up to a maximum of five, meaning the function can extract from paths nested up to five levels deep in the JSON string. Path elements are case-sensitive. If a specified path element does not exist in the JSON string, the function returns `NULL`.
`null_if_invalid`	`BOOLEAN`	Optional. If you set this value to `TRUE`, the function returns `NULL` if the JSON string is invalid. If you set this value to `FALSE`, the function returns an error when the JSON string is invalid. The default value is `FALSE`.

Examples Extract Values from Nested Paths This example extracts the value based on two specified paths in the JSON string, n4 and n6.

SQL

SELECT JSON_EXTRACT_PATH_TEXT(
    '{"n2":{"n3":1},"n4":{"n5":99,"n6":"circle"}}',
    'n4', 'n6'
    );

*Output: *"circle" Return NULL from Invalid JSON In this example, the query requests the same nested paths, but the JSON is invalid because all of the keys lack quotation marks. The query returns NULL because the null_if_invalid argument equals TRUE.

SQL

SELECT JSON_EXTRACT_PATH_TEXT(
    '{n2:{n3:1},n4:{n5:99,n6:"circle"}}',
    'n4', 'n6',
    TRUE
    );

*Output: *NULL

LCASE

Alias for LOWER.

LEFT

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

SQL

LEFT(char, integer)

Argument	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

SQL

SELECT LEFT('ocient data warehouse', 8);

*Output: *ocient d Example

SQL

SELECT LEFT('ocient data warehouse', -3);

*Output: *ocient data wareho

LENGTH

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

SQL

LENGTH(character_or_binary_value)

Example

SQL

SELECT LENGTH('ocient data warehouse');

*Output: *21

LOCATE

Alias for POSITION. Returns the index position of the first occurrence of the character value substring in the character value string. Optionally, you can also include an additional value offset to offset the LOCATE function by the specified number of spaces. Index positions begin at 1. Syntax

SQL

LOCATE(substring, string [, offset] )

Argument	Data Type	Description
`substring`	`CHAR`	A substring to be found for its first position in string.
`string`	`CHAR`	A string to be evaluated for the first position of `substring`.
`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

SQL

SELECT LOCATE('ware', 'ocient data warehouse');

*Output: *13 Example

SQL

SELECT LOCATE('e', 'ocient');

*Output: *4 Example In this example, the index starts at position 5. This means the function skips the initial 'e' in the string. Instead, it returns the second e at index position 15.

SQL

SELECT LOCATE('e', 'ocient database', 5);

*Output: *15

LOWER

Alias for LCASE. Convert string to lowercase. Syntax

SQL

LOWER(char)

Example

SQL

SELECT LOWER('Ocient');

*Output: *ocient Example

SQL

SELECT LOWER('OCIENT');

*Output: *ocient

LPAD

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 the argument pad is not provided, the space character is used. Syntax

SQL

LPAD(string, length [, pad_character] )

Argument	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` to equal the `length` value. If not provided, `pad_character` uses a whitespace character to pad.

Example

SQL

SELECT LPAD('ocient data warehouse',30,'ab');

*Output: *ababababaocient data warehouse Example

SQL

SELECT LPAD('ocient data warehouse',6);

*Output: *ocient

LTRIM

Removes leading blanks from the string value string. Alternatively, you can specify a second string value trim_character. If you specify the trim_character value, the LTRIM function removes all leading instances of the trim_character value from the string. Syntax

SQL

LTRIM(string [, trim_character] )

Argument	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 to be trimmed from the left side of `string`. Each character is trimmed individually, not as a cohesive substring. If you do not specify this argument, this value defaults to removing all whitespace from the left side of `string`.

Example

SQL

SELECT LTRIM(' ocient');

*Output: *ocient Example

SQL

SELECT LTRIM('aaaaaocient','a');

*Output: *ocient Example In this example, all characters 'abeo ' are removed from the left side of the string.

SQL

SELECT LTRIM('aaeabe ocient','abeo ');

*Output: *cient

MD5

Returns the hexadecimal string (all lowercase) representing the md5 hash of char. Syntax

SQL

MD5(char)

Example

SQL

SELECT md5('ocient');

*Output: *438f03cf6e9ddf8793e02db25f2d2f88

MID

Alias for SUBSTRING.

OCTET_LENGTH

Returns the length in bytes of a character or binary value. Syntax

SQL

OCTET_LENGTH(value)

Example

SQL

SELECT OCTET_LENGTH('a');

*Output: *1 Example

SQL

SELECT OCTET_LENGTH('ocient');

*Output: *6

POSITION

Alias for LOCATE.

REGEXP_COUNT

Searches a string for all occurrences of a regular expression pattern. The function returns an integer representing the number of times the regular expression pattern occurs in the string. Syntax

SQL

REGEXP_COUNT( string, pattern [, position [, parameters ] ] )

Argument	Data Type	Description
`string`	`CHAR`	The string to search, specified as a character string.
`pattern`	`CHAR`	The regular expression pattern to use in the search, specified as a character string.
`position`	`INT`	Optional. The position, specified as a positive integer, that represents the position within the `string` string where to begin the search, based on the number of characters. If this integer exceeds the number of characters in `string`, then the function returns `0`. The default value is `1`.
`parameters`	`CHAR`	Optional. Parameters, specified as a character string, that contains one or more characters representing regular expression options for pattern matching. Supported options are: `c` — Perform case-sensitive matching. The System enables this type of matching by default. `i` — Perform case-insensitive matching. `p` — Interpret the pattern using the Perl Compatible Regular Expression (PCRE) dialect. For details, see PCRE. The Ocient System enables this interpretation by default.

Example This example searches the 'ABABDaSGRESaB' string for the count of occurrences of the 'AB' string by ignoring the case sensitivity. The search starts at position 1.

SQL

SELECT REGEXP_COUNT('ABABDaSGRESaB', 'AB', 1, 'i');

*Output: *3

REGEXP_INSTR

Searches a string using a regular expression pattern and returns an integer representing the start position or end position of the substring that matches. The function returns 0 if no match is found. The REGEXP_INSTR function is similar to the POSITION function, but it allows greater precision with regular expressions. Syntax

SQL

REGEXP_INSTR( source_string, pattern [, position [, occurrence] [, option [, parameters ] ] ] ] )

Argument	Data Type	Description
`string`	`CHAR`	The string to search, specified as a character string. Note that SQL escape sequences for string literals can override regular expression escape sequences. For details, see String Literals and Escape Sequences.
`pattern`	`CHAR`	The regular expression pattern to use in the search, specified as a character string.
`position`	`INT`	Optional. A positive integer that represents the position within the `string` string where to begin the search, based on the number of characters. This value alters only the start position for the match, not the returned string position. If this integer exceeds the number of characters in `string`, then the function returns `0`. The default value is `1`.
`occurrence`	`INT`	Optional. A positive integer that represents the occurrence of a positive pattern match to return. The default value is `1`, which means the function returns the first substring that matches the regular expression pattern.
`option`	`INT`	Optional. An integer that specifies whether to return the position of the matching start position or the end position. Your choices are: * `0`: Returns the start position of the match. * `1`: Returns the end position of the match `+1`. The function treats any nonzero integer value as `1`. The default value is `0`.
`parameters`	`CHAR`	Optional. A string containing one or more characters that represents the regular expression options for pattern matching. Supported options are: `c` — Perform case-sensitive matching. The Ocient System enables this type of matching by default. `i` — Perform case-insensitive matching. `e` — Extract the substring using a regular expression subpattern. This subpattern is enclosed in parentheses in the regular expression. The function uses the full regular expression pattern for the match but returns only the first subpattern match. `p` — Interpret the pattern using the Perl Compatible Regular Expression (PCRE) dialect. For details, see PCRE. The Ocient System enables this interpretation by default.

Example The query searches the website URL for the substring preceded by the # character. The function includes optional arguments to start the search at position 9 and match the first occurrence. The last specified optional argument directs the function to return the ending position of the matching substring.

SQL

SELECT REGEXP_INSTR(
    'https://docs.ocient.com/character-binary-functions#J28jB',
    '#\w+',
    9,
    1,
    1
    );

*Output: *57

REGEXP_REPLACE

Searches a string for all occurrences of a regular expression pattern. The function replaces every match occurrence of the pattern with a new string. The REGEXP_REPLACE function is similar to the REPLACE and TRANSLATE functions, but it allows greater precision with regular expressions. Syntax

SQL

REGEXP_REPLACE( string, pattern [, replace_string [ , position [, parameters ] ] ] )

Argument	Data Type	Description
`string`	`CHAR`	The string to search, specified as a character string. Note that SQL escape sequences for string literals can override regular expression escape sequences. For details, see String Literals and Escape Sequences.
`pattern`	`CHAR`	The regular expression pattern to use in the search, specified as a character string.
`replace_string`	`CHAR`	Optional. A string to replace all occurrences of the regular expression pattern in `string`. This string can include references to capture groups in the regular expression `pattern`. To reference capture groups, use a `$` followed by the group number. For example, `$1` references the first capture group, and `$2` references the second. The default value is an empty string `''`.
`position`	`INT`	Optional. A positive integer that represents the position within the string to begin the search, based on the number of characters. If this integer exceeds the number of characters in `string`, then the function returns the original `string`. The default value is `1`.
`parameters`	`CHAR`	Optional. A string containing one or more characters representing regular expression options for pattern matching. Supported options are: `c` — Perform case-sensitive matching. The Ocient System enables this type of matching by default. `i` — Perform case-insensitive matching. `p` — Interpret the pattern using the Perl Compatible Regular Expression (PCRE) dialect. For details, see PCRE. The Ocient System enables this interpretation by default.

Examples Replace Text Using a Substring The query replaces the matching substring '#J28jB' with the characters '#0FqD_'. The search begins at position 9.

SQL

SELECT REGEXP_REPLACE(
    'https://docs.ocient.com/character-binary-functions#J28jB',
    '#\w+',
    '#0FqD_',
    9
    );

*Output: */character-and-binary-functions Replace Text Using Multiple Capture Groups This example uses three capture groups to take an unformatted phone number and convert it into the (XXX) XXX-XXXX format. The replace_string argument references each capture group as $1, $2, and $3.

SQL

SELECT REGEXP_REPLACE(
    '5558675309',
    '(\d{3})(\d{3})(\d{4})',
    '($1) $2-$3'
    );

*Output: *(555) 867-5309

REGEXP_SUBSTR

Returns one substring from a string that matches a specified regular expression pattern. REGEXP_SUBSTR is similar to the SUBSTR function, but it allows greater precision with regular expressions. If the pattern produces no matches, the function returns an empty string. Syntax

SQL

REGEXP_SUBSTR( string, pattern [, position [, occurrence [, parameters ] ] ] )

Argument	Data Type	Description
`string`	`CHAR`	The string to search, specified as a character string. Note that SQL escape sequences for string literals can override regular expression escape sequences. For details, see String Literals and Escape Sequences.
`pattern`	`CHAR`	The regular expression pattern to use in the search, specified as a character string.
`position`	`INT`	Optional. A positive integer that represents the position within the string to begin the search, based on the number of characters. If this integer is greater than the number of characters in `string`, then the function returns the original `string`. The default value is `1`.
`occurrence`	`INT`	Optional. A positive integer that represents the occurrence of a positive pattern match to return. If this value exceeds the number of matches, then the function returns NULL. The default value is `1`, which means the function returns the first substring that matches the regular expression pattern.
`parameters`	`CHAR`	Optional. A string containing one or more characters representing regular expression options for pattern matching. Supported options are: `c` — Perform case-sensitive matching. The Ocient System enables this type of matching by default. `i` — Perform case-insensitive matching. `e` — Extract the substring using a regular expression subpattern. This subpattern is enclosed in parentheses in the regular expression. The function uses the full regular expression pattern for the match, but it returns only the first subpattern match. If there is no subexpression in the `pattern` argument, then the `e` parameter is ignored. `p` — Interpret the pattern using the Perl Compatible Regular Expression (PCRE) dialect. For details, see PCRE. The Ocient System enables this interpretation by default.

Example The query returns the first occurrence of the #J28jB string by using the regular expression pattern '#\w+'. The search starts at position 9.

SQL

SELECT REGEXP_SUBSTR(
    'https://docs.ocient.com/character-binary-functions#J28jB',
    '#\w+',
    9,
    1
    );

*Output: *#J28jB

REPEAT

Repeats the char string num times without spaces. Syntax

SQL

REPEAT(char, num)

Example Repeat the a string five times.

SQL

SELECT REPEAT('a', 5);

*Output: *aaaaa

REPLACE

Replaces all occurrences of substr_to_remove in the character value string with substr_to_replace. Syntax

SQL

REPLACE(string, substr_to_remove, substr_to_replace)

Argument	Data Type	Description
`string`	`CHAR`	A string to alter 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. If `substr_to_remove` is the empty string, the system returns `string`.
`substr_to_replace`	`CHAR`	A substring to replace all instances of `substr_to_remove`.

Example

SQL

SELECT REPLACE('abcabcabcabc', 'ab', '$');

*Output: *$c$c$c$c

REVERSE

Reverse the input string. Syntax

SQL

REVERSE(char)

Example

SQL

SELECT REVERSE('abcd');

*Output: *dcba

RIGHT

Return the number of trailing characters in the string equal to the value integer. Syntax

SQL

RIGHT(char, integer)

Argument	Data Type	Description
`char`	`CHAR`	A string to evaluate 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

SQL

SELECT RIGHT('ocient data warehouse', 8);

*Output: *arehouse Example

SQL

SELECT RIGHT('ocient data warehouse', -3);

*Output: *ent data warehouse

RPAD

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 the argument pad is not provided, the space character is used. Syntax

SQL

RPAD(character_value_text, integral_value_length [, character_value_pad] )

Argument	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` to equal the `length` value. If not provided, `pad_character` uses a whitespace character to pad.

Example

SQL

SELECT RPAD('ocient data warehouse',30);

*Output: *ocient data warehouse Example

SQL

SELECT RPAD('ocient data warehouse',30,'ab');

*Output: *ocient data warehouseababababa

RSUBSTRING

Returns the substring from the right side of a string, based on a specified length. Syntax

SQL

RSUBSTRING(string, integer)

Argument	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

SQL

SELECT RSUBSTRING('ocient data warehouse',14);

*Output: *data warehouse

RTRIM

Removes trailing blanks from the string value string. Alternatively, you can specify a second string value trim_character. If you specify the trim_character value, the RTRIM function removes all trailing instances of the trim_character value from the string. Syntax

SQL

RTRIM(string [, trim_character] )

Argument	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 to be trimmed from the right side of `string`. Each character is trimmed individually, not as a cohesive substring. If you do not specify this argument, this value defaults to removing all whitespace from the right side of `string`.

Example

SQL

SELECT RTRIM('ocient ');

*Output: *ocient Example

SQL

SELECT RTRIM('ocientaaaaaa','a');

*Output: *ocient

SHA1

Uses the [SHA-1](https://en.wikipedia.org/wiki/SHA-1#:~:text=In%20cryptography%2C%20SHA%2D1%20(,rendered%20as%2040%20hexadecimal%20digits.) cryptographic hash function to convert a string into a 40-character string representing the hexadecimal value of a 160-bit checksum. Syntax

SQL

SHA1(string)

Argument	Data Type	Description
`string`	`CHAR`	A string to convert using the SHA-1 cryptographic hash function.

Example

SQL

SELECT SHA1('Ocient');

*Output: *0c2a9a042b9f047f875c3414e7a4f4c53efbe082

SPACE

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

SQL

SPACE(repeat)

Example

SQL

SELECT CONCAT(SPACE(10),'end');

*Output: * end

SPLIT_PART

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

SQL

SPLIT_PART(string, delimiter, index)

Argument	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

SQL

SELECT SPLIT_PART('id|name|address','|',1);

*Output: *id Example

SQL

SELECT SPLIT_PART('id|name|address','|',2);

*Output: *name Example

SQL

SELECT SPLIT_PART('id,name,address',',',3);

*Output: *address

SPLIT_TO_ARRAY

Splits a string into an array of substrings. Syntax

SQL

SPLIT_TO_ARRAY(string, [ delimiter ])

Argument	Data Type	Description
`string`	`CHAR`	A string to split into an array of substrings.
`delimiter`	`CHAR`	Optional. The delimiter that divides the string. The default value is a comma (`,`).

Example This example specifies | as the delimiter to split the input string.

SQL

SELECT SPLIT_TO_ARRAY('AB|CD|EF', '|');

*Output: *['AB','CD','EF']

STARTSWITH

Returns true if string starts with substring and false otherwise. Syntax

SQL

STARTSWITH(string, substring)

Argument	Data Type	Description
`string`	`CHAR`	A string to search.
`substring`	`CHAR`	A substring to find at the beginning of `string`.

Example

SQL

SELECT STARTSWITH('ocient','o');

*Output: *true Example

SQL

SELECT STARTSWITH('ocient','c');

*Output: *false

STRPOS

Equivalent to using LOCATE as LOCATE(substring, string). Note the reversed argument order. Syntax

SQL

STRPOS(string, substring)

Argument	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

SQL

SELECT STRPOS('ocient data warehouse','house');

*Output: *17

SUBSTR

Alias for SUBSTRING.

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

SQL

SUBSTRING(string, start_position [, length] )

Argument	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`.
`length`	`INT`	Optional. The number of characters from the `start_position` to include in the returned substring. If unspecified, SUBSTRING returns all characters after the `start_position`.

Example

SQL

SELECT SUBSTRING('ocient data warehouse',8);

*Output: *data warehouse Example

SQL

SELECT SUBSTRING('ocient data warehouse',8,4);

*Output: *data

TO_CHAR

Converts a numeric, date, or timestamp value into a CHAR date type.

SQL

TO_CHAR(value, format)

Argument	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

SQL

SELECT TO_CHAR(20200610132514/1000000,'9999-99-99');

*Output: *2020-06-10 Example

SQL

SELECT TO_CHAR(20200610132514%1000000,'99:99:99');

*Output: *13:25:14

TRANSLATE

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

SQL

TRANSLATE(string, char_to_remove, char_to_replace)

Argument	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

SQL

SELECT TRANSLATE('abcdef','ace','ghi');

*Output: *gbhdif

TRIM

Alias for BTRIM. Trim leading and trailing space characters 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

SQL

TRIM(string [, trim_char] )

Argument	Data Type	Description
`string`	`CHAR`	A string to be trimmed of leading and trailing space characters, 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

SQL

SELECT TRIM(' trimmed string ');

*Output: *trimmed string Example

SQL

SELECT TRIM('aaaaaaaatrimmed stringaaaaaaaaa', 'a');

*Output: *trimmed string

UCASE

Alias for UPPER.

UPPER

Alias for UCASE. Convert string to upper case. Syntax

SQL

UPPER(character_value)

Example

SQL

SELECT UPPER('ocient');

*Output: *OCIENT

Concatenate Operator(`||`)

The || operator concatenates two strings. If you specify a NULL string, the result is NULL. || Syntax

SQL

string1 || string2

Argument	Data Type	Description
`string1`	`CHAR`	A string to concatenate.
`string2`	`CHAR`	A string to concatenate with the first string.

Examples Concatenate Two Strings Concatenate two strings.

SQL

SELECT 'hello ' || 'world';

Output: 'hello world' Concatenate a NULL String Concatenate two strings, one of which is NULL.

SQL

SELECT NULL || 'world';

Output: NULL WHERE Formatting Functions

Documentation Index

​ASCII

​BIT_LENGTH

​BTRIM

​CHR

​CHAR_LENGTH

​CHARACTER_LENGTH

​CONCAT

​ENDSWITH

​INITCAP

​INSTR

​JSON_EXTRACT_PATH_TEXT

​LCASE

​LEFT

​LENGTH

​LOCATE

​LOWER

​LPAD

​LTRIM

​MD5

​MID

​OCTET_LENGTH

​POSITION

​REGEXP_COUNT

​REGEXP_INSTR

​REGEXP_REPLACE

​REGEXP_SUBSTR

​REPEAT

​REPLACE

​REVERSE

​RIGHT

​RPAD

​RSUBSTRING

​RTRIM

​SHA1

​SPACE

​SPLIT_PART

​SPLIT_TO_ARRAY

​STARTSWITH

​STRPOS

​SUBSTR

​SUBSTRING

​TO_CHAR

​TRANSLATE

​TRIM

​UCASE

​UPPER

​Concatenate Operator(||)

​Related Links

ASCII

BIT_LENGTH

BTRIM

CHR

CHAR_LENGTH

CHARACTER_LENGTH

CONCAT

ENDSWITH

INITCAP

INSTR

JSON_EXTRACT_PATH_TEXT

LCASE

LEFT

LENGTH

LOCATE

LOWER

LPAD

LTRIM

MD5

MID

OCTET_LENGTH

POSITION

REGEXP_COUNT

REGEXP_INSTR

REGEXP_REPLACE

REGEXP_SUBSTR

REPEAT

REPLACE

REVERSE

RIGHT

RPAD

RSUBSTRING

RTRIM

SHA1

SPACE

SPLIT_PART

SPLIT_TO_ARRAY

STARTSWITH

STRPOS

SUBSTR

SUBSTRING

TO_CHAR

TRANSLATE

TRIM

UCASE

UPPER

Concatenate Operator(`||`)

Related Links