Guide Du Langage SQL De Firebird 5.0 | Expressions

`AT`

Disponible en

DSQL, PSQL.

Syntaxe

<expr> AT {TIME ZONE <time zone string> | LOCAL}

<time zone string> ::=
    '<time zone>'

<time zone> ::=
    <time zone region> |
    [+/-] <hour displacement> [: <minute displacement>]

Convertit l’heure ou l’horodateur dans le fuseau horaire spécifié.Si le mot-clé LOCAL est utilisé, la conversion se fait vers le fuseau horaire de la session.

Example 1. Utilisation de la fonction AT

select time '12:00 GMT' at time zone '-03'
  from rdb$database;

select current_timestamp at time zone 'America/Sao_Paulo'
  from rdb$database;

select timestamp '2018-01-01 12:00 GMT' at local
  from rdb$database;

`NEXT VALUE FOR`

Disponible enDSQL, PSQL.

Syntaxe

NEXT VALUE FOR sequence-name

Renvoie la valeur suivante dans la séquence (SEQUENCE). SEQUENCE est un terme générateur compatible avec SQL dans InterBase et Firebird.L’opérateur NEXT VALUE FOR est totalement équivalent à la fonction GEN_ID (sequence-name, 1) et constitue la syntaxe recommandée.

Note	La phrase `NEXT VALUE FOR` ne prend pas en charge une valeur différente de celle spécifiée lorsque la séquence a été créée dans la phrase `INCREMENT [BY]`. Si une valeur différente est requise, utilisez l’ancienne fonction `GEN_ID`.

Example 1. Utilisation de NEXT VALUE FOR

NEW.CUST_ID = NEXT VALUE FOR CUSTSEQ;

Voir aussi :

SEQUENCE (GENERATOR), GEN_ID.

Expressions conditionnelles

Une expression conditionnelle est une expression qui renvoie différentes valeurs en fonction de la véracité d’une ou plusieurs conditions.Cette section ne décrit qu’une seule expression conditionnelle CASE.Les autres expressions conditionnelles sont des fonctions intégrées dérivées et sont décrites dans Fonctions conditionnelles.

CASE

Disponible en

DSQL, ESQL.

L’opérateur CASE ne renvoie qu’une seule valeur parmi plusieurs possibles.Il existe deux variantes de syntaxe :

Un simple CASE, comparable à Pascal case ou C switch ;
Une CASE interrogeable qui fonctionne comme une série d’opérateurs if … sinon si … sinon si .

Simple CASE

Syntaxe

CASE <test-expr>
  WHEN <expr> THEN <result>
  [WHEN <expr> THEN <result> ...]
  [ELSE <defaultresult>]
END

Avec cette variante, la test-expr est comparée à la première expr,puis le deuxième expr, et ainsi de suite,jusqu’à ce qu’une correspondance soit trouvée, auquel cas le résultat correspondant est renvoyé.Si aucune correspondance n’est trouvée, le résultat par défaut de la branche ELSE est retourné.S’il n’y a pas de correspondance et que la branche ELSE est manquante, il retourne NULL.

Une correspondance est équivalente à l’opérateur “=”, c’est-à-dire queSi test-expr a la valeur NULL, alors il ne correspond à aucune des expr, même celles qui ont la valeur NULL.

Les résultats ne doivent pas nécessairement être des valeurs littérales, ils peuvent aussi être des noms de champs, des variables, des expressions complexes ou des NULL.

Example 1. En utilisant un simple CASE

SELECT
  NAME,
  AGE,
  CASE UPPER(SEX)
    WHEN 'M' THEN 'Male'
    WHEN 'F' THEN 'Female'
    ELSE 'Unknown'
  END AS SEXNAME,
  RELIGION
FROM PEOPLE

Une forme abrégée de l’opérateur CASE simple est utilisée dans la fonction DECODE.

Recherche CASE

Syntaxe

CASE
  WHEN <bool_expr> THEN <result>
  [WHEN <bool_expr> THEN <result> …]
  [ELSE <defaultresult>]
END

Ici <bool_expr> est une expression qui donne un triple résultat logique : TRUE, FALSE ou NULL.La première expression qui retourne TRUE définit le résultat.Si aucune expression ne retourne TRUE, le résultat par défaut de la branche ELSE est pris comme résultat.Si aucune expression ne retourne TRUE et qu’il n’y a pas de branche ELSE, le résultat est NULL.

Comme avec l’instruction simple CASE, les résultats ne doivent pas nécessairement être des valeurs littérales : ils peuvent être des champs ou des noms de variables, des expressions complexes ou NULL.

Example 1. Utilisation d’un moteur de recherche CASE

CANVOTE = CASE
  WHEN AGE >= 18 THEN 'Yes'
  WHEN AGE < 18 THEN 'No'
  ELSE 'Unsure'
END;

En termes de `NULL`

