> ## 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.

# Scalar Data Conversion Functions

> Reference for Ocient SQL scalar conversion functions such as CAST, CONVERT, TO_CHAR, and TO_NUMBER to transform values between supported data types.

export const Python = "Python®";

export const Unix = "Unix®";

export const Ocient = "Ocient®";

These functions convert one data type to another.

## Scalar Data Conversion Functions

| **Function** | **Syntax** | **Purpose** |
| - | - | - |
| BYTE | BYTE(numeric value) | Casts the argument to a value of type byte |
| | BYTE(character value) | Parses the string as a byte value |
| SMALLINT | SMALLINT(numeric value) | Casts the argument to a value of type smallint |
| | SMALLINT(character value) | Parses the string as a `smallint` value |
| INTEGER | INT(numeric value) or INTEGER(numeric value) | Casts the argument to a value of type integer |
| | INT(character value) or INTEGER(character value) | Parses the string as an integer value |
| BIGINT | BIGINT(numeric value) | Casts the argument to a value of type bigint |
| | BIGINT(character value) | Parses the string as a `bigint` value |
| | BIGINT(time value) | Creates a `bigint` with the number of milliseconds represented by the time argument |
| | BIGINT(timestamp value) | Creates a `bigint` with the number of milliseconds after the {Unix} epoch corresponding to the timestamp argument |
| FLOAT | FLOAT(numeric value) | Casts the argument to a value of type float |
| | FLOAT(character value) | Parses the string as a float value |
| DOUBLE | DOUBLE(numeric value) | Casts the argument to a value of type double |
| | DOUBLE(character value) | Parses the string as a double value |
| DECIMAL | DECIMAL(numeric value) | Casts the argument to a value of type decimal |
| | DECIMAL(character value) | Parses the string and creates a decimal value |
| | DECIMAL(numeric value, integer value, integer value) | Casts the first argument to a decimal value with precision of the second argument and scale of the third argument |
| | DECIMAL(character value, integer value, integer value) | Parses the first argument to a decimal value with precision of the second argument and scale of the third argument |
| CHAR \| STR | CHAR(numeric value) | Creates a string version of the numeric value |
| | CHAR(character value) | None |
| | CHAR(timestamp) | Creates a string representation of the timestamp |
| | CHAR(date) | Creates a string representation of the date |
| | CHAR(time) | Creates a string representation of the time |
| | CHAR(boolean) | Creates a string representation of the Boolean value |
| | CHAR(binary) | Creates a hexadecimal string representation of the binary value |
| | CHAR(ip) | Creates a string representation of the IPV4 or IPV6 value |
| | CHAR(ipv4) | Creates a string representation of the IPV4 value |
| | CHAR(hash) | Creates a string representation of the fixed-binary hash value |
| | CHAR(point) | Creates a string representation of the point value |
| | CHAR(uuid) | Creates a string representation of the Universally Unique IDentifier (UUID) |
| BINARY | BINARY(character value) | Parses a hexadecimal string (such as `0x54ab`) to create a binary value. Letters can be in either case. |
| | BINARY(binary) | None |
| | BINARY(hash) | Converts the fixed-length binary value (hash) to a variable-length binary value |
| BOOLEAN | BOOLEAN(character value) | Parses a string to create a Boolean value. <br /><br />The function converts any of these string values to `TRUE`: `true`, `t`, `yes`, `y`, `on`, `1`.<br /><br />The function converts any of these string values to `FALSE`: `false`, `f`, `no`, `n`, `off`, `0`.<br /><br />The string parsing is case-insensitive. Any unsupported string values raise an error. |
| | BOOLEAN(numeric value) | Parses a numeric value to create a Boolean value. <br /><br />The function converts any non-zero value to `TRUE`. <br /><br />The function converts `0` to `FALSE`. |
| | BOOLEAN(boolean) | None |
| DATE | DATE(character value) | Parses a string in the form 'YYYY-MM-DD' to create a date. Extra characters are ignored. |
| | DATE(date) | None |
| | DATE(timestamp) | Truncates a timestamp to be a date value |
| HASH | HASH(char, length) | Creates a fixed-length binary value, with length \<length>, from a string, i.e., 0x1234abcd. Zero extended if the string does not have enough bytes, truncated if it has too many. |
| | HASH(binary, length) | Creates a fixed-length binary value, with length \<length>, from a variable-length binary value. Zero extended if the string does not have enough bytes, truncated if it has too many. |
| IP | IP(character value) | Creates a value of type IPV4 or IPV6 from a string |
| IPV4 | IPV4(character value) | Parses a string containing an IP address and creates a value of type IPV4 |
| | IPV4(ipv4) | no-op |
| TIME | TIME(character value) | Parses the string to create a time value. The string must be in the form `'HH:MM[.SSSSSSSSS]'` |
| | TIME(bigint) | Creates a time value representing a certain number of milliseconds after midnight |
| | TIME(time) | None |
| | TIME(timestamp) | Makes a time value from the time component of a timestamp |
| TIMESTAMP | TIMESTAMP(character value) | Parses a string and makes a timestamp value. The string must be in the format `'YYYY-MM-DD[ HH:MM][.SSSSSSSSS]'`. |
| | TIMESTAMP(date) | Makes a timestamp from the date by setting the time component to `00:00:00.0` |
| | TIMESTAMP(timestamp) | None |
| | TIMESTAMP(bigint value) | Creates a timestamp from a `bigint` representing the number of milliseconds after the Unix epoch (1/1/1970). |
| UUID | UUID(character value) | Parses the string and makes a UUID value. The string must be a valid UUID. |
| | UUID(uuid) | None |
| WEEKS | WEEKS(integral value) | Converts an integral value to an interval value of type weeks for date calculations. |
| | Weeks(weeks) | None |
| DAYS | DAYS(integral value) | Converts an integral value to an interval value of type days for date calculations. |
| | DAYS(days) | None |
| | DAYS(weeks) | Converts an interval value of type weeks to an interval value of type days representing the same time span. |
| MONTHS | MONTHS(integral value) | Converts an integral value to an interval value of type months for date calculations. |
| | MONTHS(months) | None |
| | MONTHS(years) | Converts an interval value of type years to an interval value of type months representing the same time span |
| YEARS | YEARS(integral value) | Converts an integral value to an interval value of type years |
| | YEARS(years) | None |
| HOURS | HOURS(integral value) | Converts an integral value to an interval value of type hours |
| | HOURS(hours) | None |
| | HOURS(days) | Converts an interval value of type days to an interval value of type hours representing the same time span. |
| | HOURS(weeks) | Converts an interval value of type weeks to an interval value of type hours representing the same time span. |
| MINUTES | MINUTES(integral value) | Converts an integral value to an interval value of type minutes |
| | MINUTES(minutes) | None |
| | MINUTES(hours) | Converts an interval value of type hours to an interval value of type minutes representing the same time span |
| | MINUTES(days) | Converts an interval value of type days to an interval value of type minutes representing the same time span |
| | MINUTES(weeks) | Converts an interval value of type weeks to an interval value of type minutes representing the same time span |
| SECONDS | SECONDS(integral value) | Converts an integral value to an interval value of type seconds |
| | SECONDS(seconds) | None |
| | SECONDS(minutes) | Converts an interval value of type minutes to an interval value of type seconds representing the same time span |
| | SECONDS(hours) | Converts an interval value of type hours to an interval value of type seconds representing the same time span |
| | SECONDS(days) | Converts an interval value of type days to an interval value of type seconds representing the same time span |
| | SECONDS(weeks) | Converts an interval value of type weeks to an interval value of type seconds representing the same time span |
| MILLISECONDS | MILLISECONDS(integral value) | Converts an integral value to an interval value of type milliseconds |
| | MILLISECONDS(milliseconds) | None |
| | MILLISECONDS(seconds) | Converts an interval value of type seconds to an interval value of type milliseconds, representing the same time span |
| | MILLISECONDS(minutes) | Converts an interval value of type minutes to an interval value of type milliseconds representing the same time span |
| | MILLISECONDS(hours) | Converts an interval value of type hours to an interval value of type milliseconds representing the same time span |
| | MILLISECONDS(days) | Converts an interval value of type days to an interval value of type milliseconds representing the same time span |
| | MILLISECONDS(weeks) | Converts an interval value of type weeks to an interval value of type milliseconds representing the same time span |
| MICROSECONDS | MICROSECONDS(integral value) | Converts an integral value to an interval value of type microseconds |
| | MICROSECONDS(milliseconds) | Converts an interval value of type milliseconds to an interval value of type microseconds representing the same time span |
| | MICROSECONDS(seconds) | Converts an interval value of type seconds to an interval value of type microseconds representing the same time span |
| | MICROSECONDS(minutes) | Converts an interval value of type minutes to an interval value of type microseconds representing the same time span |
| | MICROSECONDS(hours) | Converts an interval value of type hours to an interval value of type microseconds representing the same time span |
| | MICROSECONDS(days) | Converts an interval value of type days to an interval value of type microseconds representing the same time span |
| | MICROSECONDS(weeks) | Converts an interval value of type weeks to an interval value of type microseconds representing the same time span |
| | MICROSECONDS(microseconds) | None |
| NANOSECONDS | NANOSECONDS(integral value) | Converts an integral value to an interval value of type nanoseconds |
| | NANOSECONDS(microseconds) | Converts an interval value of type microseconds to an interval value of type nanoseconds representing the same time span |
| | NANOSECONDS(milliseconds) | Converts an interval value of type milliseconds to an interval value of type nanoseconds representing the same time span |
| | NANOSECONDS(seconds) | Converts an interval value of type seconds to an interval value of type nanoseconds representing the same time span |
| | NANOSECONDS(minutes) | Converts an interval value of type minutes to an interval value of type nanoseconds representing the same time span |
| | NANOSECONDS(hours) | Converts an interval value of type hours to an interval value of type nanoseconds representing the same time span |
| | NANOSECONDS(days) | Converts an interval value of type days to an interval value of type nanoseconds representing the same time span |
| | NANOSECONDS(weeks) | Converts an interval value of type weeks to an interval value of type nanoseconds representing the same time span |
| | NANOSECONDS(nanoseconds) | None |

