Modèles de format SQL¶

Éléments de format¶

Les éléments de format sont des séquences de chiffres et/ou de lettres (généralement insensibles à la casse) et, dans certains cas, de symboles. Les éléments de format peuvent être directement concaténés les uns aux autres.

Certains éléments de format sont couramment utilisés dans tous les modèles de format pour contrôler l’impression et la correspondance du texte d’entrée. D’autres éléments de format ont des utilisations spécifiques en fonction du type de valeurs utilisés pour la conversion entrante/sortante. Pour plus d’informations, voir les sections suivantes de ce chapitre :

• Modificateurs de formats et gestion générique des espaces

• Éléments de format à position fixe

• Éléments de format de texte minimal

Littéraux de formats¶

Les littéraux de formats sont des séquences qui peuvent être composées de combinaisons des éléments suivants :

• Chaînes de caractères arbitraires délimitées par des guillemets doubles (un guillemet double est représenté par deux guillemets doubles adjacents).

• Un ou plusieurs des symboles suivants :

  Symbole/Caractère	Remarques
  . (point)	Dans les modèles numériques fixes, traités comme un élément de format lorsqu’ils suivent 0, 9 ou X ; sinon, conservés tels quels.
  , (virgule)	Dans les modèles numériques, traités comme un élément de format lorsqu’ils suivent 0, 9 ou X ; sinon, conservés tels quels.
  ; (point-virgule)	Toujours préservé tel quel.
  : (deux-points)	Toujours préservé tel quel.
  - (signe moins)	Toujours préservé tel quel.
  = (signe égal)	Toujours préservé tel quel.
  / (barre oblique)	Toujours préservé tel quel.
  ( (parenthèse gauche)	Toujours préservé tel quel.
  ) (parenthèse droite)	Toujours préservé tel quel.

Un littéral est toujours imprimé tel quel, exactement là où il se trouvait dans le modèle de format.

Voici un bref exemple d’utilisation d’un modèle de format SQL pour imprimer le signe moins après un nombre plutôt qu’avant celui-ci. Le MI indique où mettre le signe moins si le nombre est négatif.

select to_varchar(-123.45, '999.99MI') as EXAMPLE;

Copy

La sortie devrait ressembler à 123.45- plutôt qu’à -123.45 par défaut.

D’autres exemples sont inclus à la fin de ce chapitre.

Impression des chaînes de sortie à l’aide du modificateur du mode Remplissage¶

Par défaut, le mode de remplissage est réglé sur remplissage et le modificateur FM du mode de remplissage le fait basculer vers compact ; l’utilisation répétée le refait basculer vers remplissage, etc.

Dans la plupart des cas, l’utilisation du mode remplissage lors de l’impression garantit que les éléments de format produisent une sortie d’une largeur fixe en décalant les nombres à gauche avec des zéros ou des espaces d’en-tête, et en décalant le texte avec des espaces à droite. Ceci garantit que la sortie en colonnes dans des polices à largeur fixe est alignée.

En mode compact, la plupart des éléments de format ne produisent qu’une sortie de largeur minimale (c’est-à-dire que les zéros et les espaces d’en-tête et les espaces de fin sont supprimés).

Les éléments de format qui ne respectent pas ces règles sont explicitement indiqués ci-dessous.

Le modificateur de correspondance exacte, FX n’affecte pas l’impression ; l’élément de format de barre de soulignement n’imprime rien.

Analyse des chaînes d’entrée à l’aide des modificateurs¶

L’analyse des chaînes d’entrée est influencée à la fois par le modificateur du mode de remplissage, FM, et par le modificateur de correspondance exacte FX. Dans un premier temps :

• Le mode de remplissage est réglé sur remplissage et FM le bascule sur compact et inversement.

• Le mode de correspondance exacte est réglé sur lâche et FX le bascule sur exact et inversement.

Toutes les correspondances entre les chaînes et les éléments de format et les littéraux pendant l’analyse sont insensibles à la casse.

En mode lâche, la première étape de l’analyse d’entrée consiste à sauter l’espace blanc d’en-tête (une séquence d’espaces, de tabulations, de caractères LF, CR, FF et VT) ; le mode au début de l’entrée est strict si le premier élément de format est FX. Sinon, il est lâche.

Dans le mode de correspondance lâche, les espaces dans les littéraux sont comparés à n’importe quelle séquence d’entrée d’espaces non vides ; les caractères non spatiaux sont comparés un à un. En mode exact, tous les caractères d’un littéral doivent correspondre aux caractères saisis.