NULL n’est pas une valeur — c’est un état indiquant que la valeur de l’élément est inconnue ou n’existe pas.Ce n’est pas zéro, ni void, ni `chaîne vide', et il ne se comporte pas comme l’une de ces valeurs.

Lorsque vous utilisez NULL dans des expressions numériques, de chaîne de caractères ou de date/heure, vous obtenez toujours NULL.Lorsque vous utilisez NULL dans des expressions logiques (booléennes), le résultat dépendra du type d’opération et des autres valeurs impliquées.Si vous comparez une valeur à NULL, le résultat sera indéfini (UNKNOWN).

Important

Le résultat logique indéfini UNKNOWN est également représenté par la pseudo-valeur NULL.

Les expressions qui reviennent `NULL`

Les expressions de cette liste retourneront toujours NULL:

1 + 2 + 3 + NULL
'Home ' || 'sweet ' || NULL
MyField = NULL
MyField <> NULL
NULL = NULL
not (NULL)

Si vous avez du mal à comprendre pourquoi, rappelez-vous que NULL signifie inconnu.

`NULL` dans les expressions logiques

Nous avons déjà considéré que not (NULL) résulte en NULL.Pour les opérateurs AND et OR, l’interaction est un peu plus compliquée :

NULL or false → NULL
NULL or true → true
NULL or NULL → NULL
NULL and false → false
NULL and true → NULL
NULL and NULL → NULL

Example 1. NULL et les expressions logiques

(1 = NULL) or (1 <> 1)    -- returns NULL
(1 = NULL) or FALSE       -- returns NULL
(1 = NULL) or (1 = 1)     -- returns TRUE
(1 = NULL) or TRUE        -- returns TRUE
(1 = NULL) or (1 = NULL)  -- returns NULL
(1 = NULL) or UNKNOWN     -- returns NULL
(1 = NULL) and (1 <> 1)   -- returns FALSE
(1 = NULL) and FALSE      -- returns FALSE
(1 = NULL) and (1 = 1)    -- returns NULL
(1 = NULL) and TRUE       -- returns NULL
(1 = NULL) and (1 = NULL) -- returns NULL
(1 = NULL) and UNKNOWN    -- returns NULL

Sous-requêtes

Une sous-requête est un type particulier d’expression qui est en fait une requête intégrée dans une autre requête.Les sous-requêtes sont écrites comme des requêtes SELECT normales, mais elles doivent être mises entre parenthèses.Les expressions de sous-requêtes sont utilisées de la manière suivante :

Pour spécifier une colonne de sortie dans une liste de sélection SELECT ;
Pour obtenir des valeurs ou des conditions pour les prédicats de recherche (phrases WHERE, HAVING) ;
Pour créer un ensemble de données à partir duquel la requête d’inclusion peut sélectionner comme s’il s’agissait d’une table ou d’une vue ordinaire.Ces sous-requêtes apparaissent dans la phrase FROM (tables dérivées) ou dans une expression de table générique (CTE).

Sous-requêtes liées

Une sous-requête peut être liée (corrélée). Une requête est dite corrélée lorsque la sous-requête et la requête principale sont interdépendantes.Cela signifie que pour chaque enregistrement de la sous-requête à traiter, un enregistrement de la requête principale doit également être récupéré, c’est-à-dire quela sous-requête est entièrement dépendante de la requête principale.

Example 1. Sous-requête liée

SELECT *
FROM Customers C
WHERE EXISTS
      (SELECT *
       FROM Orders O
       WHERE C.cnum = O.cnum
         AND O.adate = DATE '10.03.1990');

Lorsque vous utilisez des sous-requêtes pour récupérer les valeurs des colonnes de sortie dans une liste de sélection SELECT, la sous-requête doit retourner un résultat scalaire.

Sous-requêtes retournant un résultat scalaire

Les Sous-requêtes utilisées dans les prédicats de recherche, à l’exception des prédicats d’existence et de quantité, doivent retourner un résultat scalaire, c’est-à-dire pas plus d’une colonne d’une seule ligne sélectionnée ou une valeur agrégée, sinon une erreur d’exécution (“Multiple rows in a singleton select…”) se produira.

Note

Bien que Firebird signale une erreur réelle, le message peut être légèrement trompeur.“singleton SELECT” est une requête qui ne doit pas retourner plus d’une chaîne.Cependant, singleton et scalar ne sont pas synonymes : tous les SELECTS à un seul élément ne doivent pas être scalaires ; et un échantillonnage à une seule colonne peut retourner plusieurs lignes pour les prédicats d’existence et de quantité.

Example 1. Sous-requête comme colonne de sortie dans la liste de sélection

SELECT
    e.first_name,
    e.last_name,
    (SELECT
         sh.new_salary
     FROM
         salary_history sh
     WHERE
         sh.emp_no = e.emp_no
     ORDER BY sh.change_date DESC ROWS 1) AS last_salary
FROM
    employee e

Example 2. Sous-requête dans une instruction WHERE pour obtenir la valeur du salaire maximum de l’employé et filtrer par celle-ci.

SELECT
    e.first_name,
    e.last_name,
    e.salary
FROM
    employee e
WHERE
    e.salary = (SELECT
                    MAX(ie.salary)
                FROM
                    employee ie)

Prédicats

Prédicat — est une simple expression affirmant un certain fait, appelons-le P.Si P est résolu comme VRAI, il réussit.S’il prend FALSE ou NULL (UNKNOWN), il échoue.Cependant, il y a un piège ici : supposons que le prédicat P renvoie FALSE.Dans ce cas, NOT (P) retournera VRAI.D’autre part, si P renvoie NULL (inconnu), alors NOT (P) renvoie également NULL.

En SQL, les prédicats sont vérifiés dans la contrainte CHECK, la clause WHERE, la clause CASE, la condition de jointure dans la phrase ON pour les phrases JOIN, et dans la clause HAVING.Dans PSQL, les opérateurs de contrôle du flux d’exécution vérifient les prédicats dans les phrases IF, WHILE et WHEN. Depuis que Firebird 3.0 a introduit le support des types logiques, le prédicat peut apparaître dans n’importe quelle expression valide.

Imbrications

Les conditions vérifiables ne sont pas toujours des prédicats simples.Il peut s’agir d’un groupe de prédicats, dont chacun, lorsqu’il est calculé, contribue au calcul de la vérité globale.Ces conditions complexes sont appelées assertions.Les assertions peuvent être constituées d’un ou plusieurs prédicats liés par les opérateurs logiques AND, OR et NOT. Les parenthèses peuvent être utilisées pour regrouper des prédicats et contrôler l’ordre des calculs.

Chacun des prédicats peut contenir des prédicats imbriqués.Le résultat du calcul de la vérité d’une déclaration est obtenu en calculant tous les prédicats dans la direction de l’interne vers l’externe.Chaque "niveau" est calculé par ordre de priorité jusqu’à obtenir la valeur de vérité de l’énoncé final.

Prédicats de comparaison

Un prédicat de comparaison est constitué de deux expressions jointes par un opérateur de comparaison.Il existe six opérateurs de comparaison traditionnels :

=, >, <, >=, <=, <>

(Voir Opérateurs de comparaison pour une liste complète des opérateurs de comparaison).

Si une partie (gauche ou droite) d’un prédicat de comparaison contient NULL, la valeur du prédicat est indéfinie (UNKNOWN).

Example 1. Prédicats de comparaison

Obtenez des informations sur les ordinateurs dotés d’un processeur d’une fréquence d’au moins 500 MHz et d’un prix inférieur à 800 €.

SELECT *
FROM Pc
WHERE speed >= 500 AND price < 800;

Obtenez des informations sur toutes les imprimantes qui sont des imprimantes à matrice de points et qui coûtent moins de 300 €.

SELECT *
FROM Printer
WHERE type = 'matrix' AND price < 300;

La requête suivante ne retournera aucune entrée car la comparaison est faite avec une pseudo-valeur de NULL, même s’il existe des imprimantes avec un type non spécifié.

SELECT *
FROM Printer
WHERE type = NULL AND price < 300;

Note	Note sur les comparaisons de chaînes de caractères Lors de la comparaison de l’égalité des types de champs `CHAR` et `VARCHAR`, les espaces de fin sont ignorés dans tous les cas.

Autres prédicats de comparaison

Les autres prédicats de comparaison sont constitués de mots-clés.

`BETWEEN`

Disponible en

DSQL, PSQL, ESQL.

Syntaxe

<value> [NOT] BETWEEN <value_1> AND <value_2>

Le prédicat BETWEEN vérifie si une valeur se situe (ou ne se situe pas en utilisant NOT) dans une plage de valeurs inclusive.

Les opérandes du prédicat BETWEEN sont deux arguments de types compatibles.Contrairement à d’autres bases de données, dans Firebird le prédicat BETWEEN n’est pas symétrique.La plus petite valeur doit être le premier argument, sinon le prédicat BETWEEN sera toujours faux.La recherche est une recherche inclusive.Ainsi, le prédicat BETWEEN peut être réécrit comme suit :

<value> >= <value_1> AND <value> <= <value_2>

Lors de l’utilisation du prédicat BETWEEN dans les termes de recherche des requêtes DML, l’optimiseur Firebird peut utiliser un index sur la colonne recherchée, si disponible.

Example 1. Utilisation d’un prédicat BETWEEN

SELECT *
FROM EMPLOYEE
WHERE HIRE_DATE BETWEEN date '01.01.1992' AND CURRENT_DATE

`LIKE`

Disponible en

DSQL, PSQL, ESQL.

Syntaxe

<match value> [NOT] LIKE <pattern>
  [ESCAPE <escape character>]

<match value> ::=  expression de type caractère
<pattern> ::= modèle de recherche
<escape character> ::= caractère d’échappement

Le prédicat LIKE compare une expression de type caractère avec le motif défini dans la seconde expression.La sensibilité à la casse ou aux signes diacritiques dans la comparaison est déterminée par le paramètre de tri utilisé (COLLATION).

Lorsque vous utilisez l’opérateur LIKE, tous les caractères de la chaîne de caractères du motif sont pris en compte.Cela s’applique également aux espaces avant et arrière.Si une opération de correspondance dans une requête doit renvoyer toutes les chaînes contenant des chaînes LIKE 'abv' (avec un caractère d’espacement à la fin), une chaîne contenant 'abv' (sans caractère d’échappement) ne sera pas renvoyée.

Caractères génériques

Deux symboles génériques peuvent être utilisés dans le modèle de recherche :

le caractère pourcentage (%) remplace une séquence de caractères quelconques (le nombre de caractères de la séquence peut être égal ou supérieur à 0) dans la valeur à vérifier ;
Un caractère de soulignement (_), qui peut être utilisé à la place de n’importe quel caractère dans la valeur testée.

Si la valeur testée correspond au modèle, y compris les caractères génériques, le prédicat est vrai.

Utiliser un caractère d’échappement dans une phrase `ESCAPE`

Si la chaîne recherchée contient un caractère générique, un caractère de contrôle doit être spécifié dans la clause ESCAPE.Ce caractère de contrôle doit être utilisé dans le motif avant le caractère générique, indiquant que ce dernier doit être traité comme un caractère normal.

Exemples d’utilisation du prédicat `LIKE`

Example 1. Recherche de chaînes de caractères qui commencent par une sous-chaîne donnée à l’aide d’un prédicat. LIKE

Recherchez les numéros de département qui commencent par le mot “Software”

SELECT DEPT_NO
FROM DEPT
WHERE DEPT_NAME LIKE 'Software%';

Un index peut être utilisé dans cette requête s’il est construit sur le champ DEPT_NAME.

Note

Optimisation LIKE

En général, le prédicat LIKE n’utilise pas d’index.Cependant, si le prédicat prend la forme LIKE 'string%', il sera converti en un prédicat STARTING WITH qui utilisera un index.Si vous devez effectuer une recherche à partir du début d’une chaîne, il est recommandé d’utiliser le prédicat STARTING WITH au lieu du prédicat LIKE. STARTING WITH.

Example 2. Utilisation du caractère générique “_” dans un prédicat LIKE

Recherchez les employés dont le nom comporte 5 lettres commençant par “Sm” et finissant par “th”.Dans ce cas, le prédicat sera vrai pour les noms “Smith” et “Smyth”.

SELECT
    first_name
FROM
    employee
WHERE first_name LIKE 'Sm_th'

Example 3. Recherche dans une chaîne de caractères à l’aide d’un prédicat LIKE

Recherche de tous les clients dont l’adresse contient la chaîne “Rostov”.

SELECT *
FROM CUSTOMER
WHERE ADDRESS LIKE '%Rostov%'

Tip	Si vous devez effectuer une recherche à l’intérieur d’une chaîne, nous vous recommandons d’utiliser le prédicat `CONTAINING` au lieu du prédicat `LIKE`. CONTAINING.

Utilisation du caractère de contrôle dans une phrase ESCAPE avec un prédicat LIKE.

Recherche les tables qui contiennent un caractère de soulignement dans leur nom.Dans ce cas, le caractère de contrôle est “#”.

SELECT
  RDB$RELATION_NAME
FROM RDB$RELATIONS
WHERE RDB$RELATION_NAME LIKE '%#_%' ESCAPE '#'

Voir aussi :

STARTING WITH, CONTAINING, SIMILAR TO.

`STARTING WITH`

Disponible en

DSQL, PSQL, ESQL.

Syntaxe

<value> [NOT] STARTING WITH <start-value>

Le prédicat STARTING WITH recherche une chaîne qui commence par les caractères de son argument start-value.La sensibilité à la casse et aux accents de STARTING WITH dépend du tri (COLLATION) du premier argument value.

Lorsque vous utilisez le prédicat STARTING WITH dans les termes de recherche des requêtes DML, l’optimiseur de Firebird peut utiliser un index sur la colonne recherchée s’il est défini.

Example 1. Utilisation d’un prédicat STARTING WITH

Recherchez les employés dont le nom de famille commence par "Jo".

SELECT LAST_NAME, FIRST_NAME
FROM EMPLOYEE
WHERE LAST_NAME STARTING WITH 'Jo'

Voir aussi :

LIKE.

`CONTAINING`

Disponible en

DSQL, PSQL, ESQL.

Syntaxe

<value> [NOT] CONTAINING <substring>

L’opérateur CONTAINING recherche une chaîne ou un type de chaîne en trouvant une séquence de caractères qui correspond à son argument.Il peut être utilisé pour des recherches alphanumériques (sous forme de chaîne de caractères) dans des nombres et des dates.Les recherches de type CONTAINING ne sont pas sensibles à la casse.Toutefois, si un tri sensible aux accents est utilisé, la recherche sera sensible aux accents.

Lorsque l’on utilise l’opérateur CONTAINING, tous les caractères d’une chaîne sont pris en compte.Cela s’applique également aux espaces avant et arrière.Si l’opération de comparaison dans une requête doit retourner toutes les chaînes contenant des chaînes CONTAINING 'abv' (avec un caractère d’espacement à la fin), une chaîne contenant 'abv' (sans caractère d’espacement) ne sera pas retournée.

Lors de l’utilisation du prédicat CONTAINING dans les termes de recherche des requêtes DML, l’optimiseur Firebird ne peut pas utiliser un index sur la colonne recherchée.

Recherche de sous-chaînes à l’aide du prédicat CONTAINING.

Recherchez les projets qui contiennent la chaîne “Map” dans leur nom :

SELECT *
FROM PROJECT
WHERE PROJ_NAME CONTAINING 'map';

Dans ce cas, deux lignes avec les noms “AutoMap” et “MapBrowser port” seront retournées.

Example 1. Recherche dans une date à l’aide du prédicat CONTAINING.

Une recherche d’enregistrements de modifications salariales dont la date contient le chiffre 84 (dans ce cas, les modifications qui ont eu lieu en 1984) :

SELECT *
FROM SALARY_HISTORY
WHERE CHANGE_DATE CONTAINING 84;

Voir aussi :

LIKE.

`SIMILAR TO`

Disponible en

DSQL, PSQL.

Syntaxe

string-expression [NOT] SIMILAR TO <pattern> [ESCAPE <escape-char>]

<pattern> ::= Expression régulière SQL
<escape-char> ::= symbole d'échappement

L’instruction SIMILAR TO vérifie si une chaîne de caractères correspond à un modèle d’expression régulière SQL.Contrairement à d’autres langages, le modèle doit correspondre à la totalité de la chaîne de caractères pour être exécuté avec succès - la correspondance d’une sous-chaîne ne suffit pas.Si l’un des opérandes est NULL, le résultat est NULL.Sinon, le résultat est TRUE ou FALSE.

Syntaxe des expressions régulières SQL

La syntaxe suivante définit le format d’une expression SQL régulière.Il s’agit d’une définition complète et correcte.Il est très formel et plutôt long et sera probablement déroutant pour ceux qui n’ont aucune expérience des expressions régulières.N’hésitez pas à la sauter et à commencer à lire la section suivante, Création d’expressions régulières qui utilise une approche simple à complexe.

<regular expression> ::= <regular term> ['|' <regular term> ...]

<regular term> ::= <regular factor> ...

<regular factor> ::= <regular primary> [<quantifier>]

<quantifier> ::= ? | * | + | '{' <m> [,[<n>]] '}'

<m>, <n> ::= des nombres positifs entiers, si les deux nombres sont présents, alors <m> <= <n>

<regular primary> ::=
    <character> | <character class> | %
  | (<regular expression>)

<character> ::= <escaped character> | <non-escaped character>

<escaped character> ::=
  <escape-char> <special character> | <escape-char> <escape-char>

<special character> ::= l'un des symboles []()|^-+*%_?{}

<non-escaped character> ::=
  tout caractère sauf <special character>
  et non équivalent <escape-char> (si c'est le cas)

<character class> ::=
    '_' | '[' <member> ... ']' | '[^' <non-member> ... ']'
  | '[' <member> ... '^' <non-member> ... ']'

<member>, <non-member> ::= <character> | <range> | <predefined class>

<range> ::= <character>-<character>

<predefined class> ::= '[:' <predefined class name> ':]'

<predefined class name> ::=
  ALPHA | UPPER | LOWER | DIGIT | ALNUM | SPACE | WHITESPACE

Création d’expressions régulières

Cette section présente les éléments et les règles de construction des expressions régulières SQL.

Symboles

Dans les expressions régulières, la plupart des caractères se représentent eux-mêmes, à l’exception des caractères spéciaux (special character):

[ ] ( ) | ^ - + * % _ ? { }

... et les symboles de contrôle (escaped character), s’ils sont définis.

Une expression régulière ne contenant aucun caractère spécial ou de contrôle ne correspond qu’à des chaînes de caractères totalement identiques (selon le tri utilisé). C’est-à-dire qu’il fonctionne exactement de la même manière que l’opérateur “=”:

'Apple' SIMILAR TO 'Apple' -- TRUE
'Apples' SIMILAR TO 'Apple' -- FALSE
'Apple' SIMILAR TO 'Apples' -- FALSE
'APPLE' SIMILAR TO 'Apple' -- en fonction du tri

Modèles

Les séquences SQL connues ‘_’ et ‘%’ correspondent respectivement à un caractère unique et à une chaîne de caractères de longueur quelconque :

'Birne' SIMILAR TO 'B_rne' -- TRUE
'Birne' SIMILAR TO 'B_ne' -- FALSE
'Birne' SIMILAR TO 'B%ne' -- TRUE
'Birne' SIMILAR TO 'Bir%ne%' -- TRUE
'Birne' SIMILAR TO 'Birr%ne' -- FALSE

Notez que le caractère'`%’ correspond également à une chaîne vide.

