---
title: Character and Binary Functions
slug: _6OvxjCbiBnZWNJr8LeWN
description: Utilize character and binary functions in Ocient for advanced text and binary data manipulation.
docTags: 
createdAt: Thu Jul 21 2022 17:05:13 GMT+0000 (Coordinated Universal Time)
updatedAt: Mon Mar 30 2026 18:52:32 GMT+0000 (Coordinated Universal Time)
---

:::hint{type="info"}
All string functions support Unicode characters. Functions that transform character case are locale-sensitive.
:::

:::hint{type="info"}
These functions support the UTF-8 format:

- RTRIM
- LTRIM
- LEFT
- RIGHT
- TRIM
- TRANSLATE
- RPAD
- LPAD
- SUBSTRING
:::

:::hint{type="info"}
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.

:::hint{type="info"}
The ASCII function only supports ASCII characters in the input argument.
:::

**Syntax**

```sql
ASCII(char)
```

**Example**

```sql
SELECT ASCII('a');
```

*Output:&#x20;*`97`

**Example**

```sql
SELECT ASCII('bc');
```

*Output:&#x20;*`98`

### BIT\_LENGTH

Returns the length of the character value in bits.

**Syntax**

```sql
BIT_LENGTH(char)
```

**Example**

```sql
SELECT BIT_LENGTH('a');
```

*Output:&#x20;*`8`

**Example**

```sql
SELECT BIT_LENGTH('ab');
```

*Output:&#x20;*`16`

**Example**

```sql
SELECT BIT_LENGTH('ab4');
```

*Output:&#x20;*`24`

### BTRIM

