Categories:

Semi-structured Data Functions (Type Predicates)

TYPEOF¶

Reports the type of a value stored in a VARIANT column. The type is returned as a string.

See also:

IS_<object_type> , SYSTEM$TYPEOF

Syntax¶

TYPEOF( <expr> )
Copy

Arguments¶

expr

The argument can be a column name or a general expression.

Returns¶

Returns a VARCHAR that contains the data type of the input expression, for example, BOOLEAN, DECIMAL, ARRAY, OBJECT, etc.

Usage Notes¶

  • The returned string might be DECIMAL even if the input is an exact integer, due to optimizations that change the physical storage type of the input.

Examples¶

Create a table that contains different types of data stored inside a VARIANT column, then use TYPEOF to determine the data types of each piece of data.

Create and fill a table. Note that the INSERT statement uses the PARSE_JSON function.

create or replace table vartab (n number(2), v variant);

insert into vartab
    select column1 as n, parse_json(column2) as v
    from values (1, 'null'), 
                (2, null), 
                (3, 'true'),
                (4, '-17'), 
                (5, '123.12'), 
                (6, '1.912e2'),
                (7, '"Om ara pa ca na dhih"  '), 
                (8, '[-1, 12, 289, 2188, false,]'), 
                (9, '{ "x" : "abc", "y" : false, "z": 10} ') 
       AS vals;
Copy

Query the data:

select n, v, typeof(v)
    from vartab
    order by n;
+---+------------------------+------------+
| N | V                      | TYPEOF(V)  |
|---+------------------------+------------|
| 1 | null                   | NULL_VALUE |
| 2 | NULL                   | NULL       |
| 3 | true                   | BOOLEAN    |
| 4 | -17                    | INTEGER    |
| 5 | 123.12                 | DECIMAL    |
| 6 | 1.912000000000000e+02  | DOUBLE     |
| 7 | "Om ara pa ca na dhih" | VARCHAR    |
| 8 | [                      | ARRAY      |
|   |   -1,                  |            |
|   |   12,                  |            |
|   |   289,                 |            |
|   |   2188,                |            |
|   |   false,               |            |
|   |   undefined            |            |
|   | ]                      |            |
| 9 | {                      | OBJECT     |
|   |   "x": "abc",          |            |
|   |   "y": false,          |            |
|   |   "z": 10              |            |
|   | }                      |            |
+---+------------------------+------------+
Copy
Language: English