Catégories :

Fonctions de conversion

TO_DECIMAL , TO_NUMBER , TO_NUMERIC

Convertit une expression d’entrée en un nombre à point fixe. Pour une entrée NULL, la sortie est NULL.

Ces fonctions sont synonymes.

Voir aussi :

TRY_TO_DECIMAL, TRY_TO_NUMBER, TRY_TO_NUMERIC

Syntaxe

TO_DECIMAL( <expr> [, '<format>' ] [, <precision> [, <scale> ] ] )

TO_NUMBER( <expr> [, '<format>' ] [, <precision> [, <scale> ] ] )

TO_NUMERIC( <expr> [, '<format>' ] [, <precision> [, <scale> ] ] )
Copy

Arguments

Obligatoire :

expr

Expression de type numérique, caractère ou variante.

Facultatif :

format

Le modèle de format SQL utilisé pour analyser l’entrée expr et la sortie. Pour plus d’informations, voir Modèles de format SQL.

precision

Le nombre maximal de chiffres décimaux dans le nombre résultant ; de 1 à 38. Dans Snowflake, la précision n’est pas utilisée pour déterminer le nombre d’octets nécessaires à l’enregistrement du nombre et n’a pas d’incidence sur l’efficacité. La valeur par défaut est donc le maximum (38).

scale

Nombre de chiffres décimaux fractionnaires (de 0 à la valeur de precision - 1). 0 indique qu’il n’y a pas de chiffres fractionnaires (c’est-à-dire un nombre entier). L’échelle par défaut est 0.

Renvoie

La fonction renvoie NUMBER(precision, scale).

  • Si precision n’est pas spécifié, la valeur par défaut est 38.

  • Si scale n’est pas spécifié, la valeur par défaut est 0.

Notes sur l’utilisation

  • Pour NULL, le résultat est NULL.

  • Pour les nombres à virgule fixe :

    • Les nombres ayant des échelles différentes sont convertis soit en ajoutant des zéros à droite (si l’échelle doit être augmentée), soit en réduisant le nombre de chiffres fractionnaires en arrondissant (si l’échelle doit être diminuée).

    • Notez que les transformations de nombres à virgule fixe en nombres à virgule fixe qui augmentent l’échelle peuvent échouer.

  • Pour les nombres à virgule flottante :

    • Les nombres sont convertis s’ils se situent dans l’intervalle représentable, compte tenu de l’échelle.

    • La conversion entre les nombres fractionnaires binaires et décimaux n’est pas précise. Cela peut entraîner une perte de précision ou des erreurs « out-of-range ».

    • Les valeurs d’infinité et les NaN (non-nombres) entraînent une erreur de conversion.

  • Les chaînes de caractères sont converties en nombres décimaux, entiers, fractionnaires ou à virgule flottante.

    • Pour les entrées fractionnaires, la précision est déduite du nombre de chiffres après le point.

    • Pour les entrées à virgule flottante, l’omission de la mantisse ou de l’exposant est autorisée et est interprétée comme 0. Ainsi, E est analysé comme 0.

  • Pour une entrée VARIANT :

    • Si la variante contient une valeur numérique à virgule fixe ou à virgule flottante, une conversion numérique appropriée est effectuée.

    • Si la variante contient une chaîne, une conversion de chaîne est effectuée.

    • Si la variante contient une valeur booléenne, le résultat est 0 ou 1 (pour false et true, en conséquence).

    • Si la variante contient la valeur JSON null, la sortie est NULL.

Exemples

Créez une table avec une colonne VARCHAR, puis récupérez les valeurs de la chaîne dans la table et transmettez ces valeurs à la fonction TO_NUMBER avec des valeurs precision et scale différentes.

CREATE OR REPLACE TABLE number_conv(expr VARCHAR);
INSERT INTO number_conv VALUES ('12.3456'), ('98.76546');

SELECT expr,
       TO_NUMBER(expr),
       TO_NUMBER(expr, 10, 1),
       TO_NUMBER(expr, 10, 8)
  FROM number_conv;