## Implicit Conversions

In certain situations, {Ocient} SQL can automatically convert expressions to a specific data type without an explicit SQL statement.

### **Cast from Strings**

You can cast string literals to any other data type by placing the name of the data type before the string literal.

**Examples**

**Cast a Double**

Cast the value `1.5` from a string to a floating-point value.

```sql SQL theme={null}
SELECT DOUBLE '1.5';
```

\*Output: \*`1.5`

**Cast an Array**

Cast the array `INT[0,1,2,3]` from a string to an array of integers.

```sql SQL theme={null}
SELECT INT[] 'INT[0,1,2,3]';
```

\*Output: \*`['0','1','2','3']`

**Cast a Point Value**

Cast the point value `POINT(0 0)` to the geospatial data type `POINT`. In this case, the cast keyword is `ST_POINT`, and the data type is `POINT`.

```sql SQL theme={null}
SELECT ST_POINT 'POINT(0 0)';
```

\*Output: \*`POINT(0.0 0.0)`

### Cast from Date and Time Strings

You can cast date and time values to their interval value with the `INTERVAL` keyword.

**Retrieve Interval Values from Various Date and Time Strings**

Retrieve the interval value `5` from the `'5 DAYS'` string.

```sql SQL theme={null}
SELECT INTERVAL '5 DAYS';
```