Alias for [TRIM](./#trim).

### CHR

Converts an integer value to a string.&#x20;

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.&#x20;

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:&#x20;*`a`

### CHAR\_LENGTH

Alias for [LENGTH](./#length). &#x20;

### 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.&#x20;

**Syntax**

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

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

**Example**

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

*Output:&#x20;*`ocient data warehouse`

**Example**

```sql
SELECT 'ocient'||' data warehouse';
```

*Output:&#x20;*`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:&#x20;*`true`

**Example**

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

*Output:&#x20;*`false`

**Example**

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

*Output:&#x20;*`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.<br />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.<br />ℹ️If you specify alphabetic characters for the delimiter, the function converts them to lowercase. |

**Example**

```sql
SELECT INITCAP('ocient');
```

*Output:&#x20;*`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:&#x20;*`1`

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

*Output:&#x20;*`8`

### JSON\_EXTRACT\_PATH\_TEXT

Returns the value for the key-value pair referenced by a series of path elements in a JSON string.&#x20;

**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. <br />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. <br />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. <br />If you set this value to `TRUE`, the function returns `NULL` if the JSON string is invalid. <br />If you set this value to `FALSE`, the function returns an error when the JSON string is invalid. <br />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`.&#x20;

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

*Output:&#x20;*`"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.&#x20;

The query returns `NULL` because the `null_if_invalid` argument equals `TRUE`.&#x20;

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

*Output:&#x20;*`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
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. <br /><br />If `integer` is negative, the function returns all but the last `integer` characters. |

**Example**

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

*Output:&#x20;*`ocient d`

**Example**

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

*Output:&#x20;*`ocient data wareho`

### LENGTH

Alias for CHAR\_LENGTH and CHARACTER\_LENGTH.&#x20;

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:&#x20;*`21`

### LOCATE

Alias for POSITION.

Returns the index position of the first occurrence of the character value `substring` in the character value `string`.&#x20;

Optionally, you can also include an additional value `offset` to offset the LOCATE function by the specified number of spaces.&#x20;

Index positions begin at `1`.&#x20;

**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. <br />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:&#x20;*`13`

**Example**

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

*Output:&#x20;*`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.&#x20;

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

*Output:&#x20;*`15`

### LOWER

Alias for LCASE.&#x20;

Convert string to lowercase.

**Syntax**

```sql
LOWER(char)
```

**Example**

```sql
SELECT LOWER('Ocient');
```

*Output:&#x20;*`ocient`

**Example**

```sql
SELECT LOWER('OCIENT');
```

*Output:&#x20;*`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.  <br />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. <br />If not provided, `pad_character` uses a whitespace character to pad.  |

**Example**

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

*Output:&#x20;*`ababababaocient data warehouse`

**Example**

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

*Output:&#x20;*`ocient`

### LTRIM

Removes leading blanks from the string value `string`.&#x20;

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`.&#x20;

**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.<br />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:&#x20;*`ocient`

**Example**

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

*Output:&#x20;*`ocient`

**Example**

In this example, all characters `'abeo '` are removed from the left side of the string.&#x20;

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

*Output:&#x20;*`cient`

### MD5

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

**Syntax**

```sql
MD5(char)
```

**Example**

```sql
SELECT md5('ocient');
```

*Output:&#x20;*`438f03cf6e9ddf8793e02db25f2d2f88`

### MID

Alias for [SUBSTRING](./#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:&#x20;*`1`

**Example**

```sql
SELECT OCTET_LENGTH('ocient');
```

*Output:&#x20;*`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.&#x20;

**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. <br />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. <br />If this integer exceeds the number of characters in `string`, then the function returns `0`.<br />The default value is `1`.                                                                                                                                                                                                                                                                                         |
| `parameters` | `CHAR`            | Optional. <br />Parameters, specified as a character string, that contains one or more characters representing regular expression options for pattern matching. Supported options are: <br />`c` — Perform case-sensitive matching. The Ocient® System enables this type of matching by default. <br />`i` — Perform case-insensitive matching. <br />`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
SELECT REGEXP_COUNT('ABABDaSGRESaB', 'AB', 1, 'i');
```

*Output:&#x20;*`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.&#x20;

The function returns `0` if no match is found.&#x20;

The `REGEXP_INSTR` function is similar to the [POSITION](./#position) function, but it allows greater precision with regular expressions.&#x20;

**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.<br />Note that SQL escape sequences for string literals can override regular expression escape sequences. For details, see [String Literals and Escape Sequences](<././Query Ocient/General SQL Syntax.md>).                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| `pattern`    | `CHAR`            | The regular expression pattern to use in the search, specified as a character string.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| `position`   | `INT`             | Optional. <br />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. <br />If this integer exceeds the number of characters in `string`, then the function returns `0`.<br />The default value is `1`.                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| `occurrence` | `INT`             | Optional. <br />A positive integer that represents the occurrence of a positive pattern match to return. <br />The default value is `1`, which means the function returns the first substring that matches the regular expression pattern.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
| `option`     | `INT`             | Optional. <br />An integer that specifies whether to return the position of the matching start position or the end position. Your choices are: <br />* `0`: Returns the start position of the match.&#x20;
* `1`: Returns the end position of the match `+1`.&#x20;<br />The function treats any nonzero integer value as `1`. <br />The default value is `0`.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| `parameters` | `CHAR`            | Optional. <br />A string containing one or more characters that represents the regular expression options for pattern matching. Supported options are: <br />`c` — Perform case-sensitive matching. The Ocient System enables this type of matching by default. <br />`i` — Perform case-insensitive matching. <br />`e` — Extract the substring using a regular expression subpattern. This subpattern is enclosed in parentheses in the regular expression. <br />The function uses the full regular expression pattern for the match but returns only the first subpattern match. <br />`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.&#x20;

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

*Output:&#x20;*`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.&#x20;

The `REGEXP_REPLACE` function is similar to the [REPLACE](./#replace) and [TRANSLATE](./#translate) functions, but it allows greater precision with regular expressions.&#x20;

**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.<br />Note that SQL escape sequences for string literals can override regular expression escape sequences. For details, see [String Literals and Escape Sequences](<././Query Ocient/General SQL Syntax.md>).                                                                                                                                                                                                                                                                                                    |
| `pattern`        | `CHAR`            | The regular expression pattern to use in the search, specified as a character string.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| `replace_string` | `CHAR`            | Optional. <br />A string to replace all occurrences of the regular expression pattern in `string`. <br />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. <br />The default value is an empty string `''`.                                                                                                                                                         |
| `position`       | `INT`             | Optional. <br />A positive integer that represents the position within the string to begin the search, based on the number of characters. <br />If this integer exceeds the number of characters in `string`, then the function returns the original `string`.<br />The default value is `1`.                                                                                                                                                                                                                                                                          |
| `parameters`     | `CHAR`            | Optional. <br />A string containing one or more characters representing regular expression options for pattern matching. Supported options are: <br />`c` —  Perform case-sensitive matching. The Ocient System enables this type of matching by default.<br />`i` —  Perform case-insensitive matching. <br />`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
SELECT REGEXP_REPLACE(
    'https://docs.ocient.com/character-binary-functions#J28jB', 
    '#\w+',
    '#0FqD_',
    9
    );
```

*Output:&#x20;*`https://docs.ocient.com/character-binary-functions#0FqD_`

**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:&#x20;*`(555) 867-5309`

### REGEXP\_SUBSTR

Returns one substring from a string that matches a specified regular expression pattern.&#x20;

`REGEXP_SUBSTR` is similar to the [SUBSTR](./#substr) function, but it allows greater precision with regular expressions.&#x20;

If the pattern produces no matches, the function returns an empty string.&#x20;

**Syntax**

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

| **Argument** | **Data** **Type** | **Description**                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| ------------ | ----------------- |   |
| `string`     | `CHAR`            | The string to search, specified as a character string.<br />Note that SQL escape sequences for string literals can override regular expression escape sequences. For details, see [String Literals and Escape Sequences](<././Query Ocient/General SQL Syntax.md>).                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| `pattern`    | `CHAR`            | The regular expression pattern to use in the search, specified as a character string.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| `position`   | `INT`             | Optional. <br />A positive integer that represents the position within the string to begin the search, based on the number of characters. <br />If this integer is greater than the number of characters in `string`, then the function returns the original `string`.<br />The default value is `1`.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| `occurrence` | `INT`             | Optional. <br />A positive integer that represents the occurrence of a positive pattern match to return. <br />If this value exceeds the number of matches, then the function returns NULL.  <br />The default value is `1`, which means the function returns the first substring that matches the regular expression pattern.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
| `parameters` | `CHAR`            | Optional. <br />A string containing one or more characters representing regular expression options for pattern matching. Supported options are: <br />`c` —  Perform case-sensitive matching. The Ocient System enables this type of matching by default.<br />`i` —  Perform case-insensitive matching. <br />`e` —  Extract the substring using a regular expression subpattern. This subpattern is enclosed in parentheses in the regular expression. <br />The function uses the full regular expression pattern for the match, but it returns only the first subpattern match. <br />If there is no subexpression in the `pattern` argument, then the `e` parameter is ignored. <br />`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
SELECT REGEXP_SUBSTR(
    'https://docs.ocient.com/character-binary-functions#J28jB', 
    '#\w+',
    9,
    1
    );
```

*Output:&#x20;*`#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:&#x20;*`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:&#x20;*`$c$c$c$c`

### REVERSE

Reverse the input string.

**Syntax**

```sql
REVERSE(char)
```

**Example**

```sql
SELECT REVERSE('abcd');
```

*Output:&#x20;*`dcba`

### RIGHT

Return the number of trailing characters in the string equal to the value `integer`.&#x20;

**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. <br />If `integer` is negative, the function returns all but the first `integer` characters. |

**Example**

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

*Output:&#x20;*`arehouse`

**Example**

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

*Output:&#x20;*`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.  <br />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. <br />If not provided, `pad_character` uses a whitespace character to pad.  |

**Example**

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

*Output:&#x20;*`ocient data warehouse         `

**Example**

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

*Output:&#x20;*`ocient data warehouseababababa`

### RSUBSTRING

Returns the substring from the right side of a string, based on a specified length.&#x20;

**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.  <br />This value must be positive.  |

**Example**

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

*Output:&#x20;*`data warehouse`

### RTRIM

Removes trailing blanks from the string value `string`.&#x20;

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`.&#x20;

**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.<br />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:&#x20;*`ocient`

**Example**

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

*Output:&#x20;*`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.&#x20;

**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:&#x20;*`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.&#x20;

**Syntax**

```sql
SPACE(repeat)
```

**Example**

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

*Output:&#x20;*`          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:&#x20;*`id`

**Example**

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

*Output:&#x20;*`name`

**Example**

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

*Output:&#x20;*`address`

### SPLIT\_TO\_ARRAY

Splits a string into an array of substrings.&#x20;

**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. <br />The delimiter that divides the string. <br />The default value is a comma (`,`).  |

**Example**

This example specifies `|` as the delimiter to split the input string.&#x20;

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

*Output:&#x20;*`['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:&#x20;*`true`

**Example**

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

*Output:&#x20;*`false`

### STRPOS

Equivalent to using [LOCATE](./#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:&#x20;*`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.&#x20;

**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. <br />If unspecified, SUBSTRING returns all characters after the `start_position`.  |

**Example**

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

*Output:&#x20;*`data warehouse`

**Example**

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

*Output:&#x20;*`data`

### TO\_CHAR

Converts a numeric, date, or timestamp value into a `CHAR` date type.&#x20;

```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. <br />For information on data type formats, see the [Formatting Functions](<./Formatting Functions.md>) page. |

**Example**

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

*Output:&#x20;*`2020-06-10`

**Example**

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

*Output:&#x20;*`13:25:14`&#x20;

### TRANSLATE

Replaces specified characters in a provided string with a separate set of characters.&#x20;

Characters specified in the `char_to_remove` set are replaced with characters in the `char_to_replace` set based on the corresponding index position.&#x20;

**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`.<br />Characters specified in the `char_to_remove` set are replaced with characters in the `char_to_replace` set based on the corresponding index position. <br />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:&#x20;*`gbhdif`

### TRIM

Alias for BTRIM.&#x20;

Trim leading and trailing space characters from the string.&#x20;

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:&#x20;*`trimmed string`

**Example**

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

*Output:&#x20;*`trimmed string`

### UCASE

Alias for [UPPER](./#upper). &#x20;

### UPPER

Alias for UCASE.&#x20;

Convert string to upper case.

**Syntax**

```sql
UPPER(character_value)
```

**Example**

```sql
SELECT UPPER('ocient');
```

*Output:&#x20;*`OCIENT`

### Concatenate Operator(`||`)

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

`||`**&#x20;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`

## Related Links

[WHERE](<././Query Ocient/General SQL Syntax.md>)&#x20;

[Formatting Functions](<./Formatting Functions.md>)
