Fonctions scalaires |
Les fonctions scalaires s´inscrivent dans les catégories répertoriées dans le Tableau 4.2.
Catégories de fonctions scalaires
Catégorie de fonction | Explication
--- | ---
Intégrée | Exécute des opérations sur les valeurs ou les paramètres intégrés dans la base de données.Oracle utilise le terme « intégré » pour décrire toutes les fonctions spécialisées qu´il propose, qui sont donc « intégrées » dans leur SGBD. Il s´agit d´un usage séparé et distinct des fonctions intégrées décrites ici.
Date et heure | Exécute des opérations sur les champs de date/heure et renvoie des valeurs au format datetime.
Numérique | Exécute des opérations sur des valeurs numériques et renvoie des valeurs numériques.
Chaîne | Exécute des opérations sur des valeurs de caractère (char, varchar, nchar, nvarchar et CLOB) et renvoie une chaîne ou une valeur numérique.
Remarque: CASE et CAST sont toutes les deux des fonctions. Elles sont cependant décrites en détail au chapitre 3 en raison de leur complexité et de leur utilisation fréquente dans les instructions de données SQL.

Fonctions scalaires intégrées
Les fonctions scalaires intégrées SQL99 identifient la session utilisateur en cours, ainsi que ses caractéristiques (par exemple, les privilèges de la session en cours). Les fonctions scalaires intégrées sont presque toujours non déterministiques. Les trois premières fonctions répertoriées dans le Tableau 4.3 sont des fonctions intégrées qui s´inscrivent dans la catégories date/time (date/heure) de fonctions. Les quatre éditeurs proposent diverses fonctions supplémentaires qui ne s´inscrivent pas dans le cadre des fonctions intégrées SQL, mais la norme SQL déclare uniquement celles répertoriées dans le Tableau 4.3.
Fonctions scalaires intégrées SQL99
Fonction | Usage
--- | ---
CURRENT_DATE | Identifie la date courante.
CURRENT_TIME | Identifie l´heure courante.
CURRENT_TIMESTAMP | Identifie la date et l´heure courantes.
CURRENT_USER | Identifie l´utilisateur actif dans le serveur de base de données.
SESSION_USER | Identifie l´ID d´autorisation actif, s´il est différent de l´utilisateur.
SYSTEM_USER | Identifie l´utilisateur actif dans le système d´exploitation hôte.
Microsoft SQL Server prend en charge toutes les fonctions scalaires intégrées. Oracle ne prend pas en charge les fonctions scalaires intégrées indiquées ci-dessus; cependant, il prend en charge la fonction USER fonction USER (Oracle, MySQL, PostgreSQL) en tant que synonyme de CURRENT_USER et SYSDATE fonction SYSDATE (Oracle, MySQL) en tant que synonyme de CURRENT_TIMESTAMP. MySQL prend en charge toutes les fonctions scalaires intégrées SQL99, plus les deux variantes Oracle. PostgreSQL prend en charge la fonction USER, telle qu´elle est définie dans SQL99, en tant que synonyme de CURRENT_USER. De plus, MySQL prend en charge la fonction NOW( )fonction NOW (MySQL) et UNIX_TIMESTAMP( )UNIX_TIMESTAMP (MySQL) en tant que synonymes de la fonction CURRENT_TIMESTAMP. PostgreSQL prend en charge toutes les fonctions scalaires intégrées SQL99, à l´exception de SESSION_USER.
Exemple
Les requêtes qui suivent récupèrent les valeurs des fonctions intégrées. Remarque: les implémentations des différent éditeurs renvoient les dates dans leurs formats d´origine:
    /* On MySQL */
    SELECT CURRENT_TIMESTAMP;
    -> '2001-12-15 23:50:26'

    /* On Microsoft SQL Server */
    SELECT CURRENT_TIMESTAMP
    GO
    -> 'Dec 15,2001 23:50:26'

    /* On Oracle */
    SELECT USER FROM dual;
    -> dylan

