Categories:: Semi-structured and structured data functions (Type Predicates)

IS_<object_type>¶

This family of functions serves as Boolean predicates that can be used to determine the data type of a value stored in a VARIANT column:

See also:: AS_<object_type> , TYPEOF

General usage notes¶

All the functions are unary, taking a VARIANT expression as the only argument.
All the functions return FALSE if the input is SQL NULL or the VARIANT expression contains NULL.

Examples¶

Create and fill the vartab table. The INSERT statement uses the PARSE_JSON function to insert VARIANT values in the v column of the table.

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;

Query the data. The query uses the TYPEOF function to show the data types of the values stored in the VARIANT column.

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              |            |
|   | }                      |            |
+---+------------------------+------------+

Count all rows in vartab table where the VARIANT column v contains a string value:

SELECT COUNT(*) FROM vartab WHERE IS_VARCHAR(v);

+----------+
| COUNT(*) |
|----------|
|        1 |
+----------+

Select rows in vartab table where the VARIANT column v contains the specified data type:

SELECT * FROM vartab WHERE IS_NULL_VALUE(v);

+---+------+
| N | V    |
|---+------|
| 1 | null |
+---+------+

SELECT * FROM vartab WHERE IS_BOOLEAN(v);

+---+------+
| N | V    |
|---+------|
| 3 | true |
+---+------+

SELECT * FROM vartab WHERE IS_INTEGER(v);

+---+-----+
| N | V   |
|---+-----|
| 4 | -17 |
+---+-----+

SELECT * FROM vartab WHERE IS_DECIMAL(v);

+---+--------+
| N | V      |
|---+--------|
| 4 | -17    |
| 5 | 123.12 |
+---+--------+

SELECT * FROM vartab WHERE IS_DOUBLE(v);

+---+-----------------------+
| N | V                     |
|---+-----------------------|
| 4 | -17                   |
| 5 | 123.12                |
| 6 | 1.912000000000000e+02 |
+---+-----------------------+

SELECT * FROM vartab WHERE IS_VARCHAR(v);

+---+------------------------+
| N | V                      |
|---+------------------------|
| 7 | "Om ara pa ca na dhih" |
+---+------------------------+

SELECT * FROM vartab WHERE IS_ARRAY(v);

+---+-------------+
| N | V           |
|---+-------------|
| 8 | [           |
|   |   -1,       |
|   |   12,       |
|   |   289,      |
|   |   2188,     |
|   |   false,    |
|   |   undefined |
|   | ]           |
+---+-------------+

SELECT * FROM vartab WHERE IS_OBJECT(v);

+---+---------------+
| N | V             |
|---+---------------|
| 9 | {             |
|   |   "x": "abc", |
|   |   "y": false, |
|   |   "z": 10     |
|   | }             |
+---+---------------+