- 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
- GETDATE
- GETUTCDATE
- DATEADD
- 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 :
| | | | - complétion de chaînes :
| | - 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 : chaîne de caractères, entier, réel, caractère, ... Par exemple : 125, 'A', 'Dupont'.
- au nom d'une rubrique.
- au résultat d'une autre fonction SQL.
- Ã un calcul sur une expression. Par exemple : "MaRubrique1+LEN(MaRubrique2)+1"
ELT
La fonction 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
La fonction 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 Formule1
FROM
CODEPOSTAUX
WHERE
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
La fonction 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
LTRIM(Expression initiale)
-- Suppression d'une liste de caractères
LTRIM(Expression initiale, Caractères à supprimer) 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 CLIENT
SELECT 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
RTRIM(Expression initiale)
-- Suppression d'une liste de caractères
RTRIM(Expression initiale, Caractères à supprimer) 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
TRIM(Expression initiale)
-- Suppression d'une chaîne de caractères située en début et en fin de chaîne
TRIM(Expression initiale, Chaîne à supprimer)
-- OU
TRIM(BOTH Chaîne à supprimer FROM Expression initiale)
-- Suppression d'une chaîne de caractères située en début de chaîne
TRIM(LEADING Chaîne à supprimer FROM Expression initiale)
-- Suppression d'une chaîne de caractères située en fin de chaîne
TRIM(TRAILING Chaîne à supprimer FROM Expression initiale) 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
REPLACE(Expression initiale, Chaîne à remplacer, Nouvelle chaîne)
-- Suppression de toutes les occurrences d'un mot
REPLACE(Expression initiale, Chaîne à supprimer) REVERSE
La fonction REVERSE renvoie une chaîne de caractères dont l'ordre des caractères est l'inverse de la chaîne initiale. Format d'utilisation :
REVERSE(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
TRANSLATE(Expression initiale, Caractères à remplacer, Nouveaux 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 JOURSEMAINE
GROUP BY TypeJour Le résultat affiché sera le suivant :
'JourDeSemaine', 'Lundi;Mardi;Mercredi;Jeudi;Vendredi'
'Week-end', 'Samedi;Dimanche' 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) : 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
LPAD(Expression initiale, Longueur)
-- Complétion par un caractère
LPAD(Expression initiale, Longueur, Caractère)
-- Complétion par une chaîne de caractères
LPAD(Expression initiale, Longueur, Chaîne de caractères) REPEAT
La fonction 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
RPAD(Expression initiale, Longueur)
-- Complétion par un caractère
RPAD(Expression initiale, Longueur, Caractère)
-- Complétion par une chaîne de caractères
RPAD(Expression initiale, Longueur, Chaîne de caractères) SPACE
La fonction SPACE renvoie une chaîne constituée de N espaces.
Format d'utilisation :
TO_CHAR
La fonction 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.
Par exemple, 2002 retourne 02; 2000 retourne 20.
- 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).
Exemple : ‘NLS_DATE_LANGUAGE=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).
Exemple : NLS_CURRENCY='$', NLS_NUMERIC_CHARACTERS='dg'
Remarque : Par défaut la langue, la monnaie, et les séparateurs sont déterminés par la nation en cours. CHAR
La fonction CHAR permet de convertir un code ASCII (un entier) en caractère.
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
La fonction CHR permet de convertir un code ASCII (un entier) en caractère.
<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>. 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
La fonction CAST permet de convertir une donnée d'un type dans un autre.
- 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 HFSQL en Android. INITCAP
La fonction 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
La fonction 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
La fonction 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
La fonction 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
La fonction 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)
POSITION(Chaîne à rechercher IN Expression initiale, Position de départ) 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(*)
COUNT(Rubrique)
COUNT(DISTINCT Rubrique) 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 PRODUIT
WHERE 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)
AVG(DISTINCT 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)
MAX(DISTINCT 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)
MIN(DISTINCT 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)
SUM(DISTINCT 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 C
FROM 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,
CLIENT.NomClient
FROM CLIENT, COMMANDE
WHERE CLIENT.NumClient = COMMANDE.NumClient
GROUP BY NomClient
ORDER 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}
Client.IDClient AS IDClient,
Client.Nom AS Nom,
Client.Prénom AS Prénom,
Client.Email AS Email,
Client.PointsFidélités AS PointsFidélités
FROM
Client
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,
CLIENT.NomClient
FROM CLIENT, COMMANDE
WHERE CLIENT.NumClient = COMMANDE.NumClient
GROUP BY NomClient
ORDER 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,
CLIENT.NomClient
FROM CLIENT, COMMANDE
WHERE CLIENT.NumClient = COMMANDE.NumClient
GROUP BY NomClient
ORDER 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
ASCII(Caractère)
-- Code ASCII du premier caractère d'une chaîne
ASCII(Chaîne de caractères) 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
UNICODE(Caractère)
-- Code Unicode du premier caractère d'une chaîne
UNICODE(Chaîne de caractères) 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)
SOUND LIKE(Chaîne) Exemple : Le code SQL suivant permet de lister les clients dont le nom correspond phonétiquement à "Henry" :
SELECT CLIENT.NomClient
FROM CLIENT
WHERE SOUNDEX(CLIENT.NomClient) = SOUNDEX('Henry') SELECT CLIENT.NomClient
FROM 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)
SOUND2 LIKE(Chaîne) Exemple : Le code SQL suivant permet de lister les clients dont la ville correspond phonétiquement à "Montpellier" :
SELECT CLIENT.NomVille
FROM CLIENT
WHERE SOUNDEX2(CLIENT.NomVille) = SOUNDEX2('Montpellier') SELECT CLIENT.NomVille
FROM 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 COMMANDE
WHERE 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). 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.
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, 6 INTERVAL 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, 3 INTERVAL 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)
DATEDIFF_BIG(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 Age
FROM
CLIENT
WHERE
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
La fonction GREATEST renvoie la valeur la plus grande des éléments passés en paramètre. Format d'utilisation :
GREATEST(Param1, Param2, ...) LEAST
La fonction 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
La fonction 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
Les fonctions SHA et SHA1 calculent la somme de vérification SHA1 160 bits de la chaîne passée en paramètres selon la norme RFC 3174 (Secure Hash Algorithm). La valeur retournée est une chaîne hexadécimale de 40 caractères ou bien NULL si l'argument est NULL. Une des possibilités d'utilisation de cette fonction est le hachage de clé. Format d'utilisation :
SHA(Chaîne)
SHA1(Chaîne) 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. Format d'utilisation :
chaîne REGEXP expression 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 RubriqueISJSONVALUE
FROM
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 Marque
FROM
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 Couleurs
FROM
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éristiques
FROM
Produit
WHERE
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
Contact.IDContact,
JSON_OBJECT('NomComplet' : Contact.nom+' '+Contact.prenom,
'Adresse' : Adresse+' '+CodePostal+' '+ville+' '+pays)
AS ContenuJSON
FROM
Contact
WHERE
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 ContenuJSON
FROM
Contact
WHERE
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
Contact.IDContact,
JSON_ARRAY(Contact.nom+' '+Contact.prenom) AS ContenuJSON
FROM
Contact
WHERE
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…
|
|
|