Les éléments du format numérique sont comparés aux séquences de chiffres correspondantes :

• Si les modes remplissage et exact sont en vigueur, le nombre de chiffres doit correspondre exactement à la largeur des éléments de format numérique correspondants (des zéros d’en-tête sont attendus).

• Si le mode compact ou lâche est activé, un numéro d’entrée correspondant doit avoir, au maximum, le nombre de chiffres égal à la largeur maximale de l’élément de format, et au moins un chiffre ; les zéros d’en-tête sont ignorés.

Les éléments de format textuel sont mis en correspondance sans prendre en compte la casse :

• Si les modes remplissage et exact sont en vigueur, le nombre d’espaces de fin, jusqu’à la largeur maximale de l’élément, est attendu.

• Sinon, les espaces après les éléments textuels de longueur variable sont ignorés en mode lâche, et la correspondance exacte avec le mot réel (sans espaces de remplissage) est attendue en mode exact.

Enfin, l’espace blanc de fin jusqu’à la fin de la chaîne d’entrée est ignoré si le mode actuel est lâche.

En principe, les modes lâche et exact ne permettent pas de faire correspondre les espaces lorsque ces derniers ne sont pas présents dans le modèle de format ou ne pouvaient pas être générés en imprimant le contenu des éléments de format en mode remplissage.

Les endroits où les espaces doivent être ignorés s’ils sont présents dans les modes lâche et exact peuvent être explicitement marqués en utilisant l’élément de format _ (barre de soulignement).

En règle générale, un format en mode exact ne reconnaît que les chaînes d’entrée imprimées par le même format, tandis qu’un format en mode lâche reconnaît les chaînes d’entrée imprimées par le même format avec tous les modificateurs du mode de remplissage ajoutés ou supprimés.

Formats numériques à position fixe¶

Les nombres à position fixe sont représentés par des éléments numériques, 0 ou 9. Par exemple, 999 contient des nombres de 1 à 3 chiffres décimaux. La partie fractionnaire des nombres est délimitée par des éléments séparateurs, . (point) ou D :

• . est toujours rendu comme un point.

• Pour utiliser un caractère différent pour les éléments D, modifiez la chaîne d’entrée pour remplacer tous les points par des virgules et toutes les virgules par des points avant d’appliquer la fonction de conversion.

En principe, les zéros d’en-tête dans la partie entière et les zéros en fin de ligne dans la partie fractionnaire sont remplacés par des espaces (sauf dans les cas où la valeur de la partie entière est égale à zéro, auquel cas elle est rendue comme un seul caractère 0). Pour supprimer ce comportement, utilisez l’élément de format 0 à la place de 9 ; les positions correspondantes ont 0 caractères préservés. L’élément de format B, lorsqu’il est utilisé avant le nombre, supprime le dernier 0 de la valeur entière (c’est-à-dire que si vous utilisez B et que la valeur de la partie entière du nombre est nulle, tous les chiffres s’affichent comme des espaces).

Le séparateur de groupe de chiffres , (virgule) ou G imprime le caractère de séparateur de groupe correspondant si le nombre est suffisamment grand pour que les chiffres soient sur les deux côtés du séparateur de groupe. Un exemple de modèle de format utile pour l’impression des sommes monétaires serait 999,999.00.

Lorsqu’il y a plus de chiffres dans la partie entière du nombre qu’il n’y a de positions de chiffres dans le format, tous les chiffres sont imprimés en tant que # pour indiquer un dépassement.

L’élément exposant permet de normaliser les nombres à position fixe de sorte que le premier chiffre de la partie entière soit compris entre 1 et 9 (sauf si la valeur du nombre est égale à zéro, auquel cas la valeur de l’exposant est également égale à zéro). L’élément EE sélectionne automatiquement le bon nombre de chiffres dans l’exposant et n’imprime pas le signe +, tandis que EEE, EEEE et EEEEE impriment toujours le signe + ou - de l’exposant et le nombre de chiffres demandé (les zéros d’en-tête ne sont pas supprimés). Le dépassement de l’exposant est indiqué par le signe # à la place des chiffres.

Les indicateurs exposants impriment un E majuscule ou un e minuscule selon la casse de la première lettre de l’élément de format.