Fonctions scalaires numériques
La liste des fonctions scalaires numériques SQL99 officielles est plutôt restreinte. Les différents éditeurs fournissent un nombre élevé de fonctions mathématiques et statistiques supplémentaires. MySQL prend en charge la majorité de ces commandes dans le cadre de ses variantes SQL99. Les autres produits de bases de données proposent les mêmes caractéristiques que les fonctions scalaires numériques par l´intermédiaire de leurs fonctions internes, mais les noms utilisés sont différents de ceux déclarés par la norme SQL. Les fonctions numériques prises en charge et leur syntaxe spécifique sont indiquées dans le Tableau 4.4.
Fonctions numériques SQL99
Fonction | Usage
--- | ---
BIT_LENGTH(expression) | Renvoie un nombre entier représentant le nombre de bits d´une expression.
CHAR_LENGTH(expression) | Renvoie un nombre entier représentant le nombre de caractères d´une expression.
EXTRACT(datetime_expression datepart FROM expression) | Permet d´extraire la partie date (YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR ou TIMEZONE_MINUTE) d´une expression.
OCTET_LENGTH(expression) | Renvoie un nombre entier représentant le nombre d´octets d´une expression. Cette valeur est identique à BIT_LENGTH/8.
POSITION(starting_string IN search_string) | Renvoie un nombre entier représentant la position de début d´une chaîne dans la chaîne de recherche.
BIT_LENGTH, CHAR_LENGTH et OCTET_LENGTH
Au niveau des éditeurs, la variante la plus proche de la fonction BIT_LENGTH est Oracle. Oracle prend en charge la fonction LENGTHB , qui renvoie un nombre entier représentant le nombre d´octets d´une expression. MySQL et PostgreSQL prennent en charge la fonction CHAR_LENGTH et le synonyme SQL99 CHARACTER_LENGTH( ). PostgreSQL prend également en charge EXTRACT( ), OCTET_LENGTH( )et POSITION( ), conformément à la norme SQL99. Les deux autre éditeurs proposent chacun une fonction similaire qui assurent des caractéristiques identiques. SQL Server propose la fonction LEN et Oracle, la fonction LENGTH. MySQL et PostgreSQL prennent aussi totalement en charge la fonction OCTET_LENGTH.
Exemple
L´exemple qui suit détermine la longueur d´une chaîne et une valeur récupérée à partir d´une colonne:
    /* On MySQL and PostgreSQL */
    SELECT CHAR_LENGTH('hello');
    SELECT OCTET_LENGTH(book_title) FROM titles;

    /* On Microsoft SQL Server */
    SELECT DATALENGTH(title)
    FROM titles
    WHERE type = 'popular_comp'
    GO

    /* On Oracle */
    SELECT LENGTH('HORATIO') "Length of characters"
    FROM dual;
EXTRACT
La fonction EXTRACT n´est pas prise en charge par les éditeurs de base de données, sauf dans les implémentations PostgreSQL et MySQL. Chaque éditeur prend en charge une commande séparée permettant de garantir la même fonctionnalité. Oracle utilise la fonction TO_CHAR pour extraire une portion de date dans une chaîne de caractères. SQL Server utilise la fonction CONVERT pour extraire une portion de date. L´implémentation MySQL s´est quelque peu étendue au delà de la norme SQL99. La norme SQL99 n´inclut aucune fonctionnalité permettant de renvoyer plusieurs champs pour un même appel de la fonction EXTRACT( ) (par exemple, "DAY_HOUR"). Les extensions MySQL essaient d´accomplir les mêmes actions que la combinaison DATE_TRUNC( ) et DATE_PART( ) dans PostgreSQL. MySQL prend en charge les dateparts répertoriés au Tableau 4.5.
Dateparts MySQL
Valeur type | Signification | Format attendu
--- | --- | ---
SECOND | Secondes | SECONDS
MINUTE | Minutes | MINUTES
HOUR | Heures | HOURS
DAY | Jours | DAYS
MONTH | Mois | MONTHS
YEAR | Années | YEARS
MINUTE_SECOND | Minutes et secondes | "MINUTES:SECONDS"
HOUR_MINUTE | Heures et minutes | "HOURS:MINUTES"
DAY_HOUR | Jours et heures | "DAYS HOURS"
YEAR_MONTH | Années et mois | "YEARS-MONTHS"
HOUR_SECOND | Heures, minutes, secondes | "HOURS:MINUTES:SECONDS"
DAY_MINUTE | Jours, heures, minutes | "DAYS HOURS:MINUTES"
DAY_SECOND | Jours, heures, minutes, secondes | "DAYSHOURS:MINUTES:SECONDS"
Exemple
Cet exemple extrait les dateparts de plusieurs valeurs datetime:
    /* On MySQL  */
    SELECT EXTRACT(YEAR FROM "2013-07-02");
    -> 1999
    SELECT EXTRACT(YEAR_MONTH FROM "2013-07-02 01:02:03");
    -> 199907
    SELECT EXTRACT(DAY_MINUTE FROM "2013-07-02 01:02:03");
    -> 20102
