Categories:

Data metric functions

INVALID_INTEGER_COUNT (system data metric function)

Returns the count of non-NULL column values that are not an integer: an optional sign followed by digits.

This topic provides the syntax for calling the function directly. To learn how to associate the function with a table or view so it runs at regular intervals, see Associate a DMF.

Syntax

SNOWFLAKE.CORE.INVALID_INTEGER_COUNT(<query>)

Arguments

query

Specifies a SQL query that projects a single column.

Allowed data types

The column projected by the query must have the VARCHAR data type.

Returns

The function returns a NUMBER value. NULL values are not counted.

Usage notes

  • The value must be an optional + or - sign followed by one or more digits. Leading zeros are allowed.
  • Decimals such as 3.7, exponent notation such as 1e5, thousands separators, surrounding whitespace, and the empty string are counted.
  • The function checks the format only; it does not check whether the value fits in a NUMBER column. To check whether values can be cast to a number, use INVALID_NUMERIC_TYPE_CAST_COUNT.

Example

Measure the number of values that are not whole integers in the quantity column:

SELECT SNOWFLAKE.CORE.INVALID_INTEGER_COUNT(
  SELECT
    quantity
  FROM sales.tables.order_lines
);