L’élément de format X fonctionne comme un 9, sauf que les chiffres hexadécimaux 0-9A-F sont imprimés. Actuellement, les fractions hexadécimales ne sont pas prises en charge. Semblable à 9, X remplace les zéros d’en-tête par des espaces. L’élément 0, lorsqu’il est utilisé avec X imprime des chiffres hexadécimaux sans supprimer le zéro d’en-tête (utilisez donc 000X pour imprimer des nombres hexadécimaux qui contiennent toujours 4 chiffres).

Notez que X imprime les chiffres hexadécimaux en lettres latines majuscules et x minuscule imprime des lettres minuscules en lettres latines. L’élément de format 0 hexadécimal utilise la casse de l’élément de format X suivant.

En principe, les nombres hexadécimaux sont imprimés comme non signés, c’est-à-dire que les nombres négatifs ont tous des 1 dans les bits les plus significatifs, mais l’utilisation de l’élément X avec un signe explicite (S ou MI) fait imprimer le signe - avec la valeur absolue du nombre.

Les modèles de format numérique à position fixe signalent un débordement sur des valeurs spéciales (infinies ou non) de nombres en virgule flottante.

Formats numériques en texte minimal¶

Alors que les modèles de format numérique à position fixe spécifient toujours explicitement le nombre de chiffres, les éléments de format de texte minimal utilisent un nombre minimal de chiffres basé sur la valeur du nombre. Les éléments de format TM* produisent toujours une sortie de longueur variable sans espaces, quel que soit le modificateur du mode de remplissage (remplissage ou compact).

• TM9 imprime le nombre sous forme de nombre entier ou de fraction décimale, en fonction de la valeur du nombre. Toute valeur numérique décimale à virgule fixe est imprimée avec précision avec le nombre de chiffres de la partie fractionnaire déterminé par l’échelle du nombre (les zéros de fin de ligne sont conservés en mode remplissage).

• Pour les nombres à virgule flottante, TM9 choisit le nombre de chiffres fractionnaires en fonction de l’exposant du nombre (notez que la conversion binaire en fraction décimale précise n’est pas possible). Si l’amplitude du nombre à virgule flottante est trop grande, ce qui rend la notation positionnelle trop longue, la notation scientifique est activée (voir TME ci-dessous). Si le nombre à virgule flottante est trop petit, TM9 imprime zéro.

• TME imprime le nombre en notation scientifique, c’est-à-dire avec un exposant (identique à EE) et un chiffre dans la position entière de la partie fractionnaire. La casse de l’indicateur de l’exposant (E ou e) correspond à la casse de la première lettre (T ou t) de l’élément de format.

• TM choisit TM9 ou TME en fonction de l’importance du nombre, pour minimiser la longueur du texte tout en préservant la précision.

Formats par défaut pour l’impression¶

Le tableau suivant répertorie les formats d’impression par défaut :

Formats par défaut pour l’analyse¶

Le tableau suivant répertorie les formats par défaut pour l’analyse :

La liste des formats utilisés pour la conversion optimiste automatique des chaînes (c’est-à-dire pour les chaînes qui sont automatiquement reconnues comme numériques) est l’union de tous les formats du tableau ci-dessus des formats d’entrée par défaut.

Exemples de sortie¶

Cet exemple montre comment afficher les nombres avec des zéros d’en-tête :

create table sample_numbers (f float);
insert into sample_numbers (f) values (1.2);
insert into sample_numbers (f) values (123.456);
insert into sample_numbers (f) values (1234.56);
insert into sample_numbers (f) values (-123456.789);
select to_varchar(f, '999,999.999'), to_varchar(f, 'S000,000.000') from sample_numbers;

Copy

La sortie ressemblera à :

+------------------------------+-------------------------------+
| TO_VARCHAR(F, '999,999.999') | TO_VARCHAR(F, 'S000,000.000') |
+==============================+===============================+
|        1.2                   | +000,001.200                  |
+------------------------------+-------------------------------+
|      123.456                 | +000,123.456                  |
+------------------------------+-------------------------------+
|    1,234.56                  | +001,234.560                  |
+------------------------------+-------------------------------+
| -123,456.789                 | -123,456.789                  |
+------------------------------+-------------------------------+

Copy

Vous n’avez pas besoin de zéros d’en-tête pour aligner des nombres. Le mode de remplissage par défaut est « remplissage », ce qui signifie que des espaces d’en-tête sont utilisés pour aligner les nombres en fonction de la position des points décimaux.