\*Output: \*`5`

Retrieve the interval value `2` from the `'2 WEEKS'` string.

```sql SQL theme={null}
SELECT INTERVAL '2 WEEKS';
```

\*Output: \*`2`

Retrieve the interval value `10` from the `'10 SECONDS'` string.

```sql SQL theme={null}
SELECT INTERVAL '10 SECONDS';
```

*Output:* `10`

## CAST Functions

These SQL statements allow casting an expression to any other data type.

### CAST

Transforms an expression to any other data type.

This function operates similarly to the [:: Operator](#operator).

**Syntax**

```sql SQL theme={null}
CAST[!] expr [AS] data_type
```

| **Argument** | **Data** **Type** | **Description** |
| - | - | - |
| `expr` | Any | A column or value to transform to another data type. |
| `data_type` | String | Any of the Ocient-supported data types. For a full list, see [Data Types](/data-types). |

<Info>
  As shown in the syntax examples, `CAST` can support the optional `!` operator.

  The `!` operator returns a NULL value if the operation cannot cast to the specified data type. This behavior prevents situations where the system would normally raise an error.
</Info>

**Examples**

**Cast an Integer to Varchar**

Transform the value `2` as a `VARCHAR` string.

```sql SQL theme={null}
SELECT CAST(2 AS VARCHAR);
```

*Output:* `'2'`

**Cast a Varchar to Date**

Transform the `'2020-02-02'` string to the `DATE` data type.

```sql SQL theme={null}
SELECT CAST('2020-02-02' AS DATE);
```

\*Output: \*`'2020-02-02'`

**Cast an Incompatible Type with the** `!` **Operator**

Transform the `'a'` string to an integer, or `INT` data type, using the `!` operator.

```sql SQL theme={null}
SELECT CAST!('a' AS INT);
```

*Output:* `NULL`

This operation generates NULL because the database cannot transform this value to an integer. Without adding this operator, the statement raises an error because the database cannot transform the input expression to a numeric type.

### `::` Operator

Transforms an expression to any other data type.

This function operates similarly to the [CAST](#cast) function.

`::` **Syntax**

```sql SQL theme={null}
expr ::[!] data_type
```

| **Argument** | **Data** **Type** | **Description** |
| - | - | - |
| `expr` | Any | A column or value to transform to another data type. |
| `data_type` | String | Any of the Ocient-supported data types. For a full list, see [Data Types](/data-types). |

<Info>
  As shown in the syntax examples, `::` can support the optional `!` operator.

  The `!` operator returns a NULL value if the operation cannot cast to the specified data type. This behavior prevents situations where the system would normally raise an error.
</Info>

**Examples**

**Cast an Integer to Float**

Transform the `'2'` string to a floating-point number.

```sql SQL theme={null}
SELECT '2' :: FLOAT;
```

*Output:* `2.0`

**Cast to an Incompatible Type with the** `!` **Operator**

Transform the `'a'` string to an integer, or `INT` data type, using the `!` operator.

```sql SQL theme={null}
SELECT 'a' ::! INT;
```

*Output:* `NULL`

This operation generates NULL because the database cannot transform this value to an integer. Without adding this operator, the statement raises an error because the database cannot transform the input expression to a numeric type.

## Map Functions

A map is a set of key-value pairs that the Ocient System stores as two parallel arrays: one array of keys and one array of values. The key at each position in the key array corresponds to the value at the same position in the value array. You can store a map in a single column with the data type `TUPLE<<key_type[], value_type[]>>`, where the first tuple element is the key array and the second tuple element is the value array. The map functionality is similar to a {Python} dictionary `dict` or a hash table.

The map functions insert, retrieve, and remove key-value pairs in a map. Each map function accepts a map as a single `TUPLE` type argument or as two separate `ARRAY` type arguments.

These rules apply to all map functions:

* The key array and the value array can use different element data types, and each array supports scalar element data types such as `INT`, `CHAR`, `DATE`, `TIMESTAMP`, `DECIMAL`, `UUID`, and `POINT`.
* The keys in a map must be unique. If the key array contains duplicate keys, the function returns an error.
* A NULL key is a valid map key. A map can contain at most one entry with a NULL key.
* If the map argument is NULL, the function returns NULL.

### MAP\_DELETE

Removes the entry for the specified key from a map. If the key is not present in the map, the function returns the map unchanged. If you specify a NULL key, the function removes the entry with the NULL key. The function returns the resulting map as a `TUPLE` type containing the key and value arrays.

**Syntaxes**

**Remove a Key from a Map Tuple**

Removes the entry for the specified key from a map that you specify as a tuple.

```sql SQL theme={null}
MAP_DELETE(map, key)
```

| **Argument** | **Data** **Type** | **Description** |
| - | - | - |
| `map` | TUPLE | A map as a `TUPLE` type containing two parallel arrays with the data type `TUPLE<<key_type[], value_type[]>>`. The first tuple element is the key array, and the second tuple element is the value array. |
| `key` | Element data type of the key array | The key to remove from the map. |

**Remove a Key from Separate Key and Value Arrays**

Removes the entry for the specified key from a map that you specify as separate key and value arrays.

```sql SQL theme={null}
MAP_DELETE(keys_array, values_array, key)
```

| **Argument** | **Data** **Type** | **Description** |
| - | - | - |
| `keys_array` | ARRAY | The array that contains the keys of the map. |
| `values_array` | ARRAY | The array that contains the values of the map. The value at each position in this array corresponds to the key at the same position in the `keys_array` argument. |
| `key` | Element data type of the key array | The key to remove from the map. |

**Examples**

**Remove a Key from a Map Tuple**

Remove the key `1` and its value from a map that contains two entries.

```sql SQL theme={null}
SELECT MAP_DELETE(TUPLE<<INT[], INT[]>>(INT[](1, 2), INT[](10, 20)), 1);
```

*Output:* `TUPLE<<INT[], INT[]>>(INT[](2), INT[](20))`

**Remove a Key from Separate Key and Value Arrays**

Remove the key `'bob'` and its value from a map with character keys and integer values.

```sql SQL theme={null}
SELECT MAP_DELETE(CHAR[]('alice', 'bob'), INT[](10, 20), 'bob');
```

*Output:* `TUPLE<<CHAR[], INT[]>>(CHAR[]('alice'), INT[](10))`

### MAP\_GET

Returns the value for the specified key in a map. If the key is not present in the map, the function returns NULL. If you specify a NULL key, the function returns the value for the entry with the NULL key. The function also returns NULL when the stored value for the key is NULL, so a NULL result can mean either that the key is not present or that its value is NULL.

**Syntaxes**

**Retrieve a Value from a Map Tuple**

Returns the value for the specified key from a map that you specify as a tuple.

```sql SQL theme={null}
MAP_GET(map, key)
```

| **Argument** | **Data** **Type** | **Description** |
| - | - | - |
| `map` | TUPLE | A map as a `TUPLE` type containing two parallel arrays with the data type `TUPLE<<key_type[], value_type[]>>`. The first tuple element is the key array, and the second tuple element is the value array. |
| `key` | Element data type of the key array | The key to find in the map. |

**Retrieve a Value from Separate Key and Value Arrays**

Returns the value for the specified key from a map that you specify as separate key and value arrays.

```sql SQL theme={null}
MAP_GET(keys_array, values_array, key)
```

| **Argument** | **Data** **Type** | **Description** |
| - | - | - |
| `keys_array` | ARRAY | The array that contains the keys of the map. |
| `values_array` | ARRAY | The array that contains the values of the map. The value at each position in this array corresponds to the key at the same position in the `keys_array` argument. |
| `key` | Element data type of the key array | The key to find in the map. |

**Examples**

**Retrieve a Value from a Map Tuple**

Retrieve the value for the key `'bob'` from a map with character keys and integer values.

```sql SQL theme={null}
SELECT MAP_GET(TUPLE<<CHAR[], INT[]>>(CHAR[]('alice', 'bob'), INT[](10, 20)), 'bob');
```

*Output:* `20`

**Retrieve a Value from Separate Key and Value Arrays**

Retrieve the value for the key `3`. The key is not present in the map, so the function returns NULL.

```sql SQL theme={null}
SELECT MAP_GET(INT[](1, 2), CHAR[]('x', 'y'), 3);
```

*Output:* `NULL`

### MAP\_PUT

Inserts a key-value pair into a map. If the key is already present in the map, the function replaces its value and keeps its position in the key array. The function returns the resulting map as a `TUPLE` type containing the key and value arrays.

The data type of the `key` argument must match the element data type of the key array, and the data type of the `value` argument must match the element data type of the value array. Otherwise, the function returns an error.

You can insert a NULL key or a NULL value. If the map already contains an entry with a NULL key, the function replaces its value.

**Syntaxes**

**Insert a Key-Value Pair into a Map Tuple**

Inserts a key-value pair into a map that you specify as a tuple.

```sql SQL theme={null}
MAP_PUT(map, key, value)
```

| **Argument** | **Data** **Type** | **Description** |
| - | - | - |
| `map` | TUPLE | A map as a `TUPLE` type containing two parallel arrays with the data type `TUPLE<<key_type[], value_type[]>>`. The first tuple element is the key array, and the second tuple element is the value array. |
| `key` | Element data type of the key array | The key to insert into the map. |
| `value` | Element data type of the value array | The value to associate with the key. |

**Insert a Key-Value Pair into Separate Key and Value Arrays**

Inserts a key-value pair into a map that you specify as separate key and value arrays.

```sql SQL theme={null}
MAP_PUT(keys_array, values_array, key, value)
```

| **Argument** | **Data** **Type** | **Description** |
| - | - | - |
| `keys_array` | ARRAY | The array that contains the keys of the map. |
| `values_array` | ARRAY | The array that contains the values of the map. The value at each position in this array corresponds to the key at the same position in the `keys_array` argument. |
| `key` | Element data type of the key array | The key to insert into the map. |
| `value` | Element data type of the value array | The value to associate with the key. |

**Examples**

**Insert a New Key into a Map Tuple**

Insert the key `2` with the value `20` into a map that contains one entry.

```sql SQL theme={null}
SELECT MAP_PUT(TUPLE<<INT[], INT[]>>(INT[](1), INT[](10)), 2, 20);
```

*Output:* `TUPLE<<INT[], INT[]>>(INT[](1, 2), INT[](10, 20))`

**Update an Existing Key in Separate Key and Value Arrays**

Insert the key `1` with the value `30`. The key is already present in the map, so the function replaces its value while keeping its position.

```sql SQL theme={null}
SELECT MAP_PUT(INT[](1, 2), INT[](10, 20), 1, 30);
```

*Output:* `TUPLE<<INT[], INT[]>>(INT[](1, 2), INT[](30, 20))`

## Related Links

[Date and Time Functions](/date-and-time-functions)

[Character and Binary Functions](/character-and-binary-functions)

[Math Functions and Operators](/math-functions-and-operators)

[Formatting Functions](/formatting-functions)

[Array Functions and Operators](/array-functions-and-operators)

[Tuple Functions and Operators](/tuple-functions-and-operators)