Classes de caractères

Un ensemble de caractères entre crochets définit une classe de caractères.Le caractère de la chaîne correspond à la classe du motif si le caractère est un élément de la classe :

'Citroen' SIMILAR TO 'Cit[arju]oen' -- TRUE
'Citroen' SIMILAR TO 'Ci[tr]oen' -- FALSE
'Citroen' SIMILAR TO 'Ci[tr][tr]oen' -- TRUE

Comme vous pouvez le voir sur la deuxième ligne, la classe ne comporte qu’un seul caractère, et non une séquence de caractères.

Deux caractères reliés par un trait d’union dans la définition de la classe définissent une plage.La plage pour le mappage actif comprend ces deux caractères d’extrémité et tous les caractères intermédiaires.Les plages peuvent être placées n’importe où dans la définition de la classe sans qu’il soit nécessaire d’utiliser des délimiteurs spéciaux pour garder les autres caractères dans la classe également.

'Datte' SIMILAR TO 'Dat[q-u]e' -- TRUE
'Datte' SIMILAR TO 'Dat[abq-uy]e' -- TRUE
'Datte' SIMILAR TO 'Dat[bcg-km-pwz]e' -- FALSE

Classes de caractères prédéfinies

Les classes de caractères prédéfinies suivantes peuvent également être utilisées dans la définition de la classe :