POSITION
La fonction POSITION renvoie un nombre entier indiquant la position de départ d´une chaîne dans la chaîne de recherche. MySQL et PostgreSQL prennent en charge la fonction POSITION sans aucune variation par rapport à la syntaxe SQL99. PostgreSQL propose une fonction synonyme, TEXTPOS, tandis que MySQL inclut la fonction synonyme, LOCATE. Oracle propose la fonction équivalente INSTR. Microsoft SQL Server propose à la fois la fonction CHARINDEX et la fonction PATINDEX. Les fonctions CHARINDEX et PATINDEX sont très similaires, sauf que PATINDEX permet d´inclure des caractères génériques dans les critères de recherche. Par exemple:
    /* On MySQL */
    SELECT LOCATE('bar', 'foobar');
    -> 4

    /* On MySQL and PostgreSQL */
    SELECT POSITION('fu' IN 'snafhu');
    -> 0

    /* On Microsoft SQL Server */
    SELECT CHARINDEX( 'de', 'abcdefg' )
    GO
    -> 4
    SELECT PATINDEX( '%fg', 'abcdefg' )

    GO
    -> 6

Fonctions de chaîne
Les fonctions de chaîne de base proposent diverses fonctionnalités et renvoient une chaîne comme jeu de résultats. Certaines fonctions de chaîne sont dyadiques; autrement dit, elles peuvent s´appliquer à deux chaînes à la fois. SQL99 prend en charge les fonctions de chaîne répertoriées au Tableau 4.6.
Fonctions de chaîne SQL
Fonction | Usage
--- | ---
    CONCATENATE(expression || expression)
| Ajoute au moins deux expressions littérales, valeurs de colonne ou variables dans le cadre d´une seule chaîne.
    CONVERT
| Convertit une chaîne en une autre représentation dans le même jeu de caractères.
    LOWER
| Convertit une chaîne en minuscules.
    SUBSTRING
| Extrait une portion d´une chaîne.
    TRANSLATE
| Convertit une chaîne d´un jeu de caractères vers un autre.
    TRIM
| Supprime les caractères initiaux, les caractères finaux ou les deux, dans une chaîne de caractères.
    UPPER
| Convertit une chaîne en majuscules.
CONCATENATE
SQL99 définit un opérateur de concaténation ( || ), qui joint deux chaînes distinctes en une seule valeur de chaîne. La fonction CONCATENATE joint deux chaînes ou plus, pour générer une seule chaîne de sortie. PostgreSQL et Oracle prennent en charge l´opérateur de double concaténation. Dans Microsoft SQL Server, c'est le signe plus (+) qui sert à indiquer la concaténation. MySQL prend en charge une fonction similaire, CONCAT( ). Reportez-vous à la section Opérateurs de concaténationdans le chapitre 3, pour de plus amples informations sur la concaténation dans Oracle, PostgreSQL et Microsoft SQL Server.
Syntaxe SQL99
    CONCATENATE('string1' || 'string2')
Syntaxe MySQL
    CONCAT(str1, str2, [,...n])
Si l´une des valeurs de concaténation est NULL, la chaîne renvoyée a la valeur NULL. Par ailleurs, toute valeur numérique concaténée est implicitement convertie en une chaîne de caractères:
    SELECT CONCAT('My ', 'bologna ', 'has ', 'a ', 'first ', 'name...');
    -> 'My bologna has a first name...'
    SELECT CONCAT('My ', NULL, 'has ', 'first ', 'name...');
    -> NULL
CONVERT et TRANSLATE
La fonction CONVERT modifie la représentation d´une chaîne de caractères au sein de sa collation et de son jeu de caractères. Par exemple, CONVERT permet de modifier le nombre de bits par caractère. TRANSLATE modifie le jeu de caractères d´une valeur de chaîne pour le convertir d´un jeu de caractères de base vers un autre. Il est donc possible d´utiliser TRANSLATE pour traduire une valeur du jeu de caractères anglais en Kanji (japonais) ou en jeu de caractères cyrillique (russe). La traduction doit déjà exister, soit par défaut soit après création au moyen de la commande CREATE TRANSLATION.
Syntaxe SQL99
    CONVERT (char_value target_char_set USING form_of_use source_char_name)

    TRANSLATE(char_value target_char_set USING translation_name)
