Présentation Fonctions SQL Expression scalaire LEFT RIGHT MID, SUBSTR et SUBSTRING MID SUBSTR BTRIM LTRIM RTRIM TRIM OVERLAY REPLACE TRANSLATE CONCAT GROUP_CONCAT STRING_AGG LPAD RPAD LOWER UPPER LEN / LENGTH LEN LENGTH INSTR PATINDEX POSITION COUNT AVG MAX MIN SUM EVERY TOP BOTTOM LIMIT ASCII UNICODE CURRENT_USER SYSTEM_USER USER_NAME SOUNDEX, SOUND LIKE SOUNDEX2, SOUND2 LIKE ADD_MONTHS LAST_DAY DAY DAYOFMONTH DAYOFWEEK DAYOFYEAR EOMONTH YEAR et MONTH CURRENT_TIMESTAMP CURRENT_TIME, CURTIME HOUR MINUTE SECONDE SEC_TO_TIME NOW LOCALTIME, LOCALTIMESTAMP CURRENT_DATE, CURDATE GETDATE GETUTCDATE DATEADD ADDDATE DATEDIFF et DATEDIFF_BIG DATEPART DATETIMEFROMPARTS DATEFROMPARTS MONTHS_BETWEEN NEW_TIME NEXT_DAY ROUND SYSDATE TRUNC ISDATE COALESCE NVL, IFNULL, ISNULL NULLIF DECODE CASE MATCH AGAINST Opérateurs et fonctions sur les bits IS JSON XXX JSON_VALUE JSON_QUERY JSON_EXISTS JSON_OBJECT JSON_OBJECTAGG JSON_ARRAY JSON_ARRAYAGG
Fonctions SQL utilisables dans les requêtes SQL
Les principales fonctions SQL pouvant être utilisées sur des requêtes en code SQL sont les suivantes (classées par thème) :
extraction de caractères : suppression de caractères : substitution de caractères : conversion en chaîne de caractères : modification de la casse d'une chaîne de caractères : taille d'une chaîne de caractères : LEN et LENGTH CHARACTER_LENGTH, CHAR_LENGTH et OCTET_LENGTH position d'une chaîne de caractères : nombre d'enregistrements dans un fichier : Voir aussi les fonctions mathématiques SQL . sélection des n premiers ou des n derniers enregistrements : Instruction conditionnelle Fonctions et opérateurs sur les bits
Pour plus de détails sur les fonctions SQL, consultez une documentation spécifique au langage SQL.
Remarques :
Ces instructions sont utilisables :dans le code SQL des requêtes créées sous l'éditeur de requêtes. Ces requêtes sont ensuite exécutées grâce à la fonction HExécuteRequête . dans le code SQL des requêtes exécutées avec la fonction du WLangage HExécuteRequêteSQL . Sauf indication contraire, ces fonctions sont utilisables sur tous les types de sources de données (Oracle, Access, SQL Server, ...).
Expression scalaire
Chaque paramètre passé à ces fonctions SQL correspond à une expression (appelée "Expression scalaire").
Une expression peut correspondre :
à une constante au nom d'une rubrique au résultat d'une autre fonction SQL à un calcul sur une expression ELT ELT renvoie la nième chaîne de caractères d'une liste de chaînes.Format d'utilisation :
ELT(Numéro de la chaîne, Chaîne1, Chaîne2, Chaîne3, ...)
Exemple : Le code SQL suivant permet de sélectionner la première chaîne de la liste :
SELECT ELT ( 1 , 'ej' , 'Heja' , 'hej' , 'foo' )
EXTRACTVALUE EXTRACTVALUE permet de manipuler des chaînes XML. Cette fonction renvoie le texte (CDATA) du premier noeud texte qui est un fils de l'élément correspondant à l'expression XPATH. Si plusieurs correspondances sont trouvées, le contenu du premier noeud texte fils de chaque noeud correspondant à l'expression XPATH est retourné sous forme d'une chaîne délimitée par des espaces. Format d'utilisation :
EXTRACTVALUE(Fragment de XML, Expression XPATH)
Le paramètre <Fragment de XML> doit être un fragment de XML valide. Il doit contenir une unique racine.
Exemple : Le code suivant permet de compter les éléments trouvés :
SELECT EXTRACTVALUE ( '<a><b/></a>' , 'count(/a/b)' ) FROM CLIENT WHERE CLCLEUNIK= 1
LEFT
La fonction
LEFT extrait la partie gauche (c'est-à-dire les premiers caractères) d'une expression.
Format d'utilisation :
LEFT(Expression initiale, Nombre de caractères à extraire)
Exemple : Le code SQL suivant permet de lister les départements des clients :
SELECT LEFT ( CodePostal, 2 ) FROM CLIENT
RIGHT
La fonction
RIGHT extrait la partie droite (c'est-à-dire les derniers caractères) d'une expression.
Format d'utilisation :
RIGHT(Expression initiale, Nombre de caractères à extraire)
Exemple : Le code SQL suivant permet d'extraire les cinq derniers caractères du nom des clients :
SELECT RIGHT ( Nom, 5 ) FROM CLIENT
MID, SUBSTR et SUBSTRING
Les fonctions
MID, SUBSTR et SUBSTRING permettent d'extraire une sous-chaîne du contenu d'une expression à partir d'une position donnée.
Si la position donnée correspond à :
un nombre négatif, l'extraction se fera à partir de la fin de la chaîne. à 0, l'extraction se fera à partir du début de la chaîne (équivalent à la position 1). Si la valeur absolue de la position donnée (connue grâce à la fonction
ABS ) est supérieure au nombre de caractères de l'expression initiale, une chaîne vide est retournée.
Exemple : Le code SQL suivant permet d'extraire les villes dont le second caractère est un "A" :
SELECT CODEPOSTAUX. IDCedex AS IDCedex, CODEPOSTAUX. CodePostal AS CodePostal, CODEPOSTAUX. Ville AS Ville, SUBSTR ( CODEPOSTAUX. Ville, 2 , 1 ) AS Formule1FROM CODEPOSTAUXWHERE SUBSTR ( CODEPOSTAUX. Ville, 2 , 1 ) = 'A'
MID
La fonction
MID peut être utilisée uniquement sur une source de données de type Access.
Format d'utilisation :
MID(Expression initiale, Position de départ, Nombre de caractères à extraire)
Exemple : Le code SQL suivant permet d'extraire les 3ème et 4ème caractères du nom des clients :
SELECT MID ( Nom, 3 , 2 ) FROM CLIENT
SUBSTR
La fonction
SUBSTR peut être utilisée uniquement sur une source de données de type Oracle, HFSQL Classic ou HFSQL Client/Serveur.
Format d'utilisation :
SUBSTR(Expression initiale, Position de départ, Nombre de caractères à extraire)
Exemple : Le code SQL suivant permet d'extraire les 3ème et 4ème caractères du nom des clients :
SELECT SUBSTR ( Nom, 3 , 2 ) FROM CLIENT
SPLIT_PART SPLIT_PART découpe une chaîne de caractères selon le séparateur spécifié et renvoie la nième partie de la chaîne.Format d'utilisation :
SPLIT_PART(Expression initiale, Délimiteur, Numéro de la partie à extraire)
Exemple : Le code SQL suivant permet d'extraire les 3 premiers mots correspondants à l'adresse :
SELECT SPLIT_PART ( ADRESSE, ' ' , 1 ) , SPLIT_PART ( ADRESSE, ' ' , 2 ) , SPLIT_PART ( ADRESSE, ' ' , 3 ) FROM CLIENT WHERE CLCLEUNIK= 2
BTRIM
La fonction BTRIM supprime une séquence de caractères en début ou en fin de chaîne. Format d'utilisation :
BTRIM(<Chaîne d'origine>, [<Chaîne à supprimer>])
Exemple : Supprimer la chaîne 'AB' de la chaîne 'ABRACADABRA'
BTRIM ( 'ABRACADABRA' , 'AB' )
Dans cet exemple, le résultat est 'RCDR'.
LTRIM
La fonction
LTRIM renvoie une chaîne de caractères :
soit sans les espaces situés à gauche. soit sans une liste de caractères. La suppression des caractères s'effectue de la gauche vers la droite. Cette suppression est sensible à la casse (majuscule/minuscule). Cette suppression s'arrête sur le premier caractère trouvé n'appartenant pas à la liste spécifiée.
La suppression de caractères spécifiques ne peut pas être réalisée sur une source de données de type Access ou SQL Server.
Format d'utilisation :
-- Suppression des espaces situés à gauche
Exemple : Le nom des clients est précédé de la civilité des clients ("Mr.", "Mme." ou "Melle."). Le code SQL suivant permet de :
supprimer la civilité de chaque nom (lettres "M", "r", "m", "e" et "l" et le caractère point). supprimer l'espace précédent le nom (espace qui était présent entre la civilité et le nom). SELECT LTRIM ( Nom, 'Mrmel.' ) FROM CLIENTSELECT LTRIM ( Nom) FROM CLIENT
Dans cet exemple :
Si le nom du client est : La chaîne retournée est : 'Melle. DUPONT' 'DUPONT' 'Mr. CIRVAL' 'CIRVAL' 'Mme. Dubois' 'Dubois'
RTRIM
La fonction
RTRIM renvoie une chaîne de caractères :
soit sans les espaces situés à droite. soit sans une liste de caractères. La suppression des caractères s'effectue de la droite vers la gauche. Cette suppression est sensible à la casse (majuscule/minuscule). Cette suppression s'arrête sur le premier caractère trouvé n'appartenant pas à la liste spécifiée.
La suppression de caractères spécifiques ne peut pas être réalisée sur une source de données de type Access ou SQL Server.
Format d'utilisation :
-- Suppression des espaces situés à droite
Exemple : Le code SQL suivant permet de supprimer les caractères 'E', 'U' et 'R' situés à droite du nom des clients :
SELECT RTRIM ( Nom, 'EUR' ) FROM CLIENT
Dans cet exemple :
Si le nom du client est : La chaîne retournée est : 'DUVALEUR' 'DUVAL' 'DRAFUREUR' 'DRAF' 'Galteur' 'Galteur' 'FOURMALTE' 'FOURMALTE' 'BENUR' 'BEN'
TRIM
La fonction
TRIM renvoie une chaîne de caractères :
soit sans les espaces situés à gauche et à droite. soit sans une chaîne de caractères située en début et en fin de chaîne. soit sans une chaîne de caractères située en début de chaîne. soit sans une chaîne de caractères située en fin de chaîne. La suppression des caractères s'effectue de la droite vers la gauche. Cette suppression est sensible à la casse (majuscule/minuscule). Cette suppression s'arrête sur le premier caractère trouvé n'appartenant pas à la chaîne spécifiée.
Format d'utilisation :
-- Suppression des espaces situés à gauche et à droite
OVERLAY
La fonction OVERLAY permet de remplacer une chaîne de caractères dans une autre. Format d'utilisation :
OVERLAY(<Chaîne d'origine> PLACING <Chaîne à remplacer>
FROM <Position de début> [FOR <Longueur>])
Exemple : Le code SQL suivant permet de remplacer "verte" par "rouge" :
SELECT OVERLAY ( 'Pomme verte' PLACING 'rouge' FROM 7 ) FROM Produit
REPLACE
La fonction
REPLACE renvoie une chaîne de caractères :
soit en remplaçant toutes les occurrences d'un mot présent dans une chaîne par un autre mot. soit en remplaçant toutes les occurrences d'un mot présent dans une chaîne. Le remplacement s'effectue de la droite vers la gauche. Ce remplacement est sensible à la casse (majuscule/minuscule). Ce remplacement s'arrête sur le premier caractère trouvé n'appartenant pas à la chaîne spécifiée.
Format d'utilisation :
-- Remplacement de toutes les occurrences d'un mot par un autre
REVERSE REVERSE renvoie une chaîne de caractères dont l'ordre des caractères est l'inverse de la chaîne initiale. TRANSLATE
La fonction
TRANSLATE renvoie une chaîne de caractères en remplaçant tous les caractères indiqués par d'autres caractères. Si un caractère à remplacer n'a pas sa correspondance, ce caractère est supprimé.
Le remplacement s'effectue de la droite vers la gauche. Ce remplacement est sensible à la casse (majuscule/minuscule).
Format d'utilisation :
-- Remplacement des caractères
Exemple : Le code SQL suivant permet de remplacer :
le caractère "é" par le caractère "e". le caractère "è" par le caractère "e". le caractère "à" par le caractère "a". le caractère "ù" par le caractère "u". SELECT TRANSLATE ( MonChamp, 'éèàù' , 'eeau' ) FROM MaTable
CONCAT
La fonction
CONCAT concatène plusieurs chaînes entre elles.
Format d'utilisation :
CONCAT(Chaîne 1, Chaîne 2 [, ..., Chaîne N])
GROUP_CONCAT
La clause GROUP_CONCAT permet de regrouper dans une même chaîne de caractères les différentes valeurs non nulles d'une rubrique issue d'une série d'enregistrements.Chaque valeur peut être séparée par un caractère particulier (un espace, un ";", etc.).
Cette fonction est utile par exemple pour regrouper des résultats en une seule ligne.
Si une clause GROUP BY est utilisée, les valeurs regroupées peuvent être assemblées sur un critère différent.
Format d'utilisation :
GROUP_CONCAT(<Nom colonne> SEPARATOR <Caractère de séparation>)
où :
<Nom colonne> représente la rubrique à regrouper. <Caractère de séparation> représente le caractère qui va séparer chaque valeur. Exemple : Le code SQL suivant permet de récupérer la liste des jours de la semaine et celle des jours du week-end sur une ligne. Les jours sont séparés par un point virgule.
La table "JOURSEMAINE" manipulée est la suivante :
TypeJour NomJour JourDeSemaine Lundi JourDeSemaine Mardi JourDeSemaine Mercredi JourDeSemaine Jeudi JourDeSemaine Vendredi Week-end Samedi Week-end Dimanche
SELECT TypeJour, GROUP_CONCAT ( NomJour SEPARATOR ';' ) FROM JOURSEMAINEGROUP BY TypeJour
Le résultat affiché sera le suivant :
'JourDeSemaine', 'Lundi;Mardi;Mercredi;Jeudi;Vendredi'
STRING_AGG
La fonction STRING_AGG permet de concaténer des chaînes non nulles d'une liste de valeurs.Format d'utilisation :
STRING_AGG(chaîne, séparateur)
Exemple :
Le code suivant permet de renvoyer dans une seule chaîne, la liste des modes de livraison séparée par un ';'.
SELECT STRING_AGG ( libelle, ';' ) AS str FROM livraison
Contenu du fichier livraison :
Transporteur Colis Expresss Recommandé Pris sur place Résultat retourné par la fonction STRING_AGG : "Transporteur;Colis Expresss;Recommandé;Pris sur place".
LPAD
La fonction
LPAD renvoie une chaîne de taille déterminée. Pour atteindre la taille requise, cette chaîne est complétée à gauche :
soit par des espaces. soit par un caractère ou une chaîne donnée. Format d'utilisation :
-- Complétion par des espaces
REPEAT REPEAT renvoie une chaîne de caractères constituée de la n fois la répétition de la chaîne initiale. Si n est inférieur ou égal à 0, la fonction renvoie une chaîne vide. Si la chaîne initiale ou n sont NULL, la fonction renvoie NULL. Format d'utilisation :
REPEAT(Chaîne initiale, n)
Exemple : Le code suivant permet de répéter 3 fois le nom du contact :
SELECT REPEAT ( NOMCONTACT, 14 ) FROM CLIENT WHERE CLCLEUNIK= 10
RPAD
La fonction
RPAD renvoie une chaîne de taille déterminée. Pour atteindre la taille requise, cette chaîne est complétée à droite :
soit par des espaces. soit par un caractère ou une chaîne donnée. Format d'utilisation :
-- Complétion par des espaces
SPACE SPACE renvoie une chaîne constituée de N espaces. Format d'utilisation :
TO_CHAR TO_CHAR permet de convertir en chaîne de caractères : une dateheure, une valeur numérique. Format d'utilisation :
1. Conversion d'une dateheure :
TO_CHAR(<Valeur dateheure>, <Format dateheure> [, <Options dateheure>])
Dans cette syntaxe :
Le paramètre <Format dateheure> peut correspondre à un des éléments suivants : "-", "/", ",", ".", ";", ":" "text" : caractères de ponctuation (séparation) pour une date et/ou une heure. AD, A.D. : Indicateur de l'ère AD pour une date (Après Jésus-Christ) AM, A.M. : Indicateur du méridien AM pour l'heure (Anté méridien) BC, B.C. : Indicateur de l'ère BC pour une date (Avant Jésus-Christ) CC ou SCC Siècle : Si les deux derniers chiffres du siècle sur 4 chiffres sont compris entre 01 et 99 (inclus), le siècle est représenté par les 2 derniers chiffres de l'année. Si les deux derniers chiffres du siècle sur 4 chiffres sont 00, alors le siècle est représenté par les 2 premiers chiffres de l'année. D : Numéro du jour dans la semaine (1-7). DAY : Jour en lettres. DD : Numéro du jour dans le mois (1-31). DDD : Numéro du jour dans l'année (1-366). DY : Jour en lettres abrégé FF [1..9] : Fractions de secondes. Le chiffre représente le nombre de chiffres représentant la fraction de seconde. Exemples :'HH:MI:SS.FF' SELECT TO_CHAR(SYSTIMESTAMP, 'SS.FF3') from dual; FM : Enlève les espaces à droite et à gauche. HH : heure du jour (1-12). HH12 : heure du jour (1-12). HH24 : heure du hour (0-23). IW : Numéro de la semaine dans l'année (1-52 ou 1-53) basé sur le standard ISO. IYY, IY, I : représente les derniers chiffres 3, 2, ou 1 de l'année au format ISO. IYYY : représente les 4 chiffres de l'année. J : Jour dans le calendrier Julien. Le nombre de jours depuis le 1er janvier 4712 BC. MI : Minutes (0-59). MM : Mois (01-12; Janvier = 01). MON : Mois en abrégé. MONTH : Mois complet en lettres. PM, P.M. : Indicateur du méridien PM pour l'heure (Post méridien). Q : Trimestre (1, 2, 3, 4; Janvier - Mars = 1). RM : Mois en chiffres romains (I-XII; Janvier = I). SS : Secondes (0-59). SSSSS : Nombre de secondes passées depuis minuit (0-86399). WW : Numéro de la semaine (1-53) avec la semaine 1 qui commence le premier jour de l'année. W : Numéro de semaine dans le mois (1-5) avec la semaine 1 qui commence le premier jour du mois. X : Séparateur pour la fraction de secondes. Exemple : 'HH:MI:SSXFF'. Y,YYY : Année avec une virgule. La virgule est en fait le séparateur de groupement de la locale. YEAR, SYEAR : Année S signifie BC, Année signée avec un signe moins. YYYY, SYYYY : Année sur 4 chiffres; S signifie BC, Année signée avec un signe moins. YYY, YY, Y : Dernier chiffre de l'année 3, 2, ou 1 chiffre. Le paramètre <Options dateheure> est une chaîne de caractères contenant les mots-clés suivants : "NLS_DATE_LANGUAGE='langue en us'" "NLS_NUMERIC_CHARACTERS ='dg'" : ‘dg' est une chaîne de deux caractères dont le premier correspond au séparateur décimal et le second correspond au séparateur de groupement (entre millier et centaine par exemple).french , NLS_NUMERIC_CHARACTERS =, ‘ 2. Conversion d'une valeur numérique :
TO_CHAR(<Valeur numérique>, <Format numérique> [, <Options numérique>])
Le paramètre <Format numérique> peut correspondre à un des éléments suivants : , (virgule). Positionne une virgule à l'emplacement indiqué. Exemple : 9,999 . (point). Positionne une point à l'emplacement indiqué. Exemple : 99.99 0. Complète avec des zéros devant ou derrière. Exemple : 0999 ou 9990 9. Représente les chiffres. Exemple : 9999 B. Remplace les zéros par des espaces. Exemple : B9999 C. Positionne le symbole de devise selon la norme ISO quand le paramètre NLS_ISO_CURRENCY est utilisé. Exemple : C999 D. Indique la position du séparateur décimal quand le paramètre NLS_NUMERIC_CHARACTER est utilisé. Le (.) est le séparateur par défaut. Exemple : 99D99 EEEE. Retourne une valeur au format scientifique. Exemple : 9.9EEEE G. Indique le séparateur de milliers quand le paramètre NLS_NUMERIC_CHARACTER est utilisé. Il est possible de spécifier plusieurs séparateurs de milliers. Exemple : 9G999 L. Indique la position du symbole de devise quand le paramètre NLS_ISO_CURRENCY est utilisé. Exemple : L999 MI. Place le signe - après des valeurs négatives. Exemple : 9999MI PR. Place les valeurs négatives entre des chevrons. Exemple : 9999PR rn ou RN. Retourne la valeur en chiffres romains en majuscules ou minuscules. S. Indique le signe +/- Positif ou Négatif. Exemple : S9999 U. Indique le symbole monétaire Euro quand le paramètre NLS_DUAL_CURRENCY est utilisé. Exemple : U9999 V. Retourne la valeur en puissance de 10. Exemple 999V99 X. Retourne la valeur en hexadécimal. Exemple : XXXX Dans cette syntaxe, le paramètre <Options numérique> est une chaîne de caractères contenant les mots-clés suivants : "NLS_CURRENCY='monnaie en us'" "NLS_NUMERIC_CHARACTERS ='dg'" : ‘dg' est une chaîne de deux caractères dont le premier correspond au séparateur décimal et le second correspond au séparateur de groupement (entre millier et centaine par exemple). Remarque : Par défaut la langue, la monnaie, et les séparateurs sont déterminés par la nation en cours.
CHAR Format d'utilisation :
<Code ASCII> est un numérique et correspond au caractère ASCII à convertir, compris entre 0 et 255. Dans le cas contraire, le caractère renvoyé par la fonction est NULL.Le résultat de la fonction est le caractère correspondant au code ASCII du paramètre <Code ASCII>.
Remarque : Le résultat renvoyé dépend de l'alphabet courant.
CHR Le résultat de la fonction est le caractère correspondant au code ASCII du paramètre <Code ASCII>.
Remarques :
Le résultat renvoyé dépend de l'alphabet courant. Si on est en UTF8, l'entier envoyé est interprété comme un "code point" sinon le caractère renvoyé correspond au caractère modulo 256. CAST Le paramètre <Expression> représente la valeur à convertir. Le paramètre <Type> représente le nouveau type dans lequel l'expression est convertie. Les types disponibles sont : CHARACTER Chaîne de caractères CHARACTER(Taille) Chaîne sur taille VARCHAR(Taille) Chaîne sur taille CHARACTER VARYING(Taille) Chaîne sur taille CHAR VARYING(Taille) Chaîne sur taille NVARCHAR(Taille) Chaîne Unicode sur taille VARCHAR(Taille) BINARY Chaîne binaire sur taille BINARY(Taille) Chaîne binaire sur taille VARBINARY(Taille) Chaîne binaire sur taille BLOB Mémo binaire CLOB Mémo texte TEXT Mémo texte NCLOB Mémo Unicode NTEXT Mémo Unicode NUMBER(Précision) Entier NUMBER(Précision, échelle) Entier DECIMAL(Précision) Réel DECIMAL(Précision, échelle) Réel TINYINT UNSIGNED Entier non signé sur 1 octet SMALLINT UNSIGNED Entier non signé sur 2 octets INTEGER UNSIGNED Entier non signé sur 4 octets BIGINT UNSIGNED Entier non signé sur 8 octets TINYINT Entier signé sur 1 octet SMALLINT Entier signé sur 2 octets INTEGER Entier signé sur 4 octets BIGINT Entier signé sur 8 octets FLOAT Réel sur 4 octets REAL Réel sur 8 octets DOUBLE PRECISION Réel sur 8 octets MONEY Monétaire DATE DATE DATETIME Date heure TIME Heure
Le résultat de la fonction est la valeur convertie.Exemple :
Ce code renvoie : "126".
CONVERT La fonction CONVERT permet de convertir une chaîne de caractères d'un alphabet vers un autre.
Format d'utilisation :
CONVERT(Texte à convertir, alphabet utilisé, nouvel alphabet)
Exemple : Conversion d'une chaîne UTF-8 en LATIN1 :
SELECT CONVERT ( 'texte en utf8' , 'UTF8' , 'LATIN1' )
Remarque : Cette fonction n'est pas disponible pour les requêtes SQL exécutées sur des fichiers de données HFSQL en Android.
INITCAP INITCAP renvoie une chaîne dans laquelle la première lettre de chaque mot est mise en majuscule et toutes les autres lettres sont mises en minuscules. Format d'utilisation :
Exemple :
Ce code renvoie : "Il Fait Beau".
LOWER
La fonction
LOWER convertit une expression en minuscules.
La fonction LOWER ne peut pas être utilisée sur une source de données de type Access.
Format d'utilisation :
LOWER(Expression initiale)
Exemple : Le code SQL suivant permet de convertir en minuscules le prénom des clients :
SELECT LOWER ( Prénom) FROM CLIENT
UPPER
La fonction
UPPER convertit une expression en majuscules.
La fonction UPPER ne peut pas être utilisée sur une source de données de type Access.
Format d'utilisation :
UPPER(Expression initiale)
Exemple : Le code SQL suivant permet de convertir en majuscules les villes des clients :
SELECT UPPER ( Ville) FROM CLIENT
LCASE LCASE retourne une chaîne avec tous les caractères en minuscules en fonction du jeu de caractères courant.Format d'utilisation :
LCASE(Expression initiale)
Exemple : Le code SQL suivant permet de convertir en minuscules les villes des clients :
SELECT LCASE ( Ville) FROM CLIENT
UCASE UCASE retourne une chaîne avec tous les caractères en majuscules en fonction du jeu de caractères courant.Format d'utilisation :
UCASE(Expression initiale)
Exemple : Le code SQL suivant permet de convertir en majuscules les villes des clients :
SELECT UCASE ( Ville) FROM CLIENT
LEN / LENGTH
Les fonctions
LEN et LENGTH renvoient la taille (le nombre de caractères) d'une expression.
Cette taille comprend tous les caractères y compris les caractères espace et 0 binaire.
LEN
La fonction
LEN peut être utilisée sur tous les types de source de données, sauf les sources de données de type Oracle. Pour les sources de données de type Oracle, utilisez la fonction
LENGTH .
Exemple : Le code SQL suivant permet d'obtenir la taille des noms des clients :
SELECT LEN ( Nom) FROM CLIENT
LENGTH
La fonction
LENGTH peut être utilisée uniquement sur une source de données de type Oracle.
Format d'utilisation :
LENGTH(Expression initiale)
Exemple : Le code SQL suivant permet d'obtenir la taille des noms des clients :
SELECT LENGTH ( Nom) FROM CLIENT
INSTR
La fonction
INSTR renvoie la position d'une chaîne de caractères dans une expression.
La fonction INSTR peut être utilisée uniquement sur une source de données de type Oracle ou supportant la norme SQL-92.
Format d'utilisation :
INSTR(Expression initiale, Chaîne à rechercher, Position de départ, Occurrence)
Exemple : Le code SQL suivant permet d'obtenir la position de la première occurrence de la lettre "T" dans chaque nom de ville des clients :
SELECT INSTR ( Ville, 'T' , 1 , 1 ) FROM CLIENT
FIELD FIELD renvoie l'index de la chaîne recherchée dans la liste.Si la chaîne n'est pas trouvée, la fonction renvoie 0.
Format d'utilisation :
FIELD(Chaîne à rechercher, Chaîne1, Chaîne2, ...)
FIND_IN_SET FIND_IN_SET retourne la position d'une chaîne dans une liste de valeurs.Si la chaîne n'est pas trouvée, la fonction renvoie 0.
Format d'utilisation :
FIND_IN_SET(<Chaîne à rechercher>, <Liste de valeurs>)
Le paramètre <Liste de valeurs> correspond à une chaîne de caractères contenant les valeurs séparées par une virgule.
Exemple : Le code suivant renvoie 3 :
FIND_IN_SET ( 'Rouge' , 'Bleu,Jaune,Rouge,Vert' )
PATINDEX
La fonction
PATINDEX renvoie la position de la première occurrence d'une chaîne de caractères correspondant à une valeur spécifiée (avec des caractères génériques).
Les caractères génériques autorisés sont les suivants :
'%' : représente zéro, un ou plusieurs caractères. '_' : représente un caractère unique. Ces caractères génériques peuvent être combinés.
La fonction PATINDEX peut être utilisée sur une source de données de type HFSQL Classic ou de type SQL Server.
Format d'utilisation :
PATINDEX(Valeur à rechercher, Expression)
Exemple : Le tableau ci-dessous indique la position de la première occurrence trouvée en fonction de différentes valeurs recherchées :
Valeur recherchée Nom de ville '%E%' '%E_' '%AR%' MONTPELLIER 6 10 0 PARIS 0 0 2 TARBES 5 5 2 TOULOUSE 8 0 0 VIENNE 3 0 0
POSITION
La fonction
POSITION renvoie la position d'une chaîne de caractères dans une expression.
Format d'utilisation :
POSITION(Chaîne à rechercher IN Expression initiale)
Exemple :
TestREQ est une Source de Données
sCodeSQL est une chaîne = [
SELECT POSITION( 'No' IN Nom ) As PosNom
FROM cooperateur
LIMIT 0 , 30
]
SI PAS HExécuteRequêteSQL ( TestREQ , MaConnexion , hRequêteSansCorrection , sCodeSQL ) ALORS
Erreur ( HErreurInfo ( ) )
FIN
POUR TOUT TestREQ
Trace ( TestREQ . PosNom )
FIN COUNT
La fonction
COUNT renvoie :
le nombre d'enregistrements sélectionnés dans un fichier. le nombre de valeurs non nulles d'une rubrique. le nombre de valeurs différentes et non nulles d'une rubrique. Format d'utilisation :
COUNT(*)
Exemples :
Le code SQL suivant permet d'obtenir le nombre de produits présents dans le fichier Produit :SELECT COUNT ( * ) FROM PRODUIT
Le code SQL suivant permet d'obtenir le nombre de produits sur lesquelles un taux de TVA de 5,5 % est appliqué :SELECT COUNT ( TauxTVA) FROM PRODUITWHERE TauxTVA = '5.5'
Le code SQL suivant permet d'obtenir le nombre des taux de TVA différents et non nuls :SELECT COUNT ( DISTINCT PRODUIT. TauxTVA) FROM PRODUIT
AVG
La fonction
AVG calcule :
soit la moyenne d'un ensemble de valeurs non nulles. soit la moyenne d'un ensemble de valeurs différentes et non nulles. Format d'utilisation :
AVG(Rubrique)
Exemples :
Le code SQL suivant permet d'obtenir le salaire moyen des employés :SELECT AVG ( Salaire) FROM EMPLOYE
Le code SQL suivant permet d'obtenir la moyenne des salaires différents des employés :SELECT AVG ( DISTINCT Salaire) FROM EMPLOYE
MAX
La fonction
MAX renvoie la plus grande valeur des valeurs présentes dans une rubrique pour tous les enregistrements sélectionnés dans le fichier.
La fonction MAX utilisée dans une requête sans regroupement doit renvoyer un et un seul enregistrement. Si la requête contient des regroupements, il y aura un enregistrement par regroupement.
Si la source de données contient des enregistrements, l'enregistrement retourné par la requête contiendra la valeur maximale.
Si la source de données ne contient pas d'enregistrements, la valeur du MAX dans l'enregistrement retourné est NULL.
Format d'utilisation :
MAX(Rubrique)
Exemple : Le code SQL suivant permet d'obtenir le salaire maximal des employés :
SELECT MAX ( Salaire) FROM EMPLOYE
MIN
La fonction
MIN renvoie la plus petite valeur des valeurs non nulles présentes dans une rubrique pour tous les enregistrements sélectionnés dans le fichier.
Format d'utilisation :
MIN(Rubrique)
Exemple : Le code SQL suivant permet d'obtenir le salaire minimal des employés :
SELECT MIN ( Salaire) FROM EMPLOYE
SUM
La fonction
SUM renvoie :
soit la somme totale des valeurs non nulles présentes dans une rubrique pour tous les enregistrements sélectionnés dans le fichier. soit la somme totale des valeurs différentes et non nulles présentes dans une rubrique pour tous les enregistrements sélectionnés dans le fichier. Format d'utilisation :
SUM(Rubrique)
Exemples :
Le code SQL suivant permet d'obtenir la somme totale des salaires :SELECT SUM ( Salaire) FROM EMPLOYE
Le code SQL suivant permet d'obtenir la somme totale des salaires différents :SELECT SUM ( DISTINCT Salaire) FROM EMPLOYE
Remarque : La rubrique manipulée par la fonction
SUM ne doit pas correspondre au résultat d'une opération. Ainsi, la syntaxe suivante génère une erreur :
SELECT (A*B) AS C, SUM CFROM MONFICHIER
Cette syntaxe doit être remplacée par la syntaxe suivante :
SELECT (A*B) AS C, SUM (A*B) FROM MONFICHIER
EVERY
La fonction EVERY est une fonction d'agrégation (comme l'ordre SUM par exemple), c'est-à-dire que la fonction agit sur un groupe de données et renvoie une valeur.La fonction EVERY renvoie :
Vrai si tous les arguments qu'elle a reçu sont vérifiés et vrai.Faux si au moins un des arguments n'est pas vérifié.Format d'utilisation :
EVERY(Expression 1, Expression 2, ..., Expression N)
Exemple :
Le code SQL suivant permet d'obtenir la liste des sociétés ayant des employés dont le salaire est supérieur à 10000 :
SELECT societe. nom, EVERY ( employe. salaire > 10000 ) AS riche FROM societe NATURAL JOIN employe GROUP BY societe. nom
TOP
La fonction
TOP renvoie uniquement les n premiers enregistrements du résultat d'une requête.
La fonction TOP ne peut pas être utilisée sur une source de données de type Oracle ou PostgreSQL.
Format d'utilisation :
TOP Nombre du dernier enregistrement sélectionné
Exemple : Le code SQL suivant permet de lister les 10 meilleurs clients :
SELECT TOP 10 SUM ( COMMANDE. TotalTTC) AS TotalTTC, . NomClientFROM CLIENT, COMMANDEWHERE CLIENT. NumClient = COMMANDE. NumClientGROUP BY NomClientORDER BY TotalTTC DESC
Remarque :
Il est conseillé d'utiliser la fonction TOP sur une requête triée. Dans le cas contraire, les enregistrements retournés par la fonction TOP seront sélectionnés selon leur numéro d'enregistrement. Il est possible de passer un paramètre à la fonction TOP . Le paramètre peut être : SELECT TOP { pNombreClientsMax} . IDClient AS IDClient, . Nom AS Nom, . Prénom AS Prénom, . Email AS Email, . PointsFidélités AS PointsFidélitésFROM
BOTTOM
La fonction
BOTTOM renvoie uniquement les n derniers enregistrements du résultat d'une requête.
La fonction BOTTOM peut être utilisée uniquement sur une source de données de type HFSQL.
Format d'utilisation :
BOTTOM Nombre du dernier enregistrement sélectionné
Exemple : Le code SQL suivant permet de lister les 10 plus mauvais clients :
SELECT BOTTOM 10 SUM ( COMMANDE. TotalTTC) AS TotalTTC, . NomClientFROM CLIENT, COMMANDEWHERE CLIENT. NumClient = COMMANDE. NumClientGROUP BY NomClientORDER BY TotalTTC DESC
Remarque :
Il est conseillé d'utiliser la fonction BOTTOM sur une requête triée. Dans le cas contraire, les enregistrements retournés par la fonction BOTTOM seront sélectionnés selon leur numéro d'enregistrement. Il est possible de passer un paramètre à la fonction BOTTOM . Le paramètre peut être : LIMIT
La fonction
LIMIT renvoie uniquement les n premiers enregistrements du résultat d'une requête.
La fonction LIMIT ne peut pas être utilisée sur une source de données de type Oracle ou PostgreSQL.
Format d'utilisation :
LIMIT Nombre du dernier enregistrement sélectionné
Exemple : Le code SQL suivant permet de lister les 10 meilleurs clients :
SELECT SUM ( COMMANDE. TotalTTC) AS TotalTTC, . NomClientFROM CLIENT, COMMANDEWHERE CLIENT. NumClient = COMMANDE. NumClientGROUP BY NomClientORDER BY TotalTTC DESC LIMIT 10
Remarque :
Il est conseillé d'utiliser la fonction LIMIT sur une requête triée. Dans le cas contraire, les enregistrements retournés par la fonction TOP seront sélectionnés selon leur numéro d'enregistrement. Il est possible de passer un paramètre à la fonction LIMIT . Le paramètre peut être : ASCII
La fonction
ASCII renvoie le code ASCII :
soit d'un caractère. soit du première caractère d'une chaîne. Si le caractère ou la chaîne de caractères spécifiée correspond à une chaîne vide (""), la fonction ASCII renvoie 0.
Format d'utilisation :
-- Code ASCII d'un caractère
UNICODE
La fonction
UNICODE renvoie la valeur entière définie par la norme Unicode :
soit d'un caractère. soit du première caractère d'une chaîne. Format d'utilisation :
-- Code Unicode d'un caractère
CURRENT_USER
La fonction CURRENT_USER renvoie le nom de l'utilisateur de la connexion en cours.Exemple : Le code suivant met à jour l'auteur de la modification de la table CLIENT :
UPDATE CLIENT SET USER= CURRENT_USER ( ) WHERE IDCLIENT= 1
SYSTEM_USER
La fonction SYSTEM_USER renvoie le nom de l'utilisateur de la connexion en cours.Exemple : Le code suivant met à jour l'auteur de la modification de la table CLIENT :
UPDATE CLIENT SET USER= SYSTEM_USER ( ) WHERE IDCLIENT= 1
USER_NAME
La fonction USER_NAME renvoie le nom de l'utilisateur de la connexion en cours.Exemple : Le code suivant met à jour l'auteur de la modification de la table CLIENT :
UPDATE CLIENT SET USER= USER_NAME ( ) WHERE IDCLIENT= 1
SOUNDEX, SOUND LIKE
Les fonctions
SOUNDEX et
SOUND LIKE renvoient la représentation phonétique d'une chaîne de caractères (basé sur un algorithme anglo-saxon).
Format d'utilisation :
SOUNDEX(Chaîne)
Exemple : Le code SQL suivant permet de lister les clients dont le nom correspond phonétiquement à "Henry" :
SELECT CLIENT. NomClientFROM CLIENT WHERE SOUNDEX ( CLIENT. NomClient) = SOUNDEX ( 'Henry' )
SELECT CLIENT. NomClientFROM CLIENT WHERE CLIENT. NomClient SOUND LIKE 'Henry'
Remarque : la fonction SOUNDEX utilisée sur plusieurs bases différentes (HFSQL, Oracle, MySQL, ...) peut renvoyer des résultats différents selon la base utilisée.
SOUNDEX2, SOUND2 LIKE
Les fonctions
SOUNDEX2 et
SOUND2 LIKE renvoient la représentation phonétique d'une chaîne de caractères (basé sur un algorithme proche du français).
Format d'utilisation :
SOUNDEX2(Chaîne)
Exemple : Le code SQL suivant permet de lister les clients dont la ville correspond phonétiquement à "Montpellier" :
SELECT CLIENT. NomVilleFROM CLIENT WHERE SOUNDEX2( CLIENT. NomVille) = SOUNDEX2( 'Montpellier' )
SELECT CLIENT. NomVilleFROM CLIENT WHERE CLIENT. NomVille SOUND2 LIKE 'Montpellier'
ADD_MONTHS
La fonction
ADD_MONTHS permet d'ajouter plusieurs mois à une date spécifiée.
Format d'utilisation :
ADD_MONTHS(Date,Nombre de mois)
Exemple : Le code SQL suivant permet de sélectionner les commandes du mois d'avril 2003.
SELECT DATECDE, ADD_MONTHS ( '20070203' , 2 ) AS AM FROM COMMANDE
LAST_DAY
La fonction
LAST_DAY permet d'obtenir la date du dernier jour du mois spécifié.
Exemple : Le code SQL suivant permet de sélectionner les commandes du mois de février 2008 :
SELECT LAST_DAY ( '20080203' ) AS LD, DATECDE FROM COMMANDEWHERE COMMANDE. CLCLEUNIK= 2 ORDER BY DATECDE
DAY
La fonction DAY renvoie le jour du mois, c'est-à-dire un nombre compris entre 1 et 31.DAYOFMONTH
La fonction DAYOFMONTH renvoie le jour dans le mois (compris entre 1 et 31).DAYOFWEEK
La fonction DAYOFWEEK renvoie le jour dans la semaine (1 pour Dimanche, 2 pour Lundi, etc.). DAYOFYEAR
La fonction DAYOFYEAR renvoie le jour dans l'année (compris entre 1 et 366). EOMONTH
La fonction EOMONTH renvoie le dernier jour du mois contenant la date spécifiée. Format d'utilisation :
EOMONTH(Date de départ [, Nombre de mois à ajouter ] )
où :
Date de départ : Date dont on veut le dernier jour du mois. Nombre de mois à ajouter : Nombre de mois à ajouter à la date de départ avant de calculer le dernier jour du mois. YEAR et MONTH
Les fonctions YEAR et MONTH permettent d'obtenir respectivement l'année et le mois d'une date. où date correspond :
soit à la date écrite en entier : date du type ANNEE - MOIS - JOUR (AAAMMJJ ou AAAA-MM-JJ) soit à une date partielle. dans ce cas, les informations manquantes sont des 0 soit uniquement une année (ANNEE) soit uniquement l'année et le mois (ANNEE-MOIS) Remarque :
La date se décode de gauche à droite : pour qu'une date soit valide, il faut que toutes les informations disponibles soient valides. Si le format est non correct, la fonction retourne NULL. CURRENT_TIMESTAMP
La fonction CURRENT_TIMESTAMP renvoie l'heure locale du serveur (au format date-heure). Nouveauté SaaS CURRENT_TIME, CURTIME
Les fonctions
CURRENT_TIME et
CURTIME renvoient l'heure courante du serveur.
Nouveauté SaaS HOUR
La fonction
HOUR extrait le nombre d'heures d'une heure ou d'une date-heure.
Nouveauté SaaS MINUTE
La fonction
MINUTE extrait le nombre de minutes d'une heure ou d'une date-heure.
Nouveauté SaaS SECONDE
La fonction
SECONDE extrait le nombre de secondes d'une heure.
Nouveauté SaaS SEC_TO_TIME
La fonction
SEC_TO_TIME convertit un nombre de secondes en durée.
Format d'utilisation :
SEC_TO_TIME(Nombre secondes)
Nouveauté SaaS NOW
La fonction
NOW renvoie l'heure locale du serveur (au format date-heure).
Nouveauté SaaS LOCALTIME, LOCALTIMESTAMP
Les fonctions
LOCALTIME et
LOCALTIMESTAMP renvoient l'heure locale du serveur (au format date-heure).
Format d'utilisation :
LOCALTIME()
Nouveauté SaaS CURRENT_DATE, CURDATE
Les fonctions
CURRENT_DATE et
CURDATE renvoient la date courante du serveur :
CURRENT_DATE : la date renvoyée est au format Date heure. CURDATE : la date renvoyée est au format Date.
GETDATE
La fonction GETDATE renvoie l'heure locale du serveur (au format date-heure). GETUTCDATE
La fonction GETUTCDATE renvoie l'heure UTC du serveur (au format date-heure). DATEADD
La fonction DATEADD ajoute une valeur à la date de départ et renvoie la nouvelle date correspondante. Format d'utilisation :
DATEADD(Partie à ajouter, nombre, date)
où :
Partie à ajouter : Partie de la date à laquelle le nombre doit être ajouté. Ce paramètre peut correspondre à : Partie de la date Abréviations year yy, yyyy quarter qq, q month mm, m dayofyear dy, y day dd, d week wk, ww weekday dw, w hour hh minute mi, n second ss, s millisecond ms microsecond mcs nanosecond ns
Nombre : entier correspondant au nombre d'unités à ajouter. date : date ou date-heure à manipuler. ADDDATE
La fonction
ADDDATE ajoute une valeur (année, mois, etc.) à une date heure de départ. La nouvelle date heure est retournée par la fonction. Si la valeur indiquée est négative, cette valeur sera retranchée à la date de départ sinon elle sera ajoutée.
Format d'utilisation :
ADDDATE(Date, INTERVAL Valeur Unité)
où :
Date : date ou date-heure manipulée. Valeur : Nombre d'éléments à ajouter ou retrancher à la date. Unité : Unité de la valeur ajouter ou retranchée. Ce paramètre peut correspondre aux mots-clés suivants : MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR SECOND_MICROSECOND MINUTE_MICROSECOND MINUTE_SECOND HOUR_MICROSECOND HOUR_SECOND HOUR_MINUTE DAY_MICROSECOND DAY_SECOND DAY_MINUTE DAY_HOUR YEAR_MONTH Exemple : Ajoute 6 heures à la date du rendez-vous prévue :
SELECT ADDDATE ( DateHeureRDVPrévue, INTERVAL 6 HOUR) AS NouvelleDateRDV FROM PlanningInfos
La fonction
SUBDATE retranche une valeur (année, mois, etc.) à une date heure de départ. La nouvelle date heure est retournée par la fonction. Si la valeur indiquée est négative, cette valeur sera ajoutée à la date de départ sinon elle sera retranchée.
Format d'utilisation :
SUBDATE(Date, INTERVAL Valeur Unité)
où :
Date : date ou date-heure manipulée. Valeur : Nombre d'éléments à ajouter ou retrancher à la date. Unité : Unité de la valeur ajouter ou retranchée. Ce paramètre peut correspondre au mot-clé suivants : MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR SECOND_MICROSECOND MINUTE_MICROSECOND MINUTE_SECOND HOUR_MICROSECOND HOUR_SECOND HOUR_MINUTE DAY_MICROSECOND DAY_SECOND DAY_MINUTE DAY_HOUR YEAR_MONTH Exemple : avance de 3 heures la date du rendez-vous prévue :
SELECT SUBDATE ( DateHeureRDVPrévue, INTERVAL 3 HOUR) AS NouvelleDateRDV FROM PlanningInfos
DATEDIFF et DATEDIFF_BIG
La fonction DATEDIFF calcule la différence entre deux dates dans l'unité spécifiée. La valeur de retour est un entier signé. La fonction DATEDIFF_BIG calcule la différence entre deux dates dans l'unité spécifiée. La valeur de retour est un entier très grand signé.
Format d'utilisation :
DATEDIFF(Partie utilisée, Date de départ, Date de fin)
où :
Partie utilisée : Partie de la date sur laquelle le calcul sera effectué. Ce paramètre peut correspondre à : Partie de la date Abréviations year yy, yyyy quarter qq, q month mm, m dayofyear dy, y day dd, d week wk, ww weekday dw, w hour hh minute mi, n second ss, s millisecond ms microsecond mcs nanosecond ns
Date de départ : date ou date-heure de départ du calcul. Date de fin : date ou date-heure de fin du calcul. DATEPART
La fonction DATEPART renvoie l'entier correspondant à la partie demandée de la date-heure spécifiée. Format d'utilisation :
DATEPART(Partie utilisée, date)
où :
Partie utilisée : Partie de la date à extraire. Ce paramètre peut correspondre à : Partie de la date Abréviations year yy, yyyy quarter qq, q month mm, m dayofyear dy, y day dd, d week wk, ww weekday dw, w hour hh minute mi, n second ss, s millisecond ms microsecond mcs nanosecond ns
Date de départ : date ou date-heure manipulée. DATETIMEFROMPARTS
La fonction DATETIMEFROMPARTS renvoie une date-heure correspondant aux éléments spécifiés. Format d'utilisation :
DATETIMEFROMPARTS(Année, Mois, Jours, Heures, Minutes, Secondes, Millisecondes)
DATEFROMPARTS
La fonction DATEFROMPARTS renvoie une date correspondant aux éléments spécifiés. Format d'utilisation :
DATEFROMPARTS(Année, Mois, Jours)
MONTHS_BETWEEN
La fonction
MONTHS_BETWEEN permet d'obtenir le nombre de mois entre deux dates spécifiées.
Format d'utilisation :
MONTHS_BETWEEN(Date1, Date2)
Exemple : Le code SQL suivant permet de sélectionner les commandes entre deux dates :
SELECT DATECDE, MONTHS_BETWEEN ( '20070203' , '20070102' ) AS MB FROM COMMANDE
Exemple : Le code SQL suivant permet de sélectionner les clients selon leur âge :
SELECT CLIENT. IDCLIENT, CLIENT. NOM, CLIENT. PRENOM, CAST ( MONTHS_BETWEEN ( SYSDATE , CLIENT. DATE_NAISSANCE) / 12 AS FLOAT) AS AgeFROM CLIENTWHERE Age > = 18
NEW_TIME
La fonction
NEW_TIME permet d'obtenir une date après conversion de fuseau horaire.
Format d'utilisation :
NEW_TIME(Date, Fuseau Horaire 1, Fuseau Horaire 2)
Exemple :
SELECT NEW_TIME ( '200311010145' , 'AST' , 'MST' ) AS NTI FROM CLIENT
Remarque : Si les fuseaux horaires correspondent à chaîne vide (""), le résultat sera une valeur DateHeure à 0.
NEXT_DAY
La fonction
NEXT_DAY permet d'obtenir le premier jour de la semaine suivant la date spécifiée ou le jour spécifié.
Exemple :
SELECT NEXT_DAY ( '20071007' , 'dimanche' ) AS NXD FROM CLIENT
ROUND
La fonction
ROUND permet d'arrondir la date au format spécifié.
Exemple :
SELECT DATECDE, ROUND ( DATECDE, 'YYYY' ) AS TR FROM COMMANDE
SYSDATE
La fonction
SYSDATE permet d'obtenir la date et l'heure courante.
Exemple :
SELECT SYSDATE AS SY FROM CLIENT WHERE CLCLEUNIK= 1
TRUNC
La fonction
TRUNC permet de tronquer la date au format spécifié.
Format d'utilisation :
Le paramètre "Format" peut correspondre aux valeurs suivantes :
Siècle : "CC" ou "SCC" Année : "Y", "YEAR", "YY", "YYY", "YYYY", "SYEAR", "SYYYY" Année ISO : "I", "IY", "IY", "IYYY" : ISOYear Trimestre : "Q" Mois : "MM", "MON", "MONTH" Premier jour du mois qui est le même jour de la semaine : "W" Premier jour de la semaine : "D", "DAY", "DY" Jour : "DD", "DDD", "J" Heure : "HH", "HH12", "HH24" Minutes : "MI" Exemple :
SELECT DATECDE, TRUNC ( DATECDE) AS TR FROM COMMANDE WHERE COCLEUNIK
ISDATE
La fonction ISDATE permet de savoir si une expression correspond à une date. Cette fonction renvoie : 1 si l'expression correspond à une date ou une date-heure 0 dans les autres cas. Exemple :
SELECT Date, ISDATE ( Date) FROM Commande WHERE IDCommande= 50
COALESCE
La fonction
COALESCE permet de trouver la première expression non nulle parmi ses arguments.
Format d'utilisation :
COALESCE(Param1, Param2, ...)
Exemple :
SELECT COALESCE ( hourly_wage, salary, commission) AS Total_Salary FROM wages
GREATEST GREATEST renvoie la valeur la plus grande des éléments passés en paramètre. Format d'utilisation :
GREATEST(Param1, Param2, ...)
LEAST LEAST renvoie la valeur la plus petite des éléments passés en paramètre. Format d'utilisation :
LEAST(Param1, Param2, ...)
NVL, IFNULL, ISNULL
La fonction
NVL permet de remplacer les valeurs nulles d'une colonne par une valeur de substitution. Les fonctions ISNULL et IFNULL sont identiques.
ISNULL est utilisé en SQL Server et IFNULL avec les bases MySQL ou Progress.
Format d'utilisation :
NVL(Nom Colonne, Valeur de substitution)
Exemple :
SELECT hourly_wage AS R1, NVL ( hourly_wage, 0 ) AS Total FROM wages
NULLIF
La fonction
NULLIF renvoie une valeur NULL si les deux expressions spécifiées sont égales.
Format d'utilisation :
NULLIF(expression1, expression2)
DECODE
La fonction
DECODE permet d'obtenir le fonctionnement d'une instruction SI .. ALORS .. SINON.
Format d'utilisation :
DECODE(Nom_Colonne, Valeur comparée 1, Valeur retournée 1, [Valeur comparée 2, ... Valeur retournée 2][, Valeur par défaut])
Exemple : Selon le client sélectionné, renvoie le nom correspondant à l'identifiant indiqué :
SELECT CLIENT_NOM, DECODE ( CLIENT_ID, 10000 , 'Client 1' , 10001 , 'Client 2' , 10002 , 'Client 3' , 'Autre' ) FROM CLIENT
CASE
La fonction
CASE permet d'obtenir le fonctionnement d'une instruction SI .. ALORS .. SINON.
Format d'utilisation :
CASE Nom_Colonne WHEN Valeur comparée 1 THEN Valeur retournée 1 [WHEN Valeur comparée 2 THEN ... Valeur retournée 2][ELSE Valeur retournée par défaut] END
CASE WHEN Condition 1 THEN Valeur retournée 1 [WHEN Condition 2 THEN Valeur retournée 2] ... [ELSE Valeur retournée par défaut] END
Exemple : Si la rubrique correspond à "3" renvoie "trois", à "4" renvoie "quatre" et renvoie "autre" dans les autres cas :
SELECT rubInt, CASE rubInt WHEN 3 THEN 'trois' WHEN 4 THEN 'quatre' ELSE 'autre' END
SELECT rubInt, CASE WHEN rubInt= 3 THEN 'trois' WHEN rubInt= 4 THEN 'quatre' ELSE 'autre' END
MATCH AGAINST
La fonction
MATCH AGAINST permet d'obtenir l'indice de pertinence de l'enregistrement lors d'une recherche full-text.
Format d'utilisation :
MATCH(Liste des rubriques) AGAINST [ALL] ('Valeur')
Où :
Liste des rubriques correspond à la liste des rubriques de l'index séparées par des virgules (l'ordre des rubriques n'est pas important)Valeur correspond à la valeur recherchée dans les différentes rubriques. Ce paramètre peut correspondre à une valeur littérale ou à un nom de paramètre. La valeur de recherche peut contenir les éléments suivants :Elément Signification Un mot tout seul Le mot indiqué sera recherché. La pertinence sera augmentée si le texte contient ce mot.Exemple : "WINDEV" recherche le mot "WINDEV".
Deux mots séparés par un espace Recherche l'un ou l'autre des mots.Exemple : "WINDEV WEBDEV" recherche les textes contenant soit "WINDEV" soit "WEBDEV".
Un mot précédé du signe "+" Le mot indiqué est obligatoire.Exemple : "+WINDEV" recherche les textes contenant obligatoirement "WINDEV".
Un mot précédé du signe "-" Le mot indiqué ne doit pas être présent dans le texte.Exemple : "-Index" recherche les textes ne contenant pas "Index".
Un mot précédé du signe "~" Si le texte contient le mot indiqué, la pertinence sera diminuée. Un ou plusieurs mots entourés de guillemets Les mots indiqués sont recherchés groupés et dans l'ordre.Attention : si l'option "Ignorer les mots inférieurs à " est différente de 0, les mots entre les guillemets inférieurs à la taille précisée ne seront pas recherchés.
Un mot suivi du signe "*" La recherche est une recherche du type "Commence par" le mot indiqué.
[ALL] permet de forcer le remplacement des espaces par des "+" dans la valeur recherchée.
Exemple : Dans cet exemple, SAI_Rechercher est un champ de saisie et IdUserConnecté est une variable.
MaRequête est une chaîne = [
SELECT * FROM Contact
WHERE MATCH(Contact.Nom, Contact.Prenom, Contact.CommentaireHTML,
Contact.CommentaireTexteBrut, Contact.Commentaires, Contact.Telephone, Contact.Bureau,
Contact.Portable, Contact.Mail, Contact.MSN, Contact.Site_internet, Contact.Pays,
Contact.NumFax, Contact.Ville)AGAINST ('
]
MaRequête = MaRequête + SAI_Rechercher + [
')
AND Contact.IDUtilisateur =
]
MaRequête = MaRequête + IdUserConnecté + [
ORDER BY Nom DESC
]
HExécuteRequêteSQL ( REQ_RECH , hRequêteDéfaut , MaRequête )
POUR TOUT REQ_RECH
TableAjouteLigne ( TABLE_Contact_par_catégorie , ...
REQ_RECH . idcontact , REQ_RECH . IDCategorie , IdUserConnecté , ...
REQ_RECH . Nom , REQ_RECH . Prenom )
FIN
CAS ERREUR :
Erreur ( HErreurInfo ( ) ) Pour plus de détails sur la recherche full-text, consultez
Recherche et index full-text .
MD5 MD5 calcule la somme de vérification MD5 de la chaîne passée en paramètre. La valeur retournée est un entier hexadécimal de 32 caractères qui peut être utilisé par exemple comme clé de hachage. SHA et SHA1 REGEXP ou RLIKE ou ~ ou REGEXP_LIKE Le but des fonctions REGEXP ou RLIKE ou ~ ou REGEXP_LIKE est d'évaluer une expression régulière au sein d'une requête SQL.
REGEXP_LIKE(chaîne, expression)
où :
chaîne correspond à la chaîne que l'on souhaite évaluer. expression correspond à l'expression régulière. Le résultat de la fonction est un booléen :
Vrai si la chaîne correspond à l'expression régulière.Faux si ce n'est pas le cas.Remarque : Dans une expression régulière, le caractère "\" permet de spécifier une mise en forme particulière. Ainsi "\r" correspond à un retour chariot, "\n" à un retour à la ligne...
Exemples : Dans ces exemples, la chaîne 'abcde' est comparée à une expression régulière.
sRequete = "SELECT 'abcde' REGEXP 'a[bcd]{3}e' AS result"
REQ est une Source de Données
HExécuteRequêteSQL ( REQ , hRequêteDéfaut , sRequete )
HLitPremier ( REQ )
soit bResult = REQ . RESULT
sRequete = "SELECT 'abcde' REGEXP 'a[bcd]{2}e' AS result"
HExécuteRequêteSQL ( REQ , hRequêteDéfaut , sRequete )
HLitPremier ( REQ )
bResult = REQ . result
Opérateurs et fonctions sur les bits
Les opérateurs permettant de manipuler les bits sont les suivants : Les fonctions correspondantes sont les suivantes :
BITAND, BITOR, BITXOR, BITNOT, BITANDNOT. Exemple :
sdReq est une Source de Données
sSQL est chaîne = [
SELECT
1 | 2 AS op_or, -- 3 BITOR(1, 2) AS fct_or, -- 3 3 & 6 AS op_and, -- 2 BITAND(3, 6) AS fct_and, -- 2 ~CAST(240 AS TINYINT) AS op_not, -- 15 BITNOT(CAST(240 AS TINYINT)) AS fct_not, -- 15 5 ^ 6 AS op_xor, -- 3 BITXOR(5, 6) AS fct_xor, -- 3 BITANDNOT(3,1) AS fct_andnot, -- 2 1 << 2 AS sl, -- 4 16 >> 2 AS sr -- 4]
HExécuteRequêteSQL ( sdReq , sSQL )
Trace ( "attendu:" )
Trace ( "3 - 3 - 2 - 2 - 15 - 15 - 3 - 3 - 2 - 4 - 4" )
Trace ( "obtenu:" )
POUR TOUT sdReq
Trace ( Remplace ( HEnregistrementVersChaîne ( sdReq ) , TAB , " - " ) )
FIN IS JSON XXX
Les commandes "IS JSON xxx" permettent de savoir si une rubrique est :
un contenu JSON (IS JSON), un contenu JSON représentant un objet (IS JSON OBJECT), un contenu JSON représentant un tableau (IS JSON ARRAY), ... Format d'utilisation :
IS [NOT] JSON [OBJECT|ARRAY|SCALAR|VALUE] (expression)
La valeur renvoyée est 1 si l'expression contient des données valides, 0 dans le cas contraire.
Exemple :
SELECT Produit. Caractéristiques IS JSON AS RubriqueISJSON, Produit. Caractéristiques IS JSON OBJECT AS RubriqueISJSONOBJECT, Produit. Caractéristiques IS JSON ARRAY AS RubriqueISJSONARRAY, Produit. Caractéristiques IS JSON SCALAR AS RubriqueISJSONSCALAR, Produit. Caractéristiques IS JSON VALUE AS RubriqueISJSONVALUEFROM Produit
JSON_VALUE
La commande SQL "JSON_VALUE" permet de récupérer la valeur d'un élément contenu dans la rubrique JSON.Format d'utilisation :
JSON_VALUE(expression, chemin)
expression correspond à une variable contenant du texte JSON chemin correspond à la propriété à extraire. Exemple :
SELECT Produit. Reference, Produit. Libellé, Produit. Caractéristiques, JSON_VALUE ( Produit. Caractéristiques, '$.marque' DEFAULT 'aucune marque' ON ERROR) AS MarqueFROM Produit
JSON_QUERY
La commande SQL "JSON_QUERY" permet de récupérer un objet ou un tableau au format JSON.Format d'utilisation :
JSON_QUERY(expression, chemin)
expression correspond à une variable contenant du JSON chemin spécifie l’objet ou le tableau à extraire. Exemple :
SELECT Produit. Reference, Produit. Libellé, Produit. Caractéristiques, JSON_QUERY ( Produit. Caractéristiques, '$.couleurs' ) AS CouleursFROM Produit
JSON_EXISTS
La commande SQL "JSON_EXISTS" permet de récupérer les enregistrements dont la rubrique JSON contient une donnée. Format d'utilisation :
JSON_EXISTS(expression, filtre)
expression correspond à une variable contenant du JSON filtre correspond aux données à extraire. Exemple :
SELECT Produit. Reference, Produit. Libellé, Produit. CaractéristiquesFROM ProduitWHERE JSON_EXISTS ( Produit. Caractéristiques, '$.talon?(@ == true)' )
JSON_OBJECT
La commande SQL "JSON_OBJECT" permet de récupérer un objet JSON à partir de n'importe quelle rubrique. Le contenu JSON récupéré correspond à un objet. Exemple :
SELECT . IDContact, JSON_OBJECT ( 'NomComplet' : Contact. nom+ ' ' + Contact. prenom, 'Adresse' : Adresse+ ' ' + CodePostal+ ' ' + ville+ ' ' + pays) AS ContenuJSONFROM ContactWHERE IDContact < = 3
JSON_OBJECTAGG
La commande SQL "JSON_OBJECTAGG" permet de récupérer un objet JSON contenant une paire "clé:valeur" pour chaque clé et valeur spécifiques dans un ensemble de valeurs SQL.Exemple :
SELECT JSON_OBJECTAGG ( Contact. nom+ ' ' + Contact. prenom VALUE Adresse+ ' ' + CodePostal+ ' ' + ville+ ' ' + pays) AS ContenuJSONFROM ContactWHERE IDContact < = 3
JSON_ARRAY
La commande SQL "JSON_ARRAY" permet de récupérer un tableau JSON à partir de n'importe quelle rubrique. Le contenu JSON récupéré correspond à un tableau ( [ xxx,...] ). Exemple :
SELECT . IDContact, JSON_ARRAY ( Contact. nom+ ' ' + Contact. prenom) AS ContenuJSONFROM ContactWHERE IDContact < = 3
JSON_ARRAYAGG
La commande SQL "JSON_ARRAYAGG" permet de récupérer un tableau JSON contenant une paire "clé:valeur" pour chaque clé et valeur spécifiques dans un ensemble de valeurs SQL. Le contenu JSON récupéré correspond à un tableau ( [ xxx,...] ). Exemple :
SELECT pays, JSON_ARRAYAGG ( client. nom) FROM client GROUP BY pays - > [ "romaric" , "bob" , "joe" ]
Documentation également disponible pour…