[:ALPHA:]: Lettres latines a…z et A…Z.Cette classe comprend également les caractères avec des signes diacritiques dans les tri insensibles aux accents.
[:DIGIT:]: Chiffres décimaux 0…9.
[:ALNUM:]: Association [:ALPHA:] et [:DIGIT:].
[:UPPER:]: Capitales (majuscules) Lettres latines A…Z.Inclut également les caractères minuscules dans le tri insensible à la casse et les signes diacritiques dans le tri insensible à l’accent.
[:LOWER:]: Les lettres latines a…z (minuscules).Inclut également les caractères majuscules dans le tri insensible à la casse et les signes diacritiques dans le tri insensible à l’accent.
[:SPACE:]: Symbole d’espace (ASCII 32).
[:WHITESPACE:]: Tabulation horizontale (ASCII 9), saut de ligne (ASCII 10), tabulation verticale (ASCII 11), saut de page (ASCII 12), retour chariot (ASCII 13) et espace (ASCII 32).

Inclure une classe prédéfinie dans une déclaration SIMILAR TO a le même effet que d’inclure tous ses éléments.L’utilisation de classes prédéfinies n’est autorisée que dans la définition de la classe.Si vous n’avez besoin que d’un mappage vers une classe prédéfinie et rien d’autre, mettez une paire de parenthèses supplémentaire autour.

'Erdbeere' SIMILAR TO 'Erd[[:ALNUM:]]eere' -- TRUE
'Erdbeere' SIMILAR TO 'Erd[[:DIGIT:]]eere' -- FALSE
'Erdbeere' SIMILAR TO 'Erd[a[:SPACE:]b]eere' -- TRUE
'Erdbeere' SIMILAR TO '[[:ALPHA:]]' -- FALSE
'E' SIMILAR TO '[[:ALPHA:]]' -- TRUE

Si une définition de classe commence par un caractère d’insertion (^), tout ce qui le suit est exclu de la classe.Tous les autres caractères sont vérifiés.

'Framboise' SIMILAR TO 'Fra[^ck-p]boise' -- FALSE
'Framboise' SIMILAR TO 'Fr[^a][^a]boise' -- FALSE
'Framboise' SIMILAR TO 'Fra[^[:DIGIT:]]boise' -- TRUE

Si le caractère d’insertion (^) n’est pas au début de la séquence, la classe inclut tous les caractères qui le précèdent et exclut les caractères qui le suivent.

'Grapefruit' SIMILAR TO 'Grap[a-m^f-i]fruit' -- TRUE
'Grapefruit' SIMILAR TO 'Grap[abc^xyz]fruit' -- FALSE
'Grapefruit' SIMILAR TO 'Grap[abc^de]fruit' -- FALSE
'Grapefruit' SIMILAR TO 'Grap[abe^de]fruit' -- FALSE
'3' SIMILAR TO '[[:DIGIT:]^4-8]' -- TRUE
'6' SIMILAR TO '[[:DIGIT:]^4-8]' -- FALSE

Enfin, le caractère générique ‘_’ déjà mentionné est une classe de caractères à part entière, correspondant à n’importe quel caractère.

Quantificateurs

Un point d’interrogation ('``?'') immédiatement après un caractère ou une classe indique que l’élément précédent doit apparaître 0 ou 1 fois pour être apparié :

'Hallon' SIMILAR TO 'Hal?on' -- FALSE
'Hallon' SIMILAR TO 'Hal?lon' -- TRUE
'Hallon' SIMILAR TO 'Halll?on' -- TRUE
'Hallon' SIMILAR TO 'Hallll?on' -- FALSE
'Hallon' SIMILAR TO 'Halx?lon' -- TRUE
'Hallon' SIMILAR TO 'H[a-c]?llon[x-z]?' -- TRUE

Un astérisque (‘*’) immédiatement après un caractère ou une classe indique que l’élément précédent doit apparaître 0 fois ou plus pour que la correspondance soit établie :

'Icaque' SIMILAR TO 'Ica*que' -- TRUE
'Icaque' SIMILAR TO 'Icar*que' -- TRUE
'Icaque' SIMILAR TO 'I[a-c]*que' -- TRUE
'Icaque' SIMILAR TO '_*' -- TRUE
'Icaque' SIMILAR TO '[[:ALPHA:]]*' -- TRUE
'Icaque' SIMILAR TO 'Ica[xyz]*e' -- FALSE

Un signe plus (‘+’) immédiatement après un caractère ou une classe indique que l’élément précédent doit apparaître 1 fois ou plus pour que la correspondance soit établie :

'Jujube' SIMILAR TO 'Ju_+' -- TRUE
'Jujube' SIMILAR TO 'Ju+jube' -- TRUE
'Jujube' SIMILAR TO 'Jujuber+' -- FALSE
'Jujube' SIMILAR TO 'J[jux]+be' -- TRUE
'Jujube' SIMILAR TO 'J[[:DIGIT:]]+ujube' -- FALSE

Si un caractère ou une classe est accompagné(e) d’un nombre entre crochets (‘{’ et ‘}’), l’élément doit être répété exactement ce nombre de fois pour être apparié :

'Kiwi' SIMILAR TO 'Ki{2}wi' -- FALSE
'Kiwi' SIMILAR TO 'K[ipw]{2}i' -- TRUE
'Kiwi' SIMILAR TO 'K[ipw]{2}' -- FALSE
'Kiwi' SIMILAR TO 'K[ipw]{3}' -- TRUE

Si un nombre est suivi d’une virgule (‘,’), l’élément doit être répété au moins ce nombre de fois pour être mis en correspondance :

'Limone' SIMILAR TO 'Li{2,}mone' -- FALSE
'Limone' SIMILAR TO 'Li{1,}mone' -- TRUE
'Limone' SIMILAR TO 'Li[nezom]{2,}' -- TRUE

Si les accolades contiennent deux nombres (m et n) séparés par une virgule et que le deuxième nombre est supérieur au premier, l’élément doit être répété au moins m fois et pas plus de n fois pour correspondre :

'Mandarijn' SIMILAR TO 'M[a-p]{2,5}rijn' -- TRUE
'Mandarijn' SIMILAR TO 'M[a-p]{2,3}rijn' -- FALSE
'Mandarijn' SIMILAR TO 'M[a-p]{2,3}arijn' -- TRUE

Les quantors ‘ ? ’ , ‘*’ et ‘+’ sont des abréviations de {0,1}, {0,} et {1,} respectivement.

Le terme OR

Dans les conditions d’expression régulière, vous pouvez utiliser l’opérateur OR ‘|’. Une correspondance a eu lieu si la chaîne de paramètres correspond à au moins une des conditions :

'Nektarin' SIMILAR TO 'Nek|tarin' -- FALSE
'Nektarin' SIMILAR TO 'Nektarin|Persika' -- TRUE
'Nektarin' SIMILAR TO 'M_+|N_+|P_+' -- TRUE

Sous-expressions

Une ou plusieurs parties d’une expression régulière peuvent être regroupées en sous-expressions (également appelées sous-masques). Pour ce faire, mettez-les entre parenthèses. (‘(’ et ‘)’):

'Orange' SIMILAR TO 'O(ra|ri|ro)nge' -- TRUE
'Orange' SIMILAR TO 'O(r[a-e])+nge' -- TRUE
'Orange' SIMILAR TO 'O(ra){2,4}nge' -- FALSE
'Orange' SIMILAR TO 'O(r(an|in)g|rong)?e' -- TRUE

Caractère spécial d’échappement

Pour exclure les caractères spéciaux (que l’on trouve souvent dans les expressions régulières) du processus de correspondance, ils doivent être échappés.Il n’y a pas de caractères d’échappement spéciaux par défaut — ils sont définis par l’utilisateur si nécessaire :

'Peer (Poire)' SIMILAR TO 'P[^ ]+ \(P[^ ]+\)' ESCAPE '\' -- TRUE
'Pera [Pear]' SIMILAR TO 'P[^ ]+ #[P[^ ]+#]' ESCAPE '#' -- TRUE
'Paron-Appledryck' SIMILAR TO 'P%$-A%' ESCAPE '$' -- TRUE
'Parondryck' SIMILAR TO 'P%--A%' ESCAPE '-' -- FALSE

`IS DISTINCT FROM`

Disponible en

DSQL, PSQL.

Syntaxe

<operand1> IS [NOT] DISTINCT FROM <operand2>

Deux opérandes sont considérés comme DISTINCT (différents) s’ils ont des valeurs différentes, ou si l’un d’eux est NULL et l’autre non.Elles sont considérées comme NOT DISTINCT (égales) si elles ont les mêmes valeurs ou si elles sont toutes deux NULL.

L’option IS [NOT] DISTINCT FROM renvoie toujours VRAI ou FAUX et jamais UNKNOWN (NULL) (valeur inconnue). En revanche, les opérateurs “=” et “<>” renvoient UNKNOWN (NULL) si l’un ou les deux opérandes sont NULL.

Table 1. Les résultats des différents opérateurs de comparaison
Caractéristiques de l’opérande	Les résultats des différents opérateurs
Caractéristiques de l’opérande	`=`	`IS NOT DISTINCT FROM`	`<>`	`IS DISTINCT FROM`
Mêmes valeurs	`TRUE`	`TRUE`	`FALSE`	`FALSE`
Valeurs différentes	`FALSE`	`FALSE`	`TRUE`	`TRUE`
les deux `NULL`	`UNKNOWN`	`TRUE`	`UNKNOWN`	`FALSE`
un `NULL` et l’autre non `NULL`	`UNKNOWN`	`FALSE`	`UNKNOWN`	`TRUE`

Example 1. Utilisation d’un prédicat IS [NOT] DISTINCT FROM

SELECT ID, NAME, TEACHER
FROM COURSES
WHERE START_DAY IS NOT DISTINCT FROM END_DAY

IF (NEW.JOB IS DISTINCT FROM OLD.JOB) THEN
  POST_EVENT 'JOB_CHANGED';

Voir aussi :

[fblangref-commons-isnotboolean], [fblangref-commons-isnotnull].

Logique `IS [NOT]`

Disponible en

DSQL, PSQL.

Syntaxe

<value> IS [NOT] {TRUE | FALSE | UNKNOWN}

L’opérateur IS vérifie que l’expression du côté gauche correspond à la valeur logique du côté droit.L’expression du côté gauche doit être de type logique, sinon une erreur sera lancée.

Pour un type de données logique, le prédicat IS [NOT] UNKNOWN est équivalent à IS [NOT] NULL.

Note	Note: Seuls les littéraux `TRUE`, `FALSE`, `UNKNOWN` peuvent être utilisés dans le côté droit du prédicat, mais pas les expressions.

Example 1. Utilisation de l’opérateur IS avec un type de données logique

-- Vérifier la valeur FALSE
SELECT * FROM TBOOL WHERE BVAL IS FALSE

ID           BVAL
============ =======
2            <false>

-- Vérifier la valeur UNKNOWN
SELECT * FROM TBOOL WHERE BVAL IS UNKNOWN

ID           BVAL
============ =======
3            <null>

`IS [NOT] NULL`

Disponible en

DSQL, PSQL.

Syntaxe

<value> IS [NOT] NULL

Comme NULL n’est pas une valeur, ces opérateurs ne sont pas des opérateurs de comparaison.L’opérateur IS [NOT] NULL vérifie que l’expression du côté gauche a une valeur (IS NOT NULL) ou n’a pas de valeur. (IS NULL)

Example 1. Utilisation d’un prédicat IS [NOT] NULL

Recherche des enregistrements de vente pour lesquels aucune date d’expédition n’a été définie :

SELECT *
FROM SALES
WHERE SHIP_DATE IS NULL;

Prédicats d’existence

Ce groupe de prédicats comprend des prédicats qui utilisent des sous-requêtes et passent des valeurs pour toutes sortes d’instructions dans les conditions de recherche.Les prédicats d’existence sont appelés ainsi parce qu’ils vérifient l’existence ou la non-existence des résultats des sous-requêtes de différentes manières.

`EXISTS`

Disponible en

DSQL, PSQL, ESQL.

Syntaxe

[NOT] EXISTS (<select_stmt>)

Le prédicat EXISTS utilise la sous-requête comme argument.Si le résultat de la sous-requête contient au moins une entrée, le prédicat est évalué comme vrai (TRUE), sinon le prédicat est évalué comme faux (FALSE).

Le résultat d’une sous-requête peut contenir plusieurs colonnes, puisque les valeurs ne sont pas vérifiées, mais que seul le fait qu’il y ait des lignes du résultat est enregistré.Ce prédicat ne peut prendre que deux valeurs : VRAI et FAUX.

Le prédicat NOT EXISTS renvoie FALSE si le résultat de la sous-requête contient au moins une entrée, sinon le prédicat renvoie TRUE.

Example 1. Prédicat EXISTS

Trouvez les employés qui ont des projets.

SELECT *
FROM employee
WHERE EXISTS (SELECT *
              FROM
                employee_project ep
              WHERE
                ep.emp_no = employee.emp_no)

Example 2. Prédicat NOT EXISTS

Trouvez les employés qui n’ont pas de projets.

SELECT *
FROM employee
WHERE NOT EXISTS (SELECT *
                  FROM
                    employee_project ep
                  WHERE
                    ep.emp_no = employee.emp_no)

`IN`

Disponible en

DSQL, PSQL, ESQL.

Syntaxe

<value> [NOT] IN (<select_stmt> | <value_list>)

<value_list> ::= <value_1> [, <value_2> ...]

Le prédicat IN vérifie si la valeur de l’expression à gauche est présente dans l’ensemble des valeurs spécifiées à droite.L’ensemble des valeurs ne peut pas dépasser 1500 éléments.Le prédicat IN peut être réécrit sous la forme équivalente suivante :

(<value> = <value_1> [OR <value> = <value_2> ...])

Lorsque vous utilisez le prédicat IN dans les termes de recherche des requêtes DML, l’optimiseur de Firebird peut utiliser un index sur la colonne recherchée s’il est défini.

Dans la deuxième forme, le prédicat IN vérifie si la valeur de l’expression de gauche est présente (ou absente, lorsqu’on utilise NOT IN) dans le résultat de la sous-requête de droite.Le résultat de la sous-requête ne peut contenir qu’une seule colonne, sinon une erreur sera émise.“count of column list and variable list do not match”.

Les requêtes utilisant le prédicat IN avec une sous-requête peuvent être réécrites en une requête similaire utilisant le prédicat EXISTS.Par exemple, la requête suivante :

SELECT
  model, speed, hd
FROM PC
WHERE
  model IN (SELECT model
            FROM product
            WHERE maker = 'A');

Vous pouvez réécrire une requête similaire en utilisant un prédicat. EXISTS:

SELECT
  model, speed, hd
FROM PC
WHERE
  EXISTS (SELECT *
          FROM product
          WHERE maker = 'A'
            AND product.model = PC.model);

Cependant, une requête utilisant NOT IN ne donnera pas toujours le même résultat qu’une requête NOT EXISTS.La raison en est que le prédicat EXISTS renvoie toujours TRUE ou FALSE, alors que le prédicat IN peut renvoyer NULL dans les cas suivants :

Lorsque la valeur à vérifier est NULL et que la liste dans IN n’est pas vide.
Lorsque la valeur à vérifier n’est pas une correspondance dans la liste IN et que l’une des valeurs est NULL.

Dans ces deux cas, le prédicat IN renverra NULL, tandis que le prédicat EXISTS correspondant renverra FALSE.Dans les conditions de recherche ou l’opérateur IF, les deux résultats indiquent `Fail' et sont traités de la même manière.

Cependant, sur les mêmes données, NOT IN retournera NULL, alors que EXISTS retournera TRUE, ce qui provoquera le résultat inverse.

C’est ce que montre l’exemple suivant.

Supposons que vous ayez une requête comme celle-ci :

-- Je cherche des gens qui ne sont pas nés le même jour que...
-- Des habitants célèbres de New York
SELECT P1.name AS NAME
FROM Personnel P1
WHERE P1.birthday NOT IN (SELECT C1.birthday
                          FROM Celebrities C1
                          WHERE C1.birthcity = 'New York');

On peut supposer qu’une requête utilisant un prédicat produira un résultat similaire NOT EXISTS:

-- Je cherche des gens qui ne sont pas nés le même jour que...
-- des habitants célèbres de New York
SELECT P1.name AS NAME
FROM Personnel P1
WHERE NOT EXISTS (SELECT *
                  FROM Celebrities C1
                  WHERE C1.birthcity = 'New York'
                    AND C1.birthday = P1.birthday);

Supposons qu’il n’y ait qu’un seul résident connu à New York et que sa date de naissance soit inconnue.Lorsque vous utilisez le prédicat EXISTS, la sous-requête qu’il contient ne produira pas de résultats car la comparaison des dates de naissance à NULL donnera UNKNOWN.Ainsi, le résultat du prédicat NOT EXISTS sera vrai pour chaque ligne de la requête principale.Alors que le résultat du prédicat NOT IN sera UNKNOWN et aucune ligne ne sera affichée.

Example 1. Prédicat IN

Trouver des employés avec des noms “Pete”, “Ann” et “Roger”:

SELECT *
FROM EMPLOYEE
WHERE FIRST_NAME IN ('Pete', 'Ann', 'Roger');

Example 2. Prédicat de recherche IN

Trouvez tous les ordinateurs pour lesquels il existe des modèles dont le fabricant commence par la lettre “A”:

SELECT
  model, speed, hd
FROM PC
WHERE
  model IN (SELECT model
            FROM product
            WHERE maker STARTING WITH 'A');

Voir aussi :

EXISTS.

`SINGULAR`

Disponible en

DSQL, PSQL, ESQL.

Syntaxe

[NOT] SINGULAR (<select_stmt>)

Le prédicat SINGULAR utilise la sous-requête comme argument et l’évalue comme vrai si la sous-requête retourne une et une seule ligne de résultat, sinon le prédicat est évalué comme faux.Le résultat de la sous-requête peut contenir plusieurs colonnes, puisque les valeurs ne sont pas vérifiées.Ce prédicat ne peut prendre que deux valeurs : vrai (TRUE) et faux (FALSE).

Example 1. Prédicat SINGULAR

Trouvez les employés qui n’ont qu’un seul projet.

SELECT *
FROM employee
WHERE SINGULAR (SELECT *
                FROM
                  employee_project ep
                WHERE
                  ep.emp_no = employee.emp_no)

Prédicats de sous-requêtes quantitatives

Un quantor est un opérateur logique qui spécifie le nombre d’objets pour lesquels une déclaration donnée est vraie.Il s’agit d’une quantité logique, et non d’une quantité numérique ; elle associe l’énoncé à l’ensemble des objets possibles.Ces prédicats sont basés sur les quantificateurs logiques formels de généralité et d’existence, qui sont reconnus par la logique formelle.

Dans les expressions de sous-requêtes, les prédicats quantitatifs permettent de comparer des valeurs individuelles aux résultats des sous-requêtes ; leur forme courante :

<value expression> <comparison operator> <quantifier> <subquery>

`ALL`

Disponible en

DSQL, PSQL.

Syntaxe

<value> <op> ALL (<select_stmt>)

En utilisant le quantificateur ALL, le prédicat est vrai si chaque valeur sélectionnée par la sous-requête satisfait à la condition du prédicat de la requête externe.Si la sous-requête ne renvoie aucune chaîne, le prédicat est automatiquement considéré comme vrai.

Example 1. Prédicat ALL

Ne faites resortir que les clients dont le score est supérieur à celui de chaque client de Paris.

SELECT *
FROM Customers
WHERE rating > ALL
      (SELECT rating
       FROM Customers
       WHERE city = 'Paris')

Important

Si la sous-requête renvoie un ensemble vide, alors le prédicat sera vrai pour chaque valeur gauchère, quel que soit l’opérateur.Cela peut sembler étrange et contradictoire, car dans ce cas, chaque valeur de la main gauche est traitée comme étant simultanément supérieure, inférieure, égale et inégale à toute valeur du courant de la main droite.

Cependant, ceci est normalement cohérent avec la logique formelle : si un ensemble est vide, alors le prédicat est vrai 0 fois, soitpour chaque ligne de l’ensemble.

`ANY` et `SOME`

Disponible en

DSQL, PSQL.

Syntaxe

<value> <op> {ANY | SOME} (<select_stmt>)

Ces deux quantificateurs ont un comportement identique.Apparemment, les deux sont introduits dans la norme SQL pour une utilisation interchangeable afin d’améliorer la lisibilité des déclarations.Lorsque le quantificateur ANY ou SOME est utilisé, le prédicat est vrai si l’une des valeurs sélectionnées par la sous-requête satisfait à la condition du prédicat de la requête externe.Si la sous-requête ne renvoie aucune chaîne, le prédicat est automatiquement considéré comme faux.

Example 1. Prédicat ANY

Ne faites resortir que les clients dont les scores sont supérieurs à ceux de tous les clients de Rome.

SELECT *
FROM Customers
WHERE rating > ANY
      (SELECT rating
       FROM Customers
       WHERE city = 'Rome')

AT

AT

NEXT VALUE FOR

Expressions conditionnelles

CASE

Simple CASE

Recherche CASE

En termes de NULL

Les expressions qui reviennent NULL

NULL dans les expressions logiques

Sous-requêtes

Sous-requêtes liées

Sous-requêtes retournant un résultat scalaire

Prédicats

Imbrications

Prédicats de comparaison

Autres prédicats de comparaison

BETWEEN

LIKE

Caractères génériques

Utiliser un caractère d’échappement dans une phrase ESCAPE

Exemples d’utilisation du prédicat LIKE

STARTING WITH

CONTAINING

SIMILAR TO

Syntaxe des expressions régulières SQL

Création d’expressions régulières

Symboles

Modèles

Classes de caractères

Classes de caractères prédéfinies

Quantificateurs

Le terme OR

Sous-expressions

Caractère spécial d’échappement

IS DISTINCT FROM

Logique IS [NOT]

IS [NOT] NULL

Prédicats d’existence

EXISTS

IN

SINGULAR

Prédicats de sous-requêtes quantitatives

ALL

ANY et SOME

`AT`

`NEXT VALUE FOR`

En termes de `NULL`

Les expressions qui reviennent `NULL`

`NULL` dans les expressions logiques

`BETWEEN`

`LIKE`

Utiliser un caractère d’échappement dans une phrase `ESCAPE`

Exemples d’utilisation du prédicat `LIKE`

`STARTING WITH`

`CONTAINING`

`SIMILAR TO`

`IS DISTINCT FROM`

Logique `IS [NOT]`

`IS [NOT] NULL`

`EXISTS`

`IN`

`SINGULAR`

`ALL`

`ANY` et `SOME`