De tous les éditeurs de base de données, seul Oracle prend en charge les fonctions CONVERT et TRANSLATE en leur attribuant la même signification que SQL99. L´implémentation de la fonction TRANSLATE est très similaire dans Oracle et dans SQL99, mais elle n´est pas identique. Dans le cadre de son implémentation, Oracle n´accepte que deux arguments et permet uniquement la traduction pour le jeu de caractères de la base de données ou le jeu de caractères de la langue locale.
Dans MySQL, l´implémentation de la fonction CONVERT se limite à la traduction des nombres d´une base vers une autre. En revanche, l´implémentation de CONVERT dans Microsoft SQL Server propose un utilitaire très puissant qui modifie le type de données de base d´une expression; pour le reste, il n´offre aucun point commun avec la fonction CONVERT de SQL99. PostgreSQL ne prend pas en charge CONVERT et son implémentation de TRANSLATE permet de transformer toute occurrence d´une chaîne de caractères en une autre chaîne de caractères.
Syntaxes et variations dans MySQL
    CONV(int, from_base, to_base)
MySQL ne prend pas en charge la fonction TRANSLATE. Dans le cadre de cette implémentation, CONVERT renvoie une valeur de chaîne qui représente le nombre pour une conversion de la valeur from_base à la valeur to_base. Si un autre nombre a la valeur NULL, la fonction renvoie NULL. Voici quelques exemples:
    SELECT CONV("a",16,2);
    -> '1010'
    SELECT CONV("6E",18,8);
    -> '172'
    SELECT CONV(-17,10,-18);
    -> '-H'
Syntaxe et variations dans Microsoft SQL Server
    CONVERT (data_type[(length) | (precision,scale)], expression[,style])
Microsoft SQL Server ne prend pas en charge la fonction TRANSLATE. L´implémentation par Microsoft de la fonction CONVERT ne respecte pas la spécification SQL99. En revanche, cette fonction est équivalente, d´un point de vue fonctionnel, à la fonction CAST . La clause de style permet de définir le format d´une conversion de date. Reportez-vous à la documentation de l´éditeur pour plus d´informations. Voici un exemple:
    SELECT title, CONVERT(char(7), ytd_sales)
    FROM titles
    ORDER BY title
    GO
Syntaxe et variations dans Oracle
    CONVERT('char_value', target_char_set, source_char_set)

    TRANSLATE('char_value', 'from_text', 'to_text')
Dans le cadre de l´implémentation Oracle, la fonction CONVERT renvoie char_value dans le jeu de caractères cible. Char_value est la chaîne à convertir, tandis que target_char_set représente le nom du jeu de caractères vers lequel char_value est converti. Source_char_set représente le nom du jeu de caractères dans lequel char_value était initialement stocké.
Les jeux de caractères cible et source peuvent être des chaînes littérales, des variables ou des colonnes contenant le nom de ce jeu de caractères. Remarque: Les caractères de remplacement inadéquats pourront faire l´objet d´une substitution dans le cadre d´une conversion de ou vers un jeu de caractères qui ne peut pas prendre en charge une représentation de tous les caractères employés pour la conversion.
Oracle prend en charge plusieurs jeux de caractères courants, notamment US7ASCII, WE8DECDEC, WE8HP, F7DEC, WE8EBCDIC500, WE8PC850 et WE8ISO8859P1. Par exemple:
    SELECT CONVERT('Groß', 'US7ASCII', 'WE8HP')
    FROM DUAL;
    ->Gross
Syntaxe et variations dans PostgreSQL
    TRANSLATE (character_string, from_text, to_text)
PostgreSQL ne prend pas en charge la fonction CONVERT. Dans PostgreSQL, l´implémentation de la fonction TRANSLATE propose un vaste superensemble de fonctions similaires à celles incluses dans la spécification SQL99. Toute occurrence d´une chaîne de texte est alors convertie en une autre chaîne qui est elle-même incluse dans une autre chaîne spécifiée. Voici un exemple:
    SELECT TRANSLATE('12345abcde', '5a', 'XX');
    -> 1234XXbcde

    SELECT TRANSLATE(title, 'Computer', 'PC')
    FROM  titles
    WHERE type = 'Personal_computer'
LOWER et UPPER
Les fonctions LOWER et UPPER permettent de modifier aussi rapidement que simplement la casse d´une chaîne, afin de passer respectivement tous les caractères en minuscule ou en majuscule. Ces fonctions sont prises en charge dans toutes les implémentations de base de données couvertes dans cet ouvrage.
Exemple
    SELECT LOWER('You Talkin To ME?'), UPPER('you talking to me?!');
    -> you talking to me?, YOU TALKIN TO ME?!
Les divers éditeurs de base de données prennent également en charge diverses autres fonctions de formatage de texte propres à leur implémentation.
SUBSTRING
La fonction SUBSTRING permet d´extraire une chaîne de caractères à partir d´une autre.
Syntaxe SQL99
    SUBSTRING(extraction_string FROM starting_position [FOR length]
    [COLLATE collation_name])
