TO_NUMBER Oracle : Convertir une chaîne de caractères en nombre

La fonction TO_NUMBER est l’une des fonctions de conversion les plus utilisées en Oracle SQL. Elle permet de transformer une chaîne de caractères ou une valeur en un type numérique exploitable dans des calculs, des comparaisons ou des agrégations. Maîtriser TO_NUMBER est indispensable pour tout développeur ou analyste travaillant avec des bases de données Oracle en environnement professionnel.

Définition et utilisation de TO_NUMBER

La fonction TO_NUMBER convertit une expression — généralement une chaîne de caractères (CHAR, VARCHAR2, NCHAR, NVARCHAR2) ou un type NUMBER — vers le type de données NUMBER d’Oracle.

En entreprise, ce besoin est omniprésent dans les situations suivantes :

• Imports de données : les fichiers CSV ou les flux XML/JSON contiennent souvent des montants monétaires stockés sous forme de texte ('1 250,99', '$3000.00').
• Interfaces entre systèmes : des applications tierces transmettent des valeurs numériques encodées en chaînes vers la base Oracle.
• Calculs financiers : avant d’additionner ou de comparer des colonnes textuelles contenant des prix, des taux ou des quantités, une conversion explicite est nécessaire.
• Nettoyage et transformation de données : dans les pipelines ETL (Extract, Transform, Load), TO_NUMBER est une brique fondamentale pour garantir la qualité des données.

Sans cette conversion, Oracle lèverait des erreurs lors de toute opération arithmétique sur des types texte, ou produirait des résultats incorrects lors de comparaisons implicites.

Syntaxe de TO_NUMBER

Voici la syntaxe officielle de la fonction TO_NUMBER sous Oracle :

TO_NUMBER( expression [, format_mask [, nls_params]] )

Paramètres

Principaux éléments du masque de format

Exemples pratiques de TO_NUMBER

Exemple 1 — Conversion simple d’un montant textuel en nombre

Contexte métier : Une table FACTURES_IMPORT reçoit des données d’un fichier CSV où les montants sont stockés sous forme de chaîne avec un point comme séparateur décimal. On souhaite calculer le total des montants.

-- Sélection et conversion des montants textuels en valeurs numériques
-- La colonne MONTANT_TEXTE contient des valeurs du type '1500.75', '320.00', etc.

SELECT
    ID_FACTURE,
    MONTANT_TEXTE,
    TO_NUMBER(MONTANT_TEXTE, '999999D99') AS MONTANT_NUMERIQUE
FROM FACTURES_IMPORT;

-- Calcul du total en convertissant à la volée
SELECT
    SUM(TO_NUMBER(MONTANT_TEXTE, '999999D99')) AS TOTAL_FACTURES
FROM FACTURES_IMPORT;

Dans cet exemple, le masque '999999D99' indique qu’on attend au maximum 6 chiffres entiers et 2 décimales, séparées par le caractère décimal NLS par défaut (ici le point).

Exemple 2 — Conversion avec séparateur de milliers et paramètres NLS

Contexte métier : Un système de gestion européen stocke des prix au format français : virgule comme séparateur décimal et espace comme séparateur de milliers. La table CATALOGUE_PRODUITS contient une colonne PRIX_TEXTE avec des valeurs comme '1 250,99'.

-- Conversion d'un nombre au format européen (virgule = décimale, espace = milliers)
-- NLS_NUMERIC_CHARACTERS = ', ' signifie : virgule pour les décimales, espace pour les milliers

SELECT
    CODE_PRODUIT,
    LIBELLE,
    PRIX_TEXTE,
    TO_NUMBER(
        PRIX_TEXTE,
        '999G999D99',
        'NLS_NUMERIC_CHARACTERS = '', '''
    ) AS PRIX_NUMERIQUE
FROM CATALOGUE_PRODUITS;

-- Application d'une remise de 10 % sur le résultat converti
SELECT
    CODE_PRODUIT,
    ROUND(
        TO_NUMBER(
            PRIX_TEXTE,
            '999G999D99',
            'NLS_NUMERIC_CHARACTERS = '', '''
        ) * 0.90,
        2
    ) AS PRIX_APRES_REMISE
FROM CATALOGUE_PRODUITS;

Le troisième paramètre NLS est ici crucial : sans lui, Oracle interpréterait la virgule comme un séparateur de milliers, ce qui provoquerait une erreur de conversion ou un résultat erroné.

Erreurs courantes avec TO_NUMBER

Erreur ORA-01722 : invalid number

C’est l’erreur la plus fréquente avec TO_NUMBER. Elle se produit lorsque la chaîne fournie ne correspond pas au masque de format spécifié, ou lorsqu’aucun masque n’est fourni et que la chaîne contient des caractères non numériques.

-- ❌ Exemple incorrect : la chaîne contient un symbole monétaire non pris en charge
SELECT TO_NUMBER('€1500.00') FROM DUAL;
-- Résultat : ORA-01722: invalid number

-- ✅ Solution 1 : nettoyer la chaîne avant conversion avec REGEXP_REPLACE
SELECT TO_NUMBER(REGEXP_REPLACE('€1500.00', '[^0-9\.]', '')) FROM DUAL;
-- Résultat : 1500

-- ✅ Solution 2 : utiliser le masque L pour le symbole monétaire local
-- (nécessite que le symbole corresponde au paramètre NLS_CURRENCY de la session)
SELECT TO_NUMBER('€1500.00', 'L9999D99', 'NLS_CURRENCY = ''€'' NLS_NUMERIC_CHARACTERS = ''.''')
FROM DUAL;
-- Résultat : 1500

Bonne pratique : Avant d’appliquer TO_NUMBER sur des données d’import, vérifiez toujours le format exact des valeurs sources et utilisez REGEXP_REPLACE ou TRANSLATE pour normaliser les chaînes si nécessaire.

Résumé

Deux bonnes pratiques Oracle

1. Toujours spécifier le masque de format lorsque les données proviennent de sources externes (CSV, API, ERP). Une conversion implicite sans masque peut silencieusement échouer sur certaines lignes si les formats varient d’un enregistrement à l’autre.
2. Combiner TO_NUMBER avec CASE ou REGEXP_LIKE pour sécuriser les conversions en masse : vérifiez d’abord que la chaîne est bien numérique avant de convertir, afin d’éviter qu’une seule ligne invalide ne bloque toute une requête.

Aller plus loin

Pour approfondir votre maîtrise des conversions et du formatage de données sous Oracle, découvrez ces sujets complémentaires :

• TO_CHAR Oracle — la fonction inverse de TO_NUMBER, permettant de formater un nombre ou une date en chaîne de caractères selon un masque personnalisé.
• TO_DATE Oracle — l’équivalent de TO_NUMBER pour les dates : convertir une chaîne en type DATE avec un masque de format précis.
• CAST Oracle — une alternative standardisée SQL ANSI pour convertir des types de données, compatible avec plusieurs SGBD et utile dans les environnements multi-bases.

Sur le même thème

• ADD_MONTHS Oracle : fonction SQL pour les dates
• Fonctions analytiques SQL Oracle – Guide complet
• Fonctions d’agrégation SQL Oracle – Guide complet
• ROW_NUMBER Oracle : fonction analytique SQL expliquée
• MONTHS_BETWEEN Oracle : calcul de mois entre deux dates
• LAST_DAY Oracle : Fonction SQL pour les dates

Veuillez laisser ce champ vide

Un nouveau tutoriel SQL par semaine

Nous ne spammons pas ! Consultez notre politique de confidentialité pour plus d’informations.

Vérifiez votre boite de réception ou votre répertoire d’indésirables pour confirmer votre abonnement.