Copy

La requête renvoie le résultat suivant :

+----------+-----------------+------------------------+------------------------+
| EXPR     | TO_NUMBER(EXPR) | TO_NUMBER(EXPR, 10, 1) | TO_NUMBER(EXPR, 10, 8) |
|----------+-----------------+------------------------+------------------------|
| 12.3456  |              12 |                   12.3 |            12.34560000 |
| 98.76546 |              99 |                   98.8 |            98.76546000 |
+----------+-----------------+------------------------+------------------------+

Essayez une requête sur la même table en utilisant la fonction TO_NUMBER pour retourner un nombre avec l’argument precision de 10 et l’échelle de 9.

SELECT expr, TO_NUMBER(expr, 10, 9) FROM number_conv;
Copy

Avec l’argument precision défini sur 10, le nombre maximal de chiffres décimaux dans les résultats est 10. Comme les deux valeurs de la table ont deux chiffres avant la virgule et que scale est défini sur 9, la requête renvoie une erreur, car les résultats renverraient 11 chiffres.

100039 (22003): Numeric value '12.3456' is out of range

Utilisez différents éléments de format avec la fonction TO_DECIMAL dans une requête :

SELECT column1,
       TO_DECIMAL(column1, '99.9') as D0,
       TO_DECIMAL(column1, '99.9', 9, 5) as D5,
       TO_DECIMAL(column1, 'TM9', 9, 5) as TD5
  FROM VALUES ('1.0'), ('-12.3'), ('0.0'), ('- 0.1');
Copy

La requête renvoie le résultat suivant :

+---------+-----+-----------+-----------+
| COLUMN1 |  D0 |        D5 |       TD5 |
|---------+-----+-----------+-----------|
| 1.0     |   1 |   1.00000 |   1.00000 |
| -12.3   | -12 | -12.30000 | -12.30000 |
| 0.0     |   0 |   0.00000 |   0.00000 |
| - 0.1   |   0 |  -0.10000 |  -0.10000 |
+---------+-----+-----------+-----------+

La sortie montre que l’élément de format de texte minimal TM9 affiche précisément le nombre de chiffres de la partie fractionnaire sur la base de l’échelle spécifiée. Pour plus d’informations, voir Formats numériques de texte minimal.

Convertissez un nombre qui utilise une virgule pour séparer des groupes de chiffres :

SELECT column1,
       TO_DECIMAL(column1, '9,999.99', 6, 2) as convert_number
  FROM VALUES ('3,741.72');
Copy

La requête renvoie le résultat suivant :

+----------+----------------+
| COLUMN1  | CONVERT_NUMBER |
|----------+----------------|
| 3,741.72 |        3741.72 |
+----------+----------------+

Convertissez une valeur monétaire qui utilise une virgule pour séparer des groupes de chiffres :

SELECT column1,
       TO_DECIMAL(column1, '$9,999.99', 6, 2) as convert_currency
  FROM VALUES ('$3,741.72');
Copy

La requête renvoie le résultat suivant :

+-----------+------------------+
| COLUMN1   | CONVERT_CURRENCY |
|-----------+------------------|
| $3,741.72 |          3741.72 |
+-----------+------------------+

Utilisez l’élément de format X avec la fonction TO_DECIMAL pour convertir une valeur hexadécimale en valeur décimale :

SELECT TO_DECIMAL('ae5', 'XXX');
Copy

La requête renvoie le résultat suivant :

+--------------------------+
| TO_DECIMAL('AE5', 'XXX') |
|--------------------------|
|                     2789 |
+--------------------------+

Le nombre de chiffres de l’élément format doit être égal ou supérieur au nombre de chiffres de l’expression. Par exemple, essayez d’exécuter la requête suivante :

SELECT TO_DECIMAL('ae5', 'XX');
Copy

La requête renvoie une erreur :

100140 (22007): Can't parse 'ae5' as number with format 'XX'
Langage: Français