select to_varchar(f, '999,999.999'), to_varchar(f, 'S999,999.999') from sample_numbers;

Copy

La sortie ressemblera à :

+------------------------------+-------------------------------+
| TO_VARCHAR(F, '999,999.999') | TO_VARCHAR(F, 'S999,999.999') |
+==============================+===============================+
|        1.2                   |       +1.2                    |
+------------------------------+-------------------------------+
|      123.456                 |     +123.456                  |
+------------------------------+-------------------------------+
|    1,234.56                  |   +1,234.56                   |
+------------------------------+-------------------------------+
| -123,456.789                 | -123,456.789                  |
+------------------------------+-------------------------------+

Copy

Cet exemple montre ce qui se passe si vous utilisez le modificateur FM (Fill Mode) pour passer du mode « remplissage » au mode « compact », c’est-à-dire pour supprimer les premiers caractères qui aligneraient les nombres :

select  to_varchar(f, '999,999.999'), to_varchar(f, 'FM999,999.999') from sample_numbers;

Copy

La sortie ressemblera à :

+------------------------------+--------------------------------+
| TO_VARCHAR(F, '999,999.999') | TO_VARCHAR(F, 'FM999,999.999') |
+==============================+================================+
|        1.2                   | 1.2                            |
+------------------------------+--------------------------------+
|      123.456                 | 123.456                        |
+------------------------------+--------------------------------+
|    1,234.56                  | 1,234.56                       |
+------------------------------+--------------------------------+
| -123,456.789                 | -123,456.789                   |
+------------------------------+--------------------------------+

Copy

Cet exemple montre comment afficher les nombres en notation exponentielle :

select to_char(1234, '9d999EE'), 'will look like', '1.234E3';

Copy

La sortie ressemblera à :

+--------------------------+------------------+-----------+
| TO_CHAR(1234, '9D999EE') | 'WILL LOOK LIKE' | '1.234E3' |
+==========================+==================+===========+
| 1.234E3                  |  will look like  |  1.234E3  |
+--------------------------+------------------+-----------+

Copy

Ceci montre comment inclure des littéraux dans la sortie. Les parties littérales sont délimitées par des guillemets doubles (qui, à leur tour, se trouvent à l’intérieur de guillemets simples qui délimitent la chaîne).

select to_char(12, '">"99"<"');

Copy

La sortie ressemblera à :

+-------+
| > 12< |
+-------+

Copy

Exemples d’entrées¶

Ces exemples illustrent l’utilisation de modèles de formats pour les entrées.

L’exemple suivant montre quelques opérations d’entrée simples, en mettant l’accent sur la différence entre « 0 » et « 9 » pour spécifier le format des chiffres.

Le chiffre « 9 » en tant que formateur accepte les espaces ou les chiffres d’en-tête « manquants ». Le chiffre « 0 » en tant que formateur n’accepte pas les espaces ou les zéros d’en-tête manquants.

-- All of the following convert the input to the number 12,345.67.
SELECT TO_NUMBER('012,345.67', '999,999.99', 8, 2);
SELECT TO_NUMBER('12,345.67', '999,999.99', 8, 2);
SELECT TO_NUMBER(' 12,345.67', '999,999.99', 8, 2);
-- The first of the following works, but the others will not convert.
-- (They are not supposed to convert, so "failure" is correct.)
SELECT TO_NUMBER('012,345.67', '000,000.00', 8, 2);
SELECT TO_NUMBER('12,345.67', '000,000.00', 8, 2);
SELECT TO_NUMBER(' 12,345.67', '000,000.00', 8, 2);

Copy

Ceci montre comment accepter l’un des deux formats numériques (-### ou ###-).

-- Create the table and insert data.
create table format1 (v varchar, i integer);
insert into format1 (v) values ('-101');
insert into format1 (v) values ('102-');
insert into format1 (v) values ('103');

-- Try to convert varchar to integer without a
-- format model.  This fails (as expected)
-- with a message similar to:
--    "Numeric value '102-' is not recognized"
update format1 set i = TO_NUMBER(v);

-- Now try again with a format specifier that allows the minus sign
-- to be at either the beginning or the end of the number.
-- Note the use of the vertical bar ("|") to indicate that
-- either format is acceptable.
update format1 set i = TO_NUMBER(v, 'MI999|999MI');
select i from format1;

Copy