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 '