> ## Documentation Index
> Fetch the complete documentation index at: https://docs.ocient.com/llms.txt
> Use this file to discover all available pages before exploring further.
# Character and Binary Functions
export const Ocient = "Ocient®";
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 SQL theme={null}
ASCII(char)
```
**Example**
```sql SQL theme={null}
SELECT ASCII('a');
```
\*Output: \*`97`
**Example**
```sql SQL theme={null}
SELECT ASCII('bc');
```
\*Output: \*`98`
### BIT\_LENGTH
Returns the length of the character value in bits.
**Syntax**
```sql SQL theme={null}
BIT_LENGTH(char)
```
**Example**
```sql SQL theme={null}
SELECT BIT_LENGTH('a');
```
\*Output: \*`8`
**Example**
```sql SQL theme={null}
SELECT BIT_LENGTH('ab');
```
\*Output: \*`16`
**Example**
```sql SQL theme={null}
SELECT BIT_LENGTH('ab4');
```
\*Output: \*`24`
### BTRIM
Alias for [TRIM](#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 SQL theme={null}
CHR(integer)
```
**Example**
```sql SQL theme={null}
SELECT CHR(97);
```
\*Output: \*`a`
### CHAR\_LENGTH
Alias for [LENGTH](#length).
### CHARACTER\_LENGTH
Alias for [LENGTH](#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 SQL theme={null}
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 SQL theme={null}
SELECT CONCAT('ocient',' ','data', ' ', 'warehouse');
```
\*Output: \*`ocient data warehouse`
**Example**
```sql SQL theme={null}
SELECT 'ocient'||' data warehouse';
```
\*Output: \*`ocient data warehouse`
### ENDSWITH
Returns `true` if `x` ends with `y` and `false` otherwise.
**Syntax**
```sql SQL theme={null}
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 SQL theme={null}
SELECT ENDSWITH('ocient data warehouse','warehouse');
```
\*Output: \*`true`
**Example**
```sql SQL theme={null}
SELECT ENDSWITH('ocient data warehouse','db');
```
\*Output: \*`false`
**Example**
```sql SQL theme={null}
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 SQL theme={null}
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
SELECT INITCAP('ocient');
`
\*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 SQL theme={null}
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 SQL theme={null}
SELECT INSTR('ocient dw dw', 'ocient');
```
\*Output: \*`1`
```sql SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
SELECT JSON_EXTRACT_PATH_TEXT(
'{n2:{n3:1},n4:{n5:99,n6:"circle"}}',
'n4', 'n6',
TRUE
);
```
\*Output: \*`NULL`
### LCASE
Alias for [LOWER](#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 SQL theme={null}
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 SQL theme={null}
SELECT LEFT('ocient data warehouse', 8);
```
\*Output: \*`ocient d`
**Example**
```sql SQL theme={null}
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 SQL theme={null}
LENGTH(character_or_binary_value)
```
**Example**
```sql SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
SELECT LOCATE('ware', 'ocient data warehouse');
```
\*Output: \*`13`
**Example**
```sql SQL theme={null}
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 SQL theme={null}
SELECT LOCATE('e', 'ocient database', 5);
```
\*Output: \*`15`
### LOWER
Alias for LCASE.
Convert string to lowercase.
**Syntax**
```sql SQL theme={null}
LOWER(char)
```
**Example**
```sql SQL theme={null}
SELECT LOWER('Ocient');
```
\*Output: \*`ocient`
**Example**
```sql SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
SELECT LPAD('ocient data warehouse',30,'ab');
```
\*Output: \*`ababababaocient data warehouse`
**Example**
```sql SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
SELECT LTRIM(' ocient');
```
\*Output: \*`ocient`
**Example**
```sql SQL theme={null}
SELECT LTRIM('aaaaaocient','a');
```
\*Output: \*`ocient`
**Example**
In this example, all characters `'abeo '` are removed from the left side of the string.
```sql SQL theme={null}
SELECT LTRIM('aaeabe ocient','abeo ');
```
\*Output: \*`cient`
### MD5
Returns the hexadecimal string (all lowercase) representing the md5 hash of `char`.
**Syntax**
```sql SQL theme={null}
MD5(char)
```
**Example**
```sql SQL theme={null}
SELECT md5('ocient');
```
\*Output: \*`438f03cf6e9ddf8793e02db25f2d2f88`
### MID
Alias for [SUBSTRING](#substring).
### OCTET\_LENGTH
Returns the length in bytes of a character or binary value.
**Syntax**
```sql SQL theme={null}
OCTET_LENGTH(value)
```
**Example**
```sql SQL theme={null}
SELECT OCTET_LENGTH('a');
```
\*Output: \*`1`
**Example**
```sql SQL theme={null}
SELECT OCTET_LENGTH('ocient');
```
\*Output: \*`6`
### POSITION
Alias for [LOCATE](#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 SQL theme={null}
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 {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](https://www.boost.org/doc/libs/1_33_1/libs/regex/doc/syntax_perl.html#Perl). 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 SQL theme={null}
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](#position) function, but it allows greater precision with regular expressions.
**Syntax**
```sql SQL theme={null}
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](/general-sql-syntax#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](https://www.boost.org/doc/libs/1_33_1/libs/regex/doc/syntax_perl.html#Perl). 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 SQL theme={null}
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](#replace) and [TRANSLATE](#translate) functions, but it allows greater precision with regular expressions.
**Syntax**
```sql SQL theme={null}
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](/general-sql-syntax). |
| `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](https://www.boost.org/doc/libs/1_33_1/libs/regex/doc/syntax_perl.html#Perl). 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 SQL theme={null}
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 SQL theme={null}
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](#substr) function, but it allows greater precision with regular expressions.
If the pattern produces no matches, the function returns an empty string.
**Syntax**
```sql SQL theme={null}
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](/general-sql-syntax). |
| `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](https://www.pcre.org/). 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 SQL theme={null}
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 SQL theme={null}
REPEAT(char, num)
```
**Example**
Repeat the `a` string five times.
```sql SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
SELECT REPLACE('abcabcabcabc', 'ab', '$');
```
\*Output: \*`$c$c$c$c`
### REVERSE
Reverse the input string.
**Syntax**
```sql SQL theme={null}
REVERSE(char)
```
**Example**
```sql SQL theme={null}
SELECT REVERSE('abcd');
```
\*Output: \*`dcba`
### RIGHT
Return the number of trailing characters in the string equal to the value `integer`.
**Syntax**
```sql SQL theme={null}
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 SQL theme={null}
SELECT RIGHT('ocient data warehouse', 8);
```
\*Output: \*`arehouse`
**Example**
```sql SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
SELECT RPAD('ocient data warehouse',30);
```
\*Output: \*`ocient data warehouse `
**Example**
```sql SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
SELECT RTRIM('ocient ');
```
\*Output: \*`ocient`
**Example**
```sql SQL theme={null}
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](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 SQL theme={null}
SHA1(string)
```
| **Argument** | **Data** **Type** | **Description** |
| ------------ | ----------------- | ---------------------------------------------------------------- |
| `string` | `CHAR` | A string to convert using the SHA-1 cryptographic hash function. |
**Example**
```sql SQL theme={null}
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](#concat) function.
**Syntax**
```sql SQL theme={null}
SPACE(repeat)
```
**Example**
```sql SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
SELECT SPLIT_PART('id|name|address','|',1);
```
\*Output: \*`id`
**Example**
```sql SQL theme={null}
SELECT SPLIT_PART('id|name|address','|',2);
```
\*Output: \*`name`
**Example**
```sql SQL theme={null}
SELECT SPLIT_PART('id,name,address',',',3);
```
\*Output: \*`address`
### SPLIT\_TO\_ARRAY
Splits a string into an array of substrings.
**Syntax**
```sql SQL theme={null}
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 SQL theme={null}
SELECT SPLIT_TO_ARRAY('AB|CD|EF', '|');
```
\*Output: \*`['AB','CD','EF']`
### STARTSWITH
Returns `true` if `string` starts with `substring` and `false` otherwise.
**Syntax**
```sql SQL theme={null}
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 SQL theme={null}
SELECT STARTSWITH('ocient','o');
```
\*Output: \*`true`
**Example**
```sql SQL theme={null}
SELECT STARTSWITH('ocient','c');
```
\*Output: \*`false`
### STRPOS
Equivalent to using [LOCATE](#locate) as `LOCATE(substring, string)`. Note the reversed argument order.
**Syntax**
```sql SQL theme={null}
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 SQL theme={null}
SELECT STRPOS('ocient data warehouse','house');
```
\*Output: \*`17`
### SUBSTR
Alias for [SUBSTRING](#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 SQL theme={null}
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 SQL theme={null}
SELECT SUBSTRING('ocient data warehouse',8);
```
\*Output: \*`data warehouse`
**Example**
```sql SQL theme={null}
SELECT SUBSTRING('ocient data warehouse',8,4);
```
\*Output: \*`data`
### TO\_CHAR
Converts a numeric, date, or timestamp value into a `CHAR` date type.
```sql SQL theme={null}
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](/formatting-functions) page. |
**Example**
```sql SQL theme={null}
SELECT TO_CHAR(20200610132514/1000000,'9999-99-99');
```
\*Output: \*`2020-06-10`
**Example**
```sql SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
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 SQL theme={null}
SELECT TRIM(' trimmed string ');
```
\*Output: \*`trimmed string`
**Example**
```sql SQL theme={null}
SELECT TRIM('aaaaaaaatrimmed stringaaaaaaaaa', 'a');
```
\*Output: \*`trimmed string`
### UCASE
Alias for [UPPER](#upper).
### UPPER
Alias for UCASE.
Convert string to upper case.
**Syntax**
```sql SQL theme={null}
UPPER(character_value)
```
**Example**
```sql SQL theme={null}
SELECT UPPER('ocient');
```
\*Output: \*`OCIENT`
### Concatenate Operator(`||`)
The `||` operator concatenates two strings. If you specify a NULL string, the result is NULL.
`||` **Syntax**
```sql SQL theme={null}
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 SQL theme={null}
SELECT 'hello ' || 'world';
```
Output: `'hello world'`
**Concatenate a NULL String**
Concatenate two strings, one of which is NULL.
```sql SQL theme={null}
SELECT NULL || 'world';
```
Output: `NULL`
## Related Links
[WHERE](/general-sql-syntax#where)
[Formatting Functions](/formatting-functions)