Categories:

Data metric functions

INVALID_FR_INSEE_CODE_COUNT (system data metric function)

Returns the count of non-NULL column values that are not a French INSEE commune code.

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_FR_INSEE_CODE_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 a department code (01 to 19, 2A, 2B, or 21 to 95) followed by three digits, or an overseas code (971 to 978 or 986 to 988) followed by two digits.
  • Letters are matched case-insensitively.
  • The whole value must match. A value with leading or trailing whitespace, or with separators other than the ones described here, is counted.
  • An INSEE commune code is not a postal code. To check postal codes, use INVALID_FR_POSTAL_CODE_COUNT.
  • The function checks the format only; it does not verify that the commune exists.

Example

Measure the number of invalid French INSEE commune codes in the insee_code column:

SELECT SNOWFLAKE.CORE.INVALID_FR_INSEE_CODE_COUNT(
  SELECT
    insee_code
  FROM crm.tables.addresses
);