Si une entrée a la valeur NULL, la fonction SUBSTRING renvoie NULL. Extraction_string est la chaîne à partir de laquelle la valeur de caractère est extraite. Il peut s´agit d´une chaîne littérale, d´une colonne de table comportant un type de données de caractère ou d´une variable comportant un type de données de caractère. Starting_position est un nombre entier qui indique à la fonction la position à laquelle exécuter l´extraction. Le paramètre optionnel length est un nombre entier qui indique à la fonction le nombre de caractères à extraire, à partir de starting_position.
Syntaxes et variations dans MySQL
    SUBSTRING(extraction_string FROM starting_position)
Dans MySQL, l´implémentation suppose que les caractères à extraire couvrent toute la plage de la position de départ jusqu´à la fin de la chaîne de caractères.
Syntaxe et variations dans Microsoft SQL Server
    SUBSTRING(extraction_string [FROM starting_position] [FOR length])
Microsoft SQL Server prend largement en charge la norme SQL99, sauf qu´il n´autorise pas la clause COLLATE. Microsoft permet d´appliquer cette commande aux données de type texte, image et binaire; cependant, starting_position et length représentent le nombre d´octets, et non le nombre de caractères à compter.
Syntaxe et variations dans Oracle
    SUBSTR(extraction_string, starting_position [, length])
L´implémentation Oracle, SUBSTR, est très similaire à l´implémentation SQL99. Elle ne prend pas en charge la clause COLLATE. Lorsque starting_value est une valeur négative, Oracle commence à compter à partir de la fin de extraction_string. Si aucune longueur n´est indiquée, la fonction renvoie le reste de la chaîne (à partir de starting_position).
Syntaxe et variations dans PostgreSQL
    SUBSTRING(extraction_string [FROM starting_position] [FOR length])
PostgreSQL prend largement en charge la norme SQL99, sauf qu´il n´accepte pas la clause COLLATE.
Exemples
Ces exemples donnent normalement de bons résultats avec les quatre éditeurs de base de données mentionnés dans cet ouvrage. Seul le deuxième exemple Oracle, qui inclut une position de début négative, ne fonctionne pas avec les autres éditeurs (cela suppose, bien entendu, que la fonction SUBSTR d´Oracle est traduite en SUBSTRING):
    /* On Oracle, counting from the left */
    SELECT SUBSTR('ABCDEFG',3,4) FROM DUAL;
    -> CDEF

    /* On Oracle, counting from the right */
    SELECT SUBSTR('ABCDEFG',-5,4) FROM DUAL;
    -> CDEF

    /* On MySQL */
    SELECT SUBSTRING('Be vewy, vewy quiet',5);
    -> 'wy, vewy quiet''

    /* On PostgreSQL or SQL Server */
    SELECT au_lname, SUBSTRING(au_fname, 1, 1)
    FROM authors
    WHERE au_lname = 'Carson'
    -> Carson     C
TRIM
La fonction TRIM supprime les espaces initiaux, les espaces finaux ou les deux, dans une chaîne de caractères spécifique. Cette fonction supprime également les autres types de caractères dans une chaîne de caractères spécifique. La fonction par défaut a l´action suivante: elle élimine le caractère spécifié aux deux extrémités de la chaîne de caractères. Si aucune chaîne n´est spécifiée pour la suppression, TRIM élimine par défaut les espaces.
Syntaxe SQL99
    TRIM( [ [{LEADING | TRAILING | BOTH}] [removal_string] FROM ]
      target_string
      [COLLATE collation_name])
Removal_string représente la chaîne de caractères à élaguer. Target_string représente la chaîne de caractères dont il faut extraire des caractères. Si removal_string n´est pas spécifié, TRIM élimine les espaces. La clause COLLATE inclut le jeu de résultats de la fonction dans un autre jeu de collation préexistant.
MySQL, PostgreSQL et Oracle prennent en charge la syntaxe SQL99 de TRIM.
Microsoft SQL Server (ainsi, dans ce cas précis, que les autres éditeurs) propose les fonctions LTRIM et RTRIM qui permettent d´éliminer respectivement les espace initiaux et les espaces finaux. LTRIM et RTRIM ne s´appliquent pas aux autres types de caractères.
Exemples
    SELECT TRIM('   wamalamadingdong  ');
    -> 'wamalamadingdong'

    SELECT TRIM(LEADING '19' FROM '1976 AMC GREMLIN');
    -> '76 AMC GREMLIN'

    SELECT TRIM(BOTH 'x' FROM 'xxxWHISKEYxxx');
    -> 'WHISKEY'

    SELECT TRIM(TRAILING 'snack' FROM 'scooby snack');
    -> 'scooby '