|
|
- Overview
- SQL functions
- Scalar expression
- LEFT
- RIGHT
- MID, SUBSTR and 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 and MONTH
- CURRENT_TIMESTAMP
- GETDATE
- GETUTCDATE
- DATEADD
- DATEDIFF and DATEDIFF_BIG
- DATEPART
- DATETIMEFROMPARTS
- DATEFROMPARTS
- MONTHS_BETWEEN
- NEW_TIME
- NEXT_DAY
- ROUND
- SYSDATE
- TRUNC
- ISDATE
- COALESCE
- NVL, IFNULL, ISNULL
- NULLIF
- DECODE
- CASE
- MATCH AGAINST
- Bitwise operators and functions
- IS JSON XXX
- JSON_VALUE
- JSON_QUERY
- JSON_EXISTS
- JSON_OBJECT
- JSON_OBJECTAGG
- JSON_ARRAY
- JSON_ARRAYAGG
SQL functions that can be used in SQL queries
The following SQL functions can be used on the queries written in SQL code (classified by theme): | | | | | | | | | | | | | | - change the case of a string:
| | | - LEN and LENGTH
- CHARACTER_LENGTH, CHAR_LENGTH and BYTE_LENGTH
| - position of character string:
| | - number of records in a file:
| | - calculate numeric values:
| See the mathematical SQL functions. | - select the first or last n records n records:
| | | | | | | | | | | | | | | | | | | | - Bitwise functions and operators
| | | |
For more details on SQL functions, please refer to a specific SQL documentation. Remarks: - These statements can be used:
- in the SQL code of queries created in the query editor. Then, these queries will be run by HExecuteQuery.
- in the SQL code of queries run by HExecuteSQLQuery.
- Unless stated otherwise, these functions can be used with all the types of data sources (Oracle, Access, SQL Server, and so on).
Scalar expression Each parameter passed to one of these SQL functions corresponds to an expression (called "scalar expression"). An expression can correspond to: - a constant: character string, integer, real, character, ... For example: 125, 'A', 'Doe'.
- an item name.
- the result of another SQL function.
- a calculation on an expression. For example: "MyItem1+LEN(MyItem2)+1"
ELT
ELT returns the nth character string found in a list of strings.Format:
ELT(Numéro de la chaîne, Chaîne1, Chaîne2, Chaîne3, ...)
Example: The following SQL code is used to select the first string of the list:
SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo')
EXTRACTVALUE
EXTRACTVALUE is used to handle XML strings. This function returns the text (CDATA) of the first text node that is a child of the element corresponding to the XPATH expression. If several correspondences are found, the content of the first child text node of each node corresponding to the XPATH expression is returned in the format of a string delimited by space characters. Format:
EXTRACTVALUE(Fragment de XML, Expression XPATH)
<XML fragment> must be a valid XML fragment. It must contain a unique root. Example: The following code is used to count the elements found:
SELECT EXTRACTVALUE('<a><b/></a>', 'count(/a/b)')
FROM CLIENT
WHERE CLCLEUNIK=1
LEFT LEFT extracts the left part (which means the first characters) of an expression.
Format:
LEFT(Expression initiale, Nombre de caractères à extraire)
Example: The following SQL code is used to list the states of the customers:
SELECT LEFT(CodePostal, 2)
FROM CLIENT
RIGHT RIGHT extracts the right part (which means the last characters) of an expression. Format:
RIGHT(Expression initiale, Nombre de caractères à extraire)
Example: The following SQL code is used to extract the last five characters from the name of the customers:
SELECT RIGHT(Nom, 5)
FROM CLIENT
MID, SUBSTR and SUBSTRING MID, SUBSTR and SUBSTRING are used to extract a substring found in the content of an expression from a given position. If the given position corresponds to: - a negative number, the extraction will start from the end of string.
- 0, the extraction will start from the beginning of the string (equivalent to position 1).
If the absolute value of the given position (returned by ABS) is greater than the number of characters found in the initial expression, an empty string is returned. Example: The following SQL code is used to extract the cities whose second character is '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 MID can only be used on an Access data source. Format:
MID(Expression initiale, Position de départ, Nombre de caractères à extraire)
Example: The following SQL code is used to extract the 3rd and 4th characters from the name of the customers:
SELECT MID(Nom, 3, 2)
FROM CLIENT
SUBSTR SUBSTR can only be used on an Oracle, HFSQL Classic or HFSQL Client/Server data source. Format:
SUBSTR(Expression initiale, Position de départ, Nombre de caractères à extraire)
Example: The following SQL code is used to extract the 3rd and 4th characters from the name of the customers:
SELECT SUBSTR(Nom, 3, 2)
FROM CLIENT
SPLIT_PART
SPLIT_PART divides a character string according to the specified separator and returns the nth part of the string.Format:
SPLIT_PART(Expression initiale, Délimiteur, Numéro de la partie à extraire)
Example: The following SQL code is used to extract the first 3 words corresponding to the address:
SELECT SPLIT_PART(ADRESSE,' ',1), SPLIT_PART(ADRESSE,' ',2),SPLIT_PART(ADRESSE,' ',3)
FROM CLIENT
WHERE CLCLEUNIK=2
BTRIM BTRIM deletes a character string found at the beginning or at the end of a string. Format:
BTRIM(<Chaîne d'origine>, [<Chaîne à supprimer>])
Example: Delete the 'AB' string from the 'ABRACADABRA' string
BTRIM('ABRACADABRA','AB')
In this example, the result is 'RCDR'. LTRIM LTRIM returns a character string: - without space characters on the left.
- without a list of characters.
The characters are deleted from left to right. This deletion is case sensitive (lowercase/uppercase characters). This deletion stops when a character that does not belong to the specified list is found. The deletions of specific characters cannot be performed on an Access or SQL Server data source. Format:
-- Suppression des espaces situés à gauche
LTRIM(Expression initiale)
-- Suppression d'une liste de caractères
LTRIM(Expression initiale, Caractères à supprimer)
Example: The name of the customers is preceded by the title of the customers ("Mr.", "Mrs." or "Ms."). The following SQL code is used to: - delete the title from each name (the letters "M", "r", and "s" as well as the dot character).
- delete the space character found in front of the name (space character that was found between the title and the name).
SELECT LTRIM(Nom, 'Mrmel.')
FROM CLIENT
SELECT LTRIM(Nom)
FROM CLIENT
In this example: | | | If the name of the customer is: | The returned string is: |
|---|
| 'Ms. DOE' | 'DOE' | | 'Mr. CLARK' | 'CLARK' | | 'Mrs. Davis' | 'Davis' |
RTRIM RTRIM returns a character string: - without space characters on the right.
- without a list of characters.
The characters are deleted from right to left. This deletion is case sensitive (lowercase/uppercase characters). This deletion stops when a character that does not belong to the specified list is found. The deletions of specific characters cannot be performed on an Access or SQL Server data source. Format:
-- Suppression des espaces situés à droite
RTRIM(Expression initiale)
-- Suppression d'une liste de caractères
RTRIM(Expression initiale, Caractères à supprimer)
Example: The following SQL code is used to delete the 'E', 'U' and 'R' characters found on the right of the customer names:
SELECT RTRIM(Nom, 'EUR')
FROM CLIENT
In this example: | | | If the name of the customer is: | The returned string is: |
|---|
| 'DUVALEUR' | 'DUVAL' | | 'DRAFUREUR' | 'DRAF' | | 'Galteur' | 'Galteur' | | 'FOURMALTE' | 'FOURMALTE' | | 'BENUR' | 'BEN' |
TRIM TRIM returns a character string: - without space characters on the left and on the right.
- without a character string found at the beginning and at the end of string.
- without a character string found at the beginning of string.
- without a character string found at the end of string.
The characters are deleted from right to left. This deletion is case sensitive (lowercase/uppercase characters). This deletion stops when a character that does not belong to the specified string is found. Format:
-- 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 OVERLAY replaces a string with another one. Format:
OVERLAY(<Chaîne d'origine> PLACING <Chaîne à remplacer> FROM <Position de début> [FOR <Longueur>])
Example: The following SQL code is used to replace "Green" by "Red":
SELECT OVERLAY('Pomme verte' PLACING 'rouge' FROM 7) FROM Produit
REPLACE REPLACE returns a character string: - by replacing all the occurrences of a word found in a string by another word.
- by replacing all occurrences of a word found in a string.
The replacement is performed from right to left. This replacement is case sensitive (uppercase/lowercase characters). This replacement stops when a character that does not belong to the specified string is found. Format:
-- 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
REVERSE returns a character string in which the order of characters is the reversed order of the initial string. Format: TRANSLATE TRANSLATE returns a character string by replacing all the specified characters by other characters. If a character to replace has no corresponding character, this character is deleted. The replacement is performed from right to left. This replacement is case sensitive (uppercase/lowercase characters). Format:
-- Remplacement des caractères
TRANSLATE(Expression initiale, Caractères à remplacer, Nouveaux caractères)
Example: The following SQL code is used to replace: - the "é" character by the "e" character.
- the "è" character by the "e" character.
- the "à" character by the "a" character.
- the "ù" character by the "u" character.
SELECT TRANSLATE(MonChamp, 'éèàù', 'eeau')
FROM MaTable
CONCAT CONCAT concatenates several strings together. Format:
CONCAT(Chaîne 1, Chaîne 2 [, ..., Chaîne N])
 New in version 28GROUP_CONCAT The GROUP_CONCAT clause allows you to group in the same string the different non-zero values of a field from a series of records. Each value can be separated by a particular character (a space, a ";", etc.). This function is useful for example to group results into one row. If a GROUP BY clause is used, the grouped values can be assembled on a different criteria. Format:
GROUP_CONCAT(<Nom colonne> SEPARATOR <Caractère de séparation>)
where: - <Column Name> represents the field to be grouped.
- <Separator character> represents the character that will separate each value.
Example: The following SQL code is used to retrieve the list of weekdays and weekend days on an row. Days are separated by a semicolon. The table "WEEKLY DAY" handled is as follows: | | | TypeJour | NomJour |
|---|
| Week | Monday | | Week | Tuesday | | Week | Wednesday | | Week | Thursday | | Week | Friday | | Weekend | Saturday | | Weekend | Sunday |
SELECT TypeJour, GROUP_CONCAT(NomJour SEPARATOR ';')
FROM JOURSEMAINE
GROUP BY TypeJour
The result will be displayed as follows:
'Semaine', 'Lundi;Mardi;Mercredi;Jeudi;Vendredi'
'Week-end', 'Samedi;Dimanche'
STRING_AGG STRING_AGG is used to concatenate non-null strings from a list of values. Format:
STRING_AGG(chaîne, séparateur)
Example: The following code returns in a single string the list of delivery modes separated by ";".
SELECT STRING_AGG(libelle,';') AS str FROM livraison
Content of delivery file: - Carrier
- Express delivery
- Recommended
- Pick up
Result returned by the STRING_AGG function: "Shipping company;Express delivery;Certified;Pick up". LPAD LPAD returns a string whose size is defined. To reach the requested size, the string is left-padded: - by space characters.
- by a character or by a given string.
Format:
-- 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
REPEAT returns a character string containing n times the repetition of the initial string. - If n is less than or equal to 0, the function returns an empty string.
- If the initial string or n is NULL, the function returns NULL.
Format:
REPEAT(Chaîne initiale, n)
Example: The following code is used to repeat the name of the contact 3 times:
SELECT REPEAT(NOMCONTACT,14)
FROM CLIENT
WHERE CLCLEUNIK=10
RPAD RPAD returns a string whose size is defined. To reach the requested size, the string is right-padded: - by space characters.
- by a character or by a given string.
Format:
-- 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
SPACE returns a string containing N space characters.
Format: TO_CHAR
TO_CHAR is used to convert into character string: - a datetime,
- a numeric value.
Format: 1. Convert a datetime value:
TO_CHAR(<Valeur dateheure>, <Format dateheure> [, <Options dateheure>])
In this syntax: - <DateTime format> can correspond to one of the following elements:
- "-", "/", ",", ".", ";", ":"
- "text": punctuation characters (separators) for a date and/or for a time.
- AD, A.D.: Indicator of AD era for a date (After Jesus-Christ)
- AM, A.M.: Indicator of AM meridian for the time (Ante Meridian)
- BC, B.C.: Indicator of BC era for a date (Before Jesus-Christ)
- CC or SCC Century:
- If the last two digits of the century on 4 digits are included between 01 and 99 (inclusive), the century is represented by the last 2 digits of the year.
- If the last two digits of the century on 4 digits are 00, the century is represented by the first 2 digits of the year.
For example, 2002 return 02 ; 2000 returns 20.
- D: Number of the day in the week (1-7).
- DAY: Day in letters.
- DD: Number of the day in the month (1-31).
- DDD: Number of the day in the year (1-366).
- DY: Abbreviated day in letters
- FF [1..9]: Fractions of seconds. The digit represents the number of digits representing the fraction of second. Examples:
- 'HH:MI:SS.FF'
- SELECT TO_CHAR(SYSTIMESTAMP, 'SS.FF3') from dual;
- FM: Removes the space characters on the right and on the left.
- HH: hour of the day (1-12).
- HH12: hour of the day (1-12).
- HH24: hour of the day (0-23).
- IW: Number of the week in the year (1-52 or 1-53) according to the ISO standard.
- IYY, IY, I: represents the last 3, 2, or 1 digits of the year in ISO format.
- IYYY: represents the 4 digits of the year.
- J: Day in the Julian calendar. The number of days since January 1st, 4712 BC.
- MI: Minutes (0-59).
- MM: Month (01-12 ; January = 01).
- MON: Abbreviated month.
- MONTH: Full month in letters.
- PM, P.M.: Indicator of PM meridian for the time (Post Meridian).
- Q: Quarter (1, 2, 3, 4 ; January - March = 1).
- RM: Month in Roman numbers (I-XII ; January = I).
- SS: Seconds (0-59).
- SSSSS: Number of seconds passed since midnight (0-86399).
- WW: Number of the week (1-53) with week 1 starting from the first day of the year.
- W: Number of the week in the month (1-5) with week 1 starting from the first day of the month.
- X: Separator for the fraction of seconds. Example: 'HH:MI:SSXFF'.
- Y,YYY: Year with a comma. The comma is used to separate the grouping of the local.
- YEAR, SYEAR: Year S means BC, Year signed with a minus sign.
- YYYY, SYYYY: Year on 4 digits ; S means BC, Year signed with a minus sign.
- YYY, YY, Y: Last number of the year 3, 2, or 1 number.
- The <datetime options> parameter is a character string containing the following keywords:
- "NLS_DATE_LANGUAGE=language in en"
- "NLS_NUMERIC_CHARACTERS ='dg'": 'dg' is a 2-character string whose first one corresponds to the decimal separator and whose second one corresponds to the group separator (between thousand and hundred for example).
Example: ‘NLS_DATE_LANGUAGE=french, NLS_NUMERIC_CHARACTERS =, ‘
2. Converting a numeric value:
TO_CHAR(<Valeur numérique>, <Format numérique> [, <Options numérique>])
- The <Numeric format> parameter can correspond to one of the following elements:
- , (comma). Position a comma at the specified location. Example: 9,999
- . (point). Position a point at the specified location. Example: 99.99
- 0. Fills with zeros before or after. Example: 0999 or 9990
- 9. Represents digits. Example: 9999
- B. Replaces zeros by spaces. Example: B9999
- C. Position the currency symbol according to the ISO standard when the NLS_ISO_CURRENCY parameter is used. Example: C999
- D. Indicates the position of the decimal separator when the NLS_NUMERIC_CHARACTER parameter is used. The (.) is the default separator. Example: 99D99
- EEEE. Returns a value in scientific format. Example: 9.9EEEE
- G. Indicates the thousand separator when the NLS_NUMERIC_CHARACTER parameter is used. You can specify several thousands separators. Example: 9G999
- L. Indicates the position of the currency symbol when the NLS_ISO_CURRENCY parameter is used. Example: L999
- MI. Places the - sign after negative values. Example: 9999MI
- PR. Encloses the negative values in angular brackets. Example: 9999PR
- rn or RN. Returns the value in uppercase or lowercase roman numeric.
- S. Indicates the +/- sign Positive or Negative. Example: S9999
- U. Indicates the Euro currency symbol when the NLS_DUAL_CURRENCY parameter is used. Example: U9999
- V. Returns the value in power of 10. Example 999V99
- X. Returns the value in hexadecimal format. Example: XXXX
- In this syntax, <Numeric options> is a character string containing the following keywords:
- "NLS_CURRENCY='currency in us'"
- "NLS_NUMERIC_CHARACTERS ='dg'": 'dg' is a 2-character string whose first one corresponds to the decimal separator and whose second one corresponds to the group separator (between thousand and hundred for example).
Example: NLS_CURRENCY='$', NLS_NUMERIC_CHARACTERS='dg'
Remark: By default, the language, the currency and the separators are defined by the current nation. CHAR
CHAR is used to convert an ASCII code (integer) into character.
Format:
<ASCII code> is a numeric and corresponds to the ASCII character to convert, between 0 and 255. Otherwise, the character returned by the function is NULL. The result of the function is the character corresponding to the ASCII code of <ASCII code>. Remark: The result depends on the current character set. CHR
CHR is used to convert an ASCII code (integer) into character.
<ASCII code> is a numeric and corresponds to the ASCII character to convert, between 0 and 255. Otherwise, the character returned by the function is NULL. The result of the function is the character corresponding to the ASCII code of <ASCII code>. Remarks: - The result depends on the current character set.
- In UTF8, the integer sent is interpreted as a "code point" ; otherwise, the character returned corresponds to the character modulo 256.
CAST
CAST is used to convert a data from a type to another one.- <Expression> represents the value to convert.
- <Type> represents the new type into which the expression is converted. The available types are:
| | | CHARACTER | Character string | | CHARACTER(Size) | String on size | | VARCHAR(Size) | String on size | | CHARACTER VARYING(Size) | String on size | | CHAR VARYING(Size) | String on size | | NVARCHAR(Size) | Unicode string on size | | VARCHAR(Size) BINARY | Binary string on size | | BINARY(Size) | Binary string on size | | VARBINARY(Size) | Binary string on size | | BLOB | Binary memo | | CLOB | Text memo | | TEXT | Text memo | | NCLOB | Unicode memo | | NTEXT | Unicode memo | | NUMBER(Precision) | entier | | NUMBER(Precision, scale) | entier | | DECIMAL(Precision) | Real | | DECIMAL(Precision, scale) | Real | | TINYINT UNSIGNED | Unsigned 1-byte integer | | SMALLINT UNSIGNED | Unsigned 2-byte integer | | INTEGER UNSIGNED | Unsigned 4-byte integer | | BIGINT UNSIGNED | Unsigned 8-byte integer | | TINYINT | Signed 1-byte integer | | SMALLINT | Signed 2-byte integer | | INTEGER | Signed 4-byte integer | | BIGINT | Signed 8-byte integer | | FLOAT | 4-byte real | | REAL | 8-byte real | | DOUBLE PRECISION | 8-byte real | | MONEY | Currency | | DATE | DATE | | DATETIME | Date time | | TIME | Time of day |
The result of the function is the converted value.Example:
This code returns: "126". CONVERT
CONVERT is used to convert a character string from a character set to another one. Format:
CONVERT(Texte à convertir, alphabet utilisé, nouvel alphabet)
Example: Converting a string from UTF-8 to LATIN1:
SELECT CONVERT('texte en utf8', 'UTF8', 'LATIN1')
Remark: This function is not available for the SQL queries run on HFSQL files in Android. INITCAP
INITCAP returns a character string where the first letter of each word is written in uppercase character and all the other letters are written in lowercase characters.
Format:Example:
This code returns: 'Great Weather Today'. LOWER LOWER converts an expression to lowercase. LOWER cannot be used on an Access data source. Format:
LOWER(Expression initiale)
Example: The following SQL code is used to convert the first name of the customers into lowercase characters:
SELECT LOWER(Prénom)
FROM CLIENT
UPPER UPPER converts an expression to uppercase. UPPER cannot be used on an Access data source. Format:
UPPER(Expression initiale)
Example: The following SQL code is used to convert the cities of customers into uppercase characters:
SELECT UPPER(Ville)
FROM CLIENT
LCASE
LCASE returns a string with all the characters in lowercase according to the current character set.Format:
LCASE(Expression initiale)
Example: The following SQL code is used to convert the cities of customers into lowercase characters:
SELECT LCASE(Ville)
FROM CLIENT
UCASE
UCASE returns a string with all the characters in uppercase according to the current set of characters.Format:
UCASE(Expression initiale)
Example: The following SQL code is used to convert the cities of customers into uppercase characters:
SELECT UCASE(Ville)
FROM CLIENT
LEN/LENGTH LEN and LENGTH return the size (the number of characters) of an expression. This size includes all the characters, including the space characters and the binary 0. LEN LEN can be used on all the types of data sources excluding the Oracle data sources. For the Oracle data sources, use LENGTH. Format: Example: The following SQL code is used to find out the size of the customer names:
SELECT LEN(Nom)
FROM CLIENT
LENGTH LENGTH can only be used on an Oracle data source. Format:
LENGTH(Expression initiale)
Example: The following SQL code is used to find out the size of the customer names:
SELECT LENGTH(Nom)
FROM CLIENT
INSTR INSTR returns the position of a character string in an expression. INSTR can only be used on an Oracle data source or on a data source supporting the SQL-92 standard. Format:
INSTR(Expression initiale, Chaîne à rechercher, Position de départ, Occurrence)
Example: The following SQL code is used to find out the position of the first occurrence of the letter "T" in the cities of the customers:
SELECT INSTR(Ville, 'T', 1, 1)
FROM CLIENT
FIELD
FIELD returns the index of the sought string in the list.If the string is not found, the function returns 0. Format:
FIELD(Chaîne à rechercher, Chaîne1, Chaîne2, ...)
FIND_IN_SET
FIND_IN_SET returns the position of a string in a list of values.If the string is not found, the function returns 0. Format:
FIND_IN_SET(<Chaîne à rechercher>, <Liste de valeurs>)
The <List of values> parameter corresponds to a character string containing the values separated by a comma. Example: The following code returns 3:
FIND_IN_SET('Rouge','Bleu,Jaune,Rouge,Vert')
PATINDEX PATINDEX returns the position of the first occurrence of a character string corresponding to a specified value (with generic characters). The authorized wildcard characters are: - '%': represents zero, one or more characters.
- '_': represents a single character.
These generic characters can be combined. PATINDEX can be used on an HFSQL Classic or SQL Server data source. Format:
PATINDEX(Valeur à rechercher, Expression)
Example: The table below presents the position of the first occurrence found according to the sought values: | | | | | Sought value |
|---|
| City name | '%E%' | '%E_' | '%AR%' | | MONTPELLIER | 6 | 10 | 0 | | PARIS | 0 | 0 | 2 | | TARBES | 5 | 5 | 2 | | TOULOUSE | 8 | 0 | 0 | | VIENNE | 3 | 0 | 0 |
POSITION POSITION returns the position of a character string in an expression. Format:
POSITION(Chaîne à rechercher IN Expression initiale)
POSITION(Chaîne à rechercher IN Expression initiale, Position de départ)
Example:
TestREQ is Data Source
sCodeSQL is string = [
SELECT POSITION( 'No' IN Nom ) As PosNom
FROM cooperateur
LIMIT 0 , 30
]
IF NOT HExecuteSQLQuery(TestREQ, MaConnexion, hQueryWithoutCorrection, sCodeSQL) THEN
Error(HErrorInfo())
END
FOR EACH TestREQ
Trace(TestREQ.PosNom)
END
COUNT COUNT returns: - the number of records selected in a file.
- the number of non-null values of an item.
- the number of different values and non-null values of an item.
Format:
COUNT(*)
COUNT(Rubrique)
COUNT(DISTINCT Rubrique)
Examples: - The following SQL code is used to find out the number of products found in the Product file:
SELECT COUNT(*)
FROM PRODUIT
- The following SQL code is used to find out the number of products onto which a VAT rate of 5.5 % is applied:
SELECT COUNT(TauxTVA)
FROM PRODUIT
WHERE TauxTVA = '5.5'
- The following SQL code is used to find out the number of different and non-null VAT rates:
SELECT COUNT(DISTINCT PRODUIT.TauxTVA)
FROM PRODUIT
AVG AVG calculates: - the average of a set of non-null values.
- the average of a set of different and non-null values.
Format:
AVG(Rubrique)
AVG(DISTINCT Rubrique)
Examples: - The following SQL code is used to find out the average salary of the employees:
SELECT AVG(Salaire)
FROM EMPLOYE
- The following SQL code is used to find out the average of the different salaries of the employees:
SELECT AVG(DISTINCT Salaire)
FROM EMPLOYE
MAX MAX returns the greatest of the values found in an item for all the records selected in the file. MAX used in a query without grouping must return a single record. If the query contains groupings, a record will be returned for each grouping. If the data source contains records, the record returned by the query will contain the maximum value. If the data source contains no record, the value of MAX in the record returned is NULL. Format:
MAX(Rubrique)
MAX(DISTINCT Rubrique)
Example: The following SQL code is used to find out the maximum salary of employees:
SELECT MAX(Salaire)
FROM EMPLOYE
MIN MIN returns the lowest of the non-null values found in an item for all the records selected in the file. Format:
MIN(Rubrique)
MIN(DISTINCT Rubrique)
Example: The following SQL code is used to find out the minimum salary of employees:
SELECT MIN(Salaire)
FROM EMPLOYE
SUM SUM returns: - the sum of the non-null values found in an item for all the records selected in the file.
- the sum of the different and non-null values found in an item for all the records selected in the file.
Format:
SUM(Rubrique)
SUM(DISTINCT Rubrique)
Examples: - The following SQL code is used to find out the total sum of salaries:
SELECT SUM(Salaire)
FROM EMPLOYE
- The following SQL code is used to find out the total sum of different salaries:
SELECT SUM(DISTINCT Salaire)
FROM EMPLOYE
Remark: The item handled by SUM must not correspond to the result of an operation. Therefore, the following syntax generates an error:
SELECT (A*B) AS C, SUM C
FROM MONFICHIER
This syntax must be replaced with the following syntax:
SELECT (A*B) AS C, SUM(A*B)
FROM MONFICHIER
EVERY EVERY is an aggregate function (like SUM for example), which means that the functions acts on a group of data and returns a value. EVERY returns: - True if all the received arguments are checked and true.
- False if at least one of the arguments is not checked.
Format:
EVERY(Expression 1, Expression 2, ..., Expression N)
Example:
The following SQL code is used to get the list of companies with employees whose salary is greater than 10000:
SELECT societe.nom, EVERY(employe.salaire > 10000) AS riche
FROM societe NATURAL JOIN employe GROUP BY societe.nom
TOP TOP returns the first n records of the query result. TOP cannot be used on an Oracle or PostgreSQL data source. Format:
TOP Nombre du dernier enregistrement sélectionné
Example: The following SQL code is used to list the 10 best customers:
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
Remark: - We recommend that you use TOP on a sorted query. Otherwise, the records returned by TOP will be selected according to their record number.
- You have the ability to pass a parameter to TOP. The parameter can be:
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 BOTTOM returns the last n records found in the result of a query. BOTTOM can only be used on an HFSQL data source. Format:
BOTTOM Nombre du dernier enregistrement sélectionné
Example: The following SQL code is used to list the 10 worst customers:
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
Remark: - It is recommend to use BOTTOM on a sorted query. Otherwise, the records returned by BOTTOM will be selected according to their record number.
- You have the ability to pass a parameter to BOTTOM. The parameter can be:
LIMIT LIMIT returns the first n records of the query result. LIMIT cannot be used on an Oracle or PostgreSQL data source. Format:
LIMIT Nombre du dernier enregistrement sélectionné
Example: The following SQL code is used to list the 10 best customers:
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
Remark: - It is not recommended to use LIMIT on a sorted query. Otherwise, the records returned by TOP will be selected according to their record number.
- You have the ability to pass a parameter to LIMIT. The parameter can be:
ASCII ASCII returns the ASCII code: - of a character.
- of first character found in a string.
If the character or the character string corresponds to an empty string (""), ASCII returns 0. Format:
-- 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 UNICODE returns the integer value defined by the Unicode standard: - of a character.
- of first character found in a string.
Format:
-- 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 CURRENT_USER returns the username for the current connection. Format: Example: The following code updates the author of the modification performed in CUSTOMER table:
UPDATE CLIENT SET USER=CURRENT_USER() WHERE IDCLIENT=1
SYSTEM_USER SYSTEM_USER returns the username for the current connection. Format: Example: The following code updates the author of the modification performed in CUSTOMER table:
UPDATE CLIENT SET USER=SYSTEM_USER() WHERE IDCLIENT=1
USER_NAME USER_NAME returns the username for the current connection. Format: Example: The following code updates the author of the modification performed in CUSTOMER table:
UPDATE CLIENT SET USER=USER_NAME() WHERE IDCLIENT=1
SOUNDEX, SOUND LIKE SOUNDEX and SOUND LIKE return the phonetic representation of a character string (based on an English algorithm). Format:
SOUNDEX(Chaîne)
SOUND LIKE(Chaîne)
Example: The following SQL code is used to list the customers whose name phonetically corresponds to "Henry":
SELECT CLIENT.NomClient
FROM CLIENT
WHERE SOUNDEX(CLIENT.NomClient) = SOUNDEX('Henry')
SELECT CLIENT.NomClient
FROM CLIENT
WHERE CLIENT.NomClient SOUND LIKE 'Henry'
Remark: SOUNDEX used on different databases (HFSQL, Oracle, MySQL, ...) may return different results according to the database used. SOUNDEX2, SOUND2 LIKE SOUNDEX2 and SOUND2 LIKE return the phonetic representation of a character string (based on an algorithm close to French). Format:
SOUNDEX2(Chaîne)
SOUND2 LIKE(Chaîne)
Example: The following SQL code is used to list the customers whose city phonetically corresponds to "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 ADD_MONTHS is used to add several months to a specified date. Format:
ADD_MONTHS(Date,Nombre de mois)
Example: The following SQL code is used to select the orders placed in April 2003.
SELECT DATECDE,
ADD_MONTHS('20070203',2) AS AM
FROM COMMANDE
LAST_DAY LAST_DAY is used to find out the date of the last day for the specified month. Format: Example: The following SQL code is used to select the orders placed in February 2008:
SELECT LAST_DAY('20080203') AS LD,
DATECDE
FROM COMMANDE
WHERE COMMANDE.CLCLEUNIK=2 ORDER BY DATECDE
DAY DAY returns the day of the month, which means a number included between 1 and 31. Format: DAYOFMONTH DAYOFMONTH returns the day in the month (included between 1 and 31). Format: DAYOFWEEK DAYOFWEEK returns the day in the week (1 for Sunday, 2 for Monday, etc.). Format: DAYOFYEAR DAYOFYEAR returns the day in the year (included between 1 and 366). Format: EOMONTH EOMONTH returns the last day of the month of the specified date. Format:
EOMONTH(Date de départ [, Nombre de mois à ajouter ] )
where: - Start date: Date for which to return the last day of the month.
- Number of months to add: Number of months to add to the start date before calculating the last day of the month.
YEAR and MONTH YEAR and MONTH get the year and the month of a date, respectively. Format: where date corresponds to: - the date written out: YEAR - MONTH - DAY (YYYYMMDD or YYYY-MM-DD)
- a partial date. in this case, missing data is represented by 0
- only a year (YEAR)
- only a year and a month (YEAR-MONTH)
Remark: - Dates are decoded from left to right: for a date to be valid, all available information must be valid.
- If the format is invalid, the function returns NULL.
CURRENT_TIMESTAMP CURRENT_TIMESTAMP returns the local time of server (in datetime format). Format: GETDATE GETDATE returns the local time of server (in datetime format). Format: GETUTCDATE GETUTCDATE returns the UTC time of server (in datetime format). Format: DATEADD DATEADD adds a value to the start date and returns the new date. Format:
DATEADD(Partie à ajouter, nombre, date )
where: - Part to add: Part of the date to which the number will be added. This parameter can be:
| | | Date part | Abbreviations |
|---|
| 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 |
- Number: integer corresponding to the number of units to be added.
- date: date or datetime to be used.
DATEDIFF and DATEDIFF_BIG DATEDIFF calculates the difference between two dates in a given unit. The return value is a signed integer. DATEDIFF_BIG calculates the difference between two dates in a given unit. The return value is a signed big integer. Format:
DATEDIFF(Partie utilisée, Date de départ, Date de fin)
DATEDIFF_BIG(Partie utilisée, Date de départ, Date de fin)
where: - Part used: Part of the date on which the calculation will be performed. This parameter can be:
| | | Date part | Abbreviations |
|---|
| 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 |
- Start date: start date or datetime for the calculation.
- End date: end date or datetime for the calculation.
DATEPART DATEPART returns the integer corresponding to the requested part of the specified datetime. Format:
DATEPART(Partie utilisée, date)
where: - Part used: Part of the date to be extracted. This parameter can be:
| | | Date part | Abbreviations |
|---|
| 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 |
- Start date: date or datetime used.
DATETIMEFROMPARTS DATETIMEFROMPARTS returns a datetime value that corresponds to the specified elements. Format:
DATETIMEFROMPARTS(Année, Mois, Jours, Heures, Minutes, Secondes, Millisecondes)
DATEFROMPARTS DATEFROMPARTS returns a date value that corresponds to the specified elements. Format:
DATEFROMPARTS(Année, Mois, Jours)
MONTHS_BETWEEN MONTHS_BETWEEN is used to find out the number of months between two specified dates. Format:
MONTHS_BETWEEN(Date1, Date2)
Example: The following SQL code is used to select the orders placed between two dates:
SELECT DATECDE,
MONTHS_BETWEEN('20070203','20070102') AS MB
FROM COMMANDE
Example: The following SQL code is used to select the customers according to their age:
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 NEW_TIME is used to find out a date after converting its time zone. Format:
NEW_TIME(Date, Fuseau Horaire 1, Fuseau Horaire 2)
Example:
SELECT NEW_TIME('200311010145', 'AST', 'MST') AS NTI
FROM CLIENT
Remark: If the time zones correspond to an empty string (""), the result will be a DateTime value to 0. NEXT_DAY NEXT_DAY is used to find out the first day of the week following the specified date or the specified day. Format: Example:
SELECT NEXT_DAY('20071007','dimanche') AS NXD
FROM CLIENT
ROUND ROUND is used to round the date to the specified format. Format: Example:
SELECT DATECDE,
ROUND(DATECDE,'YYYY') AS TR
FROM COMMANDE
SYSDATE SYSDATE is used to find out the current date and time. Format: Example:
SELECT SYSDATE AS SY FROM CLIENT WHERE CLCLEUNIK=1
TRUNC TRUNC is used to truncate the date to the specified format. Format:
The "Format" parameter can correspond to the following values: - Century: "CC" or "SCC"
- Year: "Y", "YEAR", "YY", "YYY", "YYYY", "SYEAR", "SYYYY"
- ISO year: "I", "IY", "IY", "IYYY": ISOYear
- Quarter: "Q"
- Mois: "MM", "MON", "MONTH"
- First day of month that is the same day of week: "W"
- First day of the week: "D", "DAY", "DY"
- Day: "DD", "DDD", "J"
- Time of day: "HH", "HH12", "HH24"
- Minutes: "MI"
Example:
SELECT DATECDE,
TRUNC(DATECDE) AS TR
FROM COMMANDE
WHERE COCLEUNIK
ISDATE ISDATE is used to determine if an expression corresponds to a date. This function returns: - 1 if the expression corresponds to a date or datetime
- 0 otherwise.
Format: Example:
SELECT DATE, ISDATE(DATE) FROM Commande WHERE IDCommande=50
COALESCE COALESCE is used to find out the first non-null expression among its arguments. Format:
COALESCE(Param1, Param2, ...)
Example:
SELECT COALESCE(hourly_wage, salary, commission) AS Total_Salary FROM wages
GREATEST
GREATEST returns the greatest value of the elements passed as parameter. Format:
GREATEST(Param1, Param2, ...)
LEAST
LEAST returns the lowest value of the elements passed as parameter. Format:
LEAST(Param1, Param2, ...)
NVL, IFNULL, ISNULL NVL is used to replace the null values of a column by a substitution value. ISNULL and IFNULL are identical. ISNULL is used in SQL Server and IFNULL is used in MySQL or Progress databases. Format:
NVL(Nom Colonne, Valeur de substitution)
Example:
SELECT hourly_wage AS R1,NVL(hourly_wage,0) AS Total FROM wages
NULLIF NULLIF returns a NULL value if the two specified expressions are equal. Format:
NULLIF(expression1, expression2)
DECODE DECODE is used to find out the operating mode of a IF .. THEN .. ELSE statement. Format:
DECODE(Nom_Colonne, Valeur comparée 1, Valeur retournée 1, [Valeur comparée 2, ...
Valeur retournée 2][, Valeur par défaut])
Example: Depending on the selected customer, returns the name corresponding to the specified identifier:
SELECT CLIENT_NOM,
DECODE(CLIENT_ID, 10000, 'Client 1',10001,'Client 2',10002,'Client 3','Autre')
FROM CLIENT
CASE CASE is used to find out the operating mode of a IF .. THEN .. ELSE statement. Format:
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
Example: Returns "three" if the item corresponds to "3" , returns "four" if the item corresponds to "4" and returns "other" in the other cases:
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 MATCH AGAINST is used to find out the pertinence of the record during a full-text search. Format:
MATCH(Liste des rubriques) AGAINST [ALL] Valeur
Where: - List of items corresponds to the list of index items separated by commas (the order of items is not important)
- Value corresponds to the value sought in the different items. This parameter can correspond to a literal value or to a parameter name. The search value can contain the following elements:
| | | Element | Meaning | | A single word | The specified word will be sought. The relevance will be increased if the text contains this word. Example: "WINDEV" searches for "WINDEV". | | Two words separated by a space character | Searches for one of the words. Example: "WINDEV WEBDEV" searches for the texts containing either "WINDEV" or "WEBDEV". | | A word preceded by the "+" sign | The specified word is mandatory. Example: "+WINDEV" searches for the texts that necessarily contain "WINDEV". | | A word preceded by the "-" sign | The specified word must not be found in the text. Example: "-Index" searches for the texts that do no contain "Index". | | A word preceded by the "~" sign | If the text contains the specified word, the relevance will be reduced. | | One or more words enclosed in quotes | The specified words are searched in group and in order. Caution: if "Ignore the words less than " differs from 0, the words enclosed in quotes less than the specified size will not be sought. | | A word followed by the "*" sign | The type of the search performed is "Starts with" the specified word. |
[ALL] is used to force the replacement of space characters by "+" in the sought value. Example: In this example, EDT_Find is an edit control and ConnectedUserID is a variable.
MaRequête is string = [
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
]
HExecuteSQLQuery(REQ_RECH, hQueryDefault, MaRequête)
FOR EACH REQ_RECH
TableAddLine(TABLE_Contact_par_catégorie, ...
REQ_RECH.idcontact,REQ_RECH.IDCategorie, IdUserConnecté, ...
REQ_RECH.Nom, REQ_RECH.Prenom)
END
CASE ERROR:
Error(HErrorInfo())
For more details on full-text search, see Search and full-text index. MD5
MD5 calculates the MD5 check sum of the string passed as parameter. The returned value is a hexadecimal integer of 32 characters that can be used as hash key for example. Format: SHA and SHA1
SHA and SHA1 calculate the 160-bit SHA1 check sum of the string passed as parameter according to the RFC 3174 standard (Secure Hash Algorithm). The returned value is a hexadecimal string of 40 characters or NULL if the argument is NULL. This function can be used for hashing the keys. Format: REGEXP or RLIKE or ~ or REGEXP_LIKE
The purpose of REGEXP or RLIKE or ~ or REGEXP_LIKE is to evaluate a regular expression inside an SQL query. Format:
REGEXP_LIKE(chaîne, expression)
where: - string corresponds to the string that must be evaluated.
- expression corresponds to the regular expression.
The function result is a boolean: - True if the string corresponds to the regular expression.
- False othewise.
Remark: In a regular expression, the "\" character is used to specify a specific formatting. Therefore, "\r" corresponds to a carriage return and "\n" to a line wrap... Examples: In these examples, the 'abcde' string is compared to a regular expression.
sRequete = "SELECT 'abcde' REGEXP 'a[bcd]{3}e' AS result"
REQ is Data Source
HExecuteSQLQuery(REQ, hQueryDefault, sRequete)
HReadFirst(REQ)
let bResult = REQ.result
// bResult vaut vrai
sRequete = "SELECT 'abcde' REGEXP 'a[bcd]{2}e' AS result"
HExecuteSQLQuery(REQ, hQueryDefault, sRequete)
HReadFirst(REQ)
bResult = REQ.result
// bResult vaut faux
Bitwise operators and functions The following are bitwise operators: The corresponding functions are as follows: - BITAND,
- BITOR,
- BITXOR,
- BITNOT,
- BITANDNOT.
Example:
sdReq is Data Source
sSQL is string = [
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
]
HExecuteSQLQuery(sdReq, sSQL)
Trace("attendu:")
Trace("3 - 3 - 2 - 2 - 15 - 15 - 3 - 3 - 2 - 4 - 4")
Trace("obtenu:")
FOR EACH sdReq
Trace(Replace(HRecordToString(sdReq), TAB, " - "))
END
IS JSON XXX "IS JSON xxx" commands are used to determine if an item is: - a JSON content (IS JSON),
- a JSON content that represents an object (IS JSON OBJECT),
- a JSON content that represents an array (IS JSON ARRAY),
- ...
Format:
IS [NOT] JSON [OBJECT|ARRAY|SCALAR|VALUE] (expression)
The command returns 1 if the expression contains valid data, 0 otherwise. Example:
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 The SQL "JSON_VALUE" command gets the value of an element contained in the JSON item.
Format:
JSON_VALUE(expression, chemin)
where: - expression corresponds to a variable containing JSON text
- path corresponds to the property to extract.
Example:
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 The SQL "JSON_QUERY" command extracts an object or an array from a JSON string.
Format:
JSON_QUERY(expression, chemin)
where: - expression corresponds to a variable that contains JSON text
- path specifies the object or the array to extract.
Example:
SELECT
Produit.Reference,
Produit.Libellé,
Produit.Caractéristiques,
JSON_QUERY(Produit.Caractéristiques, '$.couleurs' ) AS Couleurs
FROM
Produit
JSON_EXISTS The SQL "JSON_EXISTS" command allows you to get the records with a JSON item that contains a particular value.
Format:
JSON_EXISTS(expression, filtre)
where: - expression corresponds to a variable that contains JSON text
- filter corresponds to the data to be extracted.
Example:
SELECT
Produit.Reference,
Produit.Libellé,
Produit.Caractéristiques
FROM
Produit
WHERE
JSON_EXISTS(Produit.Caractéristiques, '$.talon?(@ == true)')
JSON_OBJECT The SQL "JSON_OBJECT" command returns a JSON object from any item. The retrieved JSON content is an object. Example:
SELECT
Contact.IDContact,
JSON_OBJECT('NomComplet': Contact.nom+' '+Contact.prenom,
'Adresse': Adresse+' '+CodePostal+' '+ville+' '+pays)
AS ContenuJSON
FROM
Contact
WHERE
IDContact <= 3
JSON_OBJECTAGG The SQL "JSON_OBJECTAGG" command returns a JSON object containing key-value pairs for each specific key and value in a set of SQL values. Example:
SELECT
JSON_OBJECTAGG(Contact.nom+' '+Contact.prenom VALUE Adresse+' '+
CodePostal+' '+ville+' '+pays) AS ContenuJSON
FROM
Contact
WHERE
IDContact <= 3
JSON_ARRAY The SQL "JSON_ARRAY" command returns a JSON array from any item. The JSON content retrieved is an array ( [xxx, ...] ). Example:
SELECT
Contact.IDContact,
JSON_ARRAY(Contact.nom+' '+Contact.prenom) AS ContenuJSON
FROM
Contact
WHERE
IDContact <= 3
JSON_ARRAYAGG The SQL "JSON_ARRAYAGG" command returns a JSON array containing key-value pairs for each specific key and value in a set of SQL values. The JSON content retrieved is an array ( [xxx, ...] ). Example:
SELECT pays,
JSON_ARRAYAGG(client.nom)
FROM client
GROUP BY pays -> [ "romaric", "bob", "joe" ]
This page is also available for…
|
|
|
|
|
|
|