`conv` function

Applies to: check marked yes Databricks SQL Databricks Runtime

Converts num from fromBase to toBase.

Syntax

conv(num, fromBase, toBase)

Arguments

num: An STRING expression expressing a number in fromBase.
fromBase: An INTEGER expression denoting the source base.
toBase: An INTEGER expression denoting the target base.

Returns

A STRING.

The function supports base 2 to base 36. The digit ‘A’ (or ‘a’) represents decimal 10 and ‘Z’ (or ‘z’) represents decimal 35. The range of values supported spans that of a BIGINT.

If fromBase is less than 2, or toBase is -1, 0, or 1, then the result is NULL.

If toBase is negative num is interpreted as a signed number, otherwise it so treated as an unsigned number.

If num is out of range Databricks SQL and Databricks Runtime 13.3 LTS and above raises an ARITHMETIC_OVERFLOW.

Warning

In Databricks Runtime if spark.sql.ansi.enabled is false, an overflow does not cause an error but “wraps” the result instead.

Examples

> SELECT conv('100', 2, 10);
 4

> SELECT conv('-10', 16, 10);
 18446744073709551600

> SELECT conv('-10', 16, -10);
 -16

> SELECT conv('-1', 10, 10);
 18446744073709551615

> SELECT conv('FFFFFFFFFFFFFFFFF', 16, 10);
 Error: ARITHMETIC_OVERFLOW

> SELECT conv('FFFFFFFFFFFFFFFF', 16, 10);
 18446744073709551615

> SELECT conv('FFFFFFFFFFFFFFFF', 16, -10);
 -1

conv function