|
- Overview
- SQL functions
- Scalar expression
- LEFT
- RIGHT
- MID, SUBSTR and SUBSTRING
- MID
- SUBSTR
- BTRIM
- LTRIM
- RTRIM
- TRIM
- OVERLAY
- REPLACE
- TRANSLATE
- 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
- CURRENT_TIMESTAMP
- GETDATE
- GETUTCDATE
- MONTHS_BETWEEN
- NEW_TIME
- NEXT_DAY
- ROUND
- SYSDATE
- TRUNC
- COALESCE
- NVL, IF_NULL, IS_NULL
- DECODE
- CASE
- MATCH AGAINST
SQL functions that can be used in the SQL queries
The following SQL functions can be used on the queries written in SQL code (classified by theme): | | | | | | | | | | | | - conversion into character string:
| | - modifying the case of a character string:
| | - size of character string:
| - LEN and LENGTH
- CHARACTER_LENGTH, CHAR_LENGTH and BYTE_LENGTH
| - position of character string:
| | - number of records in a file:
| | - calculating numeric values:
| See the mathematical SQL functions. | - selecting the first n records or the last n records:
| | | | | | | | | | | | | | | | | | | |
See a specific documentation about the SQL language for more details. 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 types of data sources (Oracle, Access, SQL Server, ...).
Remark: From version 19, HFSQL is the new name of HyperFileSQL. 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"
 Versions 16 and laterELT ELT returns the nth character string found in a list of strings. Use format:
ELT(String Number, String1, String2, String3, ...)
Example: The following SQL code is used to select the first string of the list:
SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo')
 New in version 16ELT ELT returns the nth character string found in a list of strings. Use format:
ELT(String Number, String1, String2, String3, ...)
Example: The following SQL code is used to select the first string of the list:
SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo')
ELT returns the nth character string found in a list of strings.Use format:
ELT(String Number, String1, String2, String3, ...)
Example: The following SQL code is used to select the first string of the list:
SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo')
Versions 16 and laterEXTRACTVALUEEXTRACTVALUE 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. Use format:
EXTRACTVALUE(XML Fragment, XPATH Expression)
<Fragment of XML> 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 CUSTOMER
WHERE CUUNIKKEY=1
New in version 16EXTRACTVALUEEXTRACTVALUE 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. Use format:
EXTRACTVALUE(XML Fragment, XPATH Expression)
<Fragment of XML> 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 CUSTOMER
WHERE CUUNIKKEY=1
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. Use format:
EXTRACTVALUE(XML Fragment, XPATH Expression)
<Fragment of XML> 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 CUSTOMER
WHERE CUUNIKKEY=1
LEFT LEFT extracts the left part (which means the first characters) of an expression.
Use format:
LEFT(Initial expression, Number of characters to extract)
Example: The following SQL code is used to list the states of the customers:
SELECT LEFT(ZipCode, 2)
FROM CUSTOMER
RIGHT RIGHT extracts the right part (which means the last characters) of an expression. Use format:
RIGHT(Initial expression, Number of characters to extract)
Example: The following SQL code is used to extract the last five characters from the name of the customers:
SELECT RIGHT(NAME, 5)
FROM CUSTOMER
MID, SUBSTR and SUBSTRING MID, SUBSTR and SUBSTRING are used to extract a sub-string 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
ZIPCODES.IDCedex AS IDCedex,
ZIPCODES.ZipCode AS ZipCode,
ZIPCODES.City AS City,
SUBSTR(ZIPCODES.City, 2, 1) AS Expression1
FROM
ZIPCODES
WHERE
SUBSTR(ZIPCODES.City, 2, 1) = 'A'
MID MID can be used only on an Access data source.
Use format:
MID(Initial expression, Start position, Number of characters to extract)
Example: The following SQL code is used to extract the 3rd and 4th characters from the name of the customers:
SELECT MID(NAME, 3, 2)
FROM CUSTOMER
SUBSTR SUBSTR can only be used on an Oracle, HFSQL Classic or HFSQL Client/Server data source. Use format:
SUBSTR(Initial expression, Start position, Number of characters to extract)
Example: The following SQL code is used to extract the 3rd and 4th characters from the name of the customers:
SELECT SUBSTR(NAME, 3, 2)
FROM CUSTOMER
Versions 16 and laterSPLIT_PARTSPLIT_PART splits a character string according to the specified separator and returns the nth part of the string. Use format:
SPLIT_PART(Initial Expression, Delimiter, Number of the Part to Extract)
Example: The following SQL code is used to extract the first 3 words corresponding to the address:
SELECT SPLIT_PART(ADDRESS,' ',1), SPLIT_PART(ADDRESS,' ',2),SPLIT_PART(ADDRESS,' ',3)
FROM CUSTOMER
WHERE CUUNIKKEY=2
New in version 16SPLIT_PARTSPLIT_PART splits a character string according to the specified separator and returns the nth part of the string. Use format:
SPLIT_PART(Initial Expression, Delimiter, Number of the Part to Extract)
Example: The following SQL code is used to extract the first 3 words corresponding to the address:
SELECT SPLIT_PART(ADDRESS,' ',1), SPLIT_PART(ADDRESS,' ',2),SPLIT_PART(ADDRESS,' ',3)
FROM CUSTOMER
WHERE CUUNIKKEY=2
SPLIT_PART
SPLIT_PART splits a character string according to the specified separator and returns the nth part of the string.Use format:
SPLIT_PART(Initial Expression, Delimiter, Number of the Part to Extract)
Example: The following SQL code is used to extract the first 3 words corresponding to the address:
SELECT SPLIT_PART(ADDRESS,' ',1), SPLIT_PART(ADDRESS,' ',2),SPLIT_PART(ADDRESS,' ',3)
FROM CUSTOMER
WHERE CUUNIKKEY=2
Versions 19 and laterBTRIM BTRIM deletes a character string found at the beginning or at the end of a string. Use format:
BTRIM(<Source string>, [<String to delete>])
Example: Delete the 'AB' string from the 'ABRACADABRA' string
BTRIM('ABRACADABRA','AB')
In this example, the result is 'RCDR'. New in version 19BTRIM BTRIM deletes a character string found at the beginning or at the end of a string. Use format:
BTRIM(<Source string>, [<String to delete>])
Example: Delete the 'AB' string from the 'ABRACADABRA' string
BTRIM('ABRACADABRA','AB')
In this example, the result is 'RCDR'. BTRIM BTRIM deletes a character string found at the beginning or at the end of a string. Use format:
BTRIM(<Source string>, [<String to delete>])
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 the 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. Use format:
-- Deleting the space characters found on the left
LTRIM(Initial expression)
-- Deleting a list of characters
LTRIM(Initial expression, Characters to delete)
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 found between the title and the name).
SELECT LTRIM(NAME, 'Ms.')
FROM CUSTOMER
SELECT LTRIM(NAME)
FROM CUSTOMER
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. Use format:
-- Deleting the space characters found on the right
RTRIM(Initial expression)
-- Deleting a list of characters
RTRIM(Initial expression, Characters to delete)
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(NAME, 'EUR')
FROM CUSTOMER
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. Use format:
-- Deleting the space characters on the right ant on the left
TRIM(Initial expression)
-- Deleting a character string found at the beginning or at the end of a string
TRIM(Initial expression, String to delete)
-- OR
TRIM(BOTH String to delete FROM Initial expression)
-- Deleting a character string found at the beginning of a string
TRIM(LEADING String to delete FROM Initial expression)
-- Deleting a character string found at the end of a string
TRIM(TRAILING String to delete FROM Initial expression)
Versions 19 and laterOVERLAY OVERLAY is used to replace a character string in another one. Use format:
OVERLAY(<Source string> PLACING <String to replace>
FROM <Start position> [FOR <Length>])
Example: The following SQL code is used to replace "Green" by "Red":
SELECT OVERLAY('Green apple' PLACING 'Red' FROM 7) FROM Product
New in version 19OVERLAY OVERLAY is used to replace a character string in another one. Use format:
OVERLAY(<Source string> PLACING <String to replace>
FROM <Start position> [FOR <Length>])
Example: The following SQL code is used to replace "Green" by "Red":
SELECT OVERLAY('Green apple' PLACING 'Red' FROM 7) FROM Product
OVERLAY OVERLAY is used to replace a character string in another one. Use format:
OVERLAY(<Source string> PLACING <String to replace>
FROM <Start position> [FOR <Length>])
Example: The following SQL code is used to replace "Green" by "Red":
SELECT OVERLAY('Green apple' PLACING 'Red' FROM 7) FROM Product
REPLACE REPLACE returns a character string: - by replacing all the occurrences of a word found in a string by another word.
- by replacing all the 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. Use format:
-- Replacing all the occurrences of a word by another word
REPLACE(Initial expression, String to replace, New string)
-- Deleting all the occurrences of a word
REPLACE(Initial expression, String to delete)
Versions 16 and laterREVERSEREVERSE returns a character string in which the order of characters is the reversed order of the initial string. Use format: New in version 16REVERSEREVERSE returns a character string in which the order of characters is the reversed order of the initial string. Use format: REVERSE
REVERSE returns a character string in which the order of characters is the reversed order of the initial string. Use 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). Use format:
-- Replacing characters
TRANSLATE(Initial expression, Characters to replace, New characters)
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(MyControl, 'éèàù', 'eeau')
FROM MyTable
CONCAT CONCAT concatenates several character strings together. Use format:
CONCAT(String 1, String 2 [,..., String N])
Versions 17 and laterSTRING_AGG STRING_AGG is used to concatenate non-null strings from a list of values. Use format:
STRING_AGG(string, separator)
Example: The following code returns in a single string the list of delivery modes separated by ";".
SELECT STRING_AGG(ltext,';') AS str FROM delivery
Content of delivery file: - ShippingCompany
- ExpressDelivery
- Certified
- PickedUp
Result returned by the STRING_AGG function: "ShippingCompany;ExpressDelivery;Certified;PickedUp". New in version 17STRING_AGG STRING_AGG is used to concatenate non-null strings from a list of values. Use format:
STRING_AGG(string, separator)
Example: The following code returns in a single string the list of delivery modes separated by ";".
SELECT STRING_AGG(ltext,';') AS str FROM delivery
Content of delivery file: - ShippingCompany
- ExpressDelivery
- Certified
- PickedUp
Result returned by the STRING_AGG function: "ShippingCompany;ExpressDelivery;Certified;PickedUp". STRING_AGG STRING_AGG is used to concatenate non-null strings from a list of values. Use format:
STRING_AGG(string, separator)
Example: The following code returns in a single string the list of delivery modes separated by ";".
SELECT STRING_AGG(ltext,';') AS str FROM delivery
Content of delivery file: - ShippingCompany
- ExpressDelivery
- Certified
- PickedUp
Result returned by the STRING_AGG function: "ShippingCompany;ExpressDelivery;Certified;PickedUp". LPAD LPAD returns a string whose size is defined. To reach the requested size, the string is completed to the left: - by space characters.
- by a character or by a given string.
Use format:
-- Completion with space characters
LPAD(Initial expression, Length)
-- Completion with a character
LPAD(Initial expression, Length, Character)
-- Completion with a character string
LPAD(Initial expression, Length, Character string)
Versions 16 and laterREPEATREPEAT 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.
Use format:
REPEAT(Initial String, n)
Example: The following code is used to repeat the name of the contact 3 times:
SELECT REPEAT(CONTACTNAME,14)
FROM CUSTOMER
WHERE CUUNIKKEY=10
New in version 16REPEATREPEAT 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.
Use format:
REPEAT(Initial String, n)
Example: The following code is used to repeat the name of the contact 3 times:
SELECT REPEAT(CONTACTNAME,14)
FROM CUSTOMER
WHERE CUUNIKKEY=10
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.
Use format:
REPEAT(Initial String, n)
Example: The following code is used to repeat the name of the contact 3 times:
SELECT REPEAT(CONTACTNAME,14)
FROM CUSTOMER
WHERE CUUNIKKEY=10
RPAD RPAD returns a string whose size is defined. To reach the requested size, the string is completed to the right: - by space characters.
- by a character or by a given string.
Use format:
-- Completion with space characters
RPAD(Initial expression, Length)
-- Completion with a character
RPAD(Initial expression, Length, Character)
-- Completion with a character string
RPAD(Initial expression, Length, Character string)
Versions 16 and laterSPACESPACE returns a string containing N space characters. Use format: New in version 16SPACESPACE returns a string containing N space characters. Use format: SPACE
SPACE returns a string containing N space characters.
Use format:Versions 19 and laterTO_CHARTO_CHAR is used to convert into character string: - a datetime,
- a numeric value.
Use format: 1. Converting a datetime:
TO_CHAR(<DateTime Value>, <DataTime Format> [, <DateTime Options>])
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. Conversion of a numeric value:
TO_CHAR(<Numeric Value>, <Numeric Format> [, <Numeric Options>])
- 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. New in version 19TO_CHARTO_CHAR is used to convert into character string: - a datetime,
- a numeric value.
Use format: 1. Converting a datetime:
TO_CHAR(<DateTime Value>, <DataTime Format> [, <DateTime Options>])
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. Conversion of a numeric value:
TO_CHAR(<Numeric Value>, <Numeric Format> [, <Numeric Options>])
- 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. TO_CHAR
TO_CHAR is used to convert into character string: - a datetime,
- a numeric value.
Use format: 1. Converting a datetime:
TO_CHAR(<DateTime Value>, <DataTime Format> [, <DateTime Options>])
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. Conversion of a numeric value:
TO_CHAR(<Numeric Value>, <Numeric Format> [, <Numeric Options>])
- 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. Versions 20 and laterCHARCHAR is used to convert an ASCII code (integer) into character.
Use format:
<ASCII Code> is a numeric and it corresponds to the ASCII character to convert, included 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. New in version 20CHARCHAR is used to convert an ASCII code (integer) into character.
Use format:
<ASCII Code> is a numeric and it corresponds to the ASCII character to convert, included 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. CHAR
CHAR is used to convert an ASCII code (integer) into character.
Use format:
<ASCII Code> is a numeric and it corresponds to the ASCII character to convert, included 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. Versions 20 and laterCHRCHR is used to convert an ASCII code (integer) into character.
<ASCII Code> is a numeric and it corresponds to the ASCII character to convert, included 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.
New in version 20CHRCHR is used to convert an ASCII code (integer) into character.
<ASCII Code> is a numeric and it corresponds to the ASCII character to convert, included 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.
CHR
CHR is used to convert an ASCII code (integer) into character.
<ASCII Code> is a numeric and it corresponds to the ASCII character to convert, included 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.
Versions 20 and laterCASTCAST 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 |
The result of the function is the converted value. Example:
This code returns: "126". New in version 20CASTCAST 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 |
The result of the function is the converted value. Example:
This code returns: "126". 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 |
The result of the function is the converted value.Example:
This code returns: "126". Versions 21 and laterCONVERTCONVERT is used to convert a character string from a character set to another one. Use format:
CONVERT(Text to Convert, Charset Used, New Charset)
Example: Converting a string from UTF-8 to LATIN1:
SELECT CONVERT('text in utf8', 'UTF8', 'LATIN1')
Remark: This function is not available for the SQL queries run on HFSQL files in Android. New in version 21CONVERTCONVERT is used to convert a character string from a character set to another one. Use format:
CONVERT(Text to Convert, Charset Used, New Charset)
Example: Converting a string from UTF-8 to LATIN1:
SELECT CONVERT('text in utf8', 'UTF8', 'LATIN1')
Remark: This function is not available for the SQL queries run on HFSQL files in Android. CONVERT
CONVERT is used to convert a character string from a character set to another one. Use format:
CONVERT(Text to Convert, Charset Used, New Charset)
Example: Converting a string from UTF-8 to LATIN1:
SELECT CONVERT('text in utf8', 'UTF8', 'LATIN1')
Remark: This function is not available for the SQL queries run on HFSQL files in Android. Versions 19 and laterINITCAPINITCAP 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. Use format: Example:
INITCAP ('grEat Weather today')
This code returns: 'Great Weather Today'. New in version 19INITCAPINITCAP 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. Use format: Example:
INITCAP ('grEat Weather today')
This code returns: 'Great Weather Today'. 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.
Use format:Example:
INITCAP ('grEat Weather today')
This code returns: 'Great Weather Today'. LOWER LOWER converts an expression into lowercase characters. LOWER cannot be used with an Access data source. Use format:
LOWER(Initial Expression)
Example: The following SQL code is used to convert the first name of the customers into lowercase characters:
SELECT LOWER(FirstName)
FROM CUSTOMER
UPPER UPPER converts an expression into uppercase characters. UPPER cannot be used with an Access data source. Use format:
UPPER(Initial Expression)
Example: The following SQL code is used to convert the cities of customers into uppercase characters:
SELECT UPPER(City)
FROM CUSTOMER
Versions 16 and laterLCASELCASE returns a string with all the characters written in lowercase according to the current character set. Use format:
LCASE(Initial Expression)
Example: The following SQL code is used to convert the cities of customers into lowercase characters:
SELECT LCASE(City)
FROM CUSTOMER
New in version 16LCASELCASE returns a string with all the characters written in lowercase according to the current character set. Use format:
LCASE(Initial Expression)
Example: The following SQL code is used to convert the cities of customers into lowercase characters:
SELECT LCASE(City)
FROM CUSTOMER
LCASE
LCASE returns a string with all the characters written in lowercase according to the current character set.Use format:
LCASE(Initial Expression)
Example: The following SQL code is used to convert the cities of customers into lowercase characters:
SELECT LCASE(City)
FROM CUSTOMER
Versions 16 and laterUCASEUCASE returns a string with all the characters in uppercase according to the current set of characters. Use format:
UCASE(Initial Expression)
Example: The following SQL code is used to convert the cities of customers into uppercase characters:
SELECT UCASE(City)
FROM CUSTOMER
New in version 16UCASEUCASE returns a string with all the characters in uppercase according to the current set of characters. Use format:
UCASE(Initial Expression)
Example: The following SQL code is used to convert the cities of customers into uppercase characters:
SELECT UCASE(City)
FROM CUSTOMER
UCASE
UCASE returns a string with all the characters in uppercase according to the current set of characters.Use format:
UCASE(Initial Expression)
Example: The following SQL code is used to convert the cities of customers into uppercase characters:
SELECT UCASE(City)
FROM CUSTOMER
LEN/LENGTH LEN and LENGTH return the size (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.
Use format: Example: The following SQL code is used to find out the size of the customer names:
SELECT LEN(NAME)
FROM CUSTOMER
LENGTH LENGTH can be used only on an Oracle data source. Use format:
LENGTH(Initial Expression)
Example: The following SQL code is used to find out the size of the customer names:
SELECT LENGTH(NAME)
FROM CUSTOMER
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. Use format:
INSTR(Initial Expression, String to Find, Start Position, 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(City, 'T', 1, 1)
FROM CUSTOMER
Versions 16 and laterFIELDFIELD returns the index of the sought string in the list. If the string is not found, the function returns 0. Use format:
FIELD(String to Find, String1, String2, ...)
New in version 16FIELDFIELD returns the index of the sought string in the list. If the string is not found, the function returns 0. Use format:
FIELD(String to Find, String1, String2, ...)
FIELD
FIELD returns the index of the sought string in the list.If the string is not found, the function returns 0. Use format:
FIELD(String to Find, String1, String2, ...)
Versions 19 and laterFIND_IN_SETFIND_IN_SET returns the position of a string in a list of values. If the string is not found, the function returns 0. Use format:
FIND_IN_SET(<String to Find>, <List of Values>)
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('Red','Blue,Yellow,Red,Green')
New in version 19FIND_IN_SETFIND_IN_SET returns the position of a string in a list of values. If the string is not found, the function returns 0. Use format:
FIND_IN_SET(<String to Find>, <List of Values>)
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('Red','Blue,Yellow,Red,Green')
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. Use format:
FIND_IN_SET(<String to Find>, <List of Values>)
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('Red','Blue,Yellow,Red,Green')
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 a HFSQL Classic or SQL Server data source. Use format:
PATINDEX(Value to Find, 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.
Use format:
POSITION(String to find IN Initial expression)
POSITION(String to find IN Initial expression, Start position)
Example:
TestQRY is Data Source
sSQLCode is string = [
SELECT POSITION( 'No' IN Name ) As NamePos
FROM cooperator
LIMIT 0 , 30
]
IF NOT HExecuteSQLQuery(TestQRY, MyConnection, hQueryWithoutCorrection, sSQLCode) THEN
Error(HErrorInfo())
END
FOR EACH TestQRY
Trace(TestQRY.NamePos)
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.
Use format:
COUNT(*)
COUNT(Item)
COUNT(DISTINCT Item)
Examples: - The following SQL code is used to find out the number of products found in the Product file:
SELECT COUNT(*)
FROM PRODUCT
- 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(VATRate)
FROM PRODUCT
WHERE VATRate = '5.5'
- The following SQL code is used to find out the number of different and non-null VAT rates:
SELECT COUNT(DISTINCT PRODUCT.VATRate)
FROM PRODUCT
AVG AVG calculates: - the average of a set of non-null values.
- the average of a set of different and non-null values.
Use format:
AVG(Item)
AVG(DISTINCT Item)
Examples: - The following SQL code is used to find out the average salary of the employees:
SELECT AVG(Salary)
FROM EMPLOYEE
- The following SQL code is used to find out the average of the different salaries of the employees:
SELECT AVG(DISTINCT Salary)
FROM EMPLOYEE
MAX MAX returns the greatest value of all 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. Use format:
MAX(Item)
MAX(DISTINCT Item)
Example: The following SQL code is used to find out the maximum salary of employees:
SELECT MAX(Salary)
FROM EMPLOYEE
MIN MIN returns the smallest value of the non-null values found in an item for all the records selected in the file. Use format:
MIN(Item)
MIN(DISTINCT Item)
Example: The following SQL code is used to find out the minimum salary of employees:
SELECT MIN(Salary)
FROM EMPLOYEE
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.
Use format:
SUM(Item)
SUM(DISTINCT Item)
Examples: - The following SQL code is used to find out the total sum of salaries:
SELECT SUM(Salary)
FROM EMPLOYEE
- The following SQL code is used to find out the total sum of different salaries:
SELECT SUM(DISTINCT Salary)
FROM EMPLOYEE
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 MYFILE
This syntax must be replaced by the following syntax:
SELECT (A*B) AS C, SUM(A*B)
FROM MYFILE
Versions 21 and laterEVERY 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.
Use 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 company.NAME, EVERY(employee.salary > 10000) AS rich
FROM company NATURAL JOIN employee GROUP BY company.NAME
New in version 21EVERY 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.
Use 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 company.NAME, EVERY(employee.salary > 10000) AS rich
FROM company NATURAL JOIN employee GROUP BY company.NAME
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.
Use 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 company.NAME, EVERY(employee.salary > 10000) AS rich
FROM company NATURAL JOIN employee GROUP BY company.NAME
TOP TOP returns the first n records of the query result. TOP cannot be used on an Oracle or PostgreSQL data source. Use format:
TOP Number of the last selected record
Example: The following SQL code is used to list the 10 best customers:
SELECT TOP 10 SUM(ORDERS.TotalIncTax) AS TotalIncTax,
CUSTOMER.CustomerName
FROM CUSTOMER, ORDERS
WHERE CUSTOMER.CustNum = ORDERS.CustNum
GROUP BY CustomerName
ORDER BY TotalIncTax 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.
Versions 20 and laterYou have the ability to pass a parameter to TOP. The parameter can be:
SELECT TOP {pMaxNumberCustomers}
Customer.CustomerID AS CustomerID,
Customer.LastName AS LastName,
Customer.FirstName AS FirstName,
Customer.Email AS Email,
Customer.FidelityBonus AS FidelityBonus
FROM
Customer
New in version 20You have the ability to pass a parameter to TOP. The parameter can be:
SELECT TOP {pMaxNumberCustomers}
Customer.CustomerID AS CustomerID,
Customer.LastName AS LastName,
Customer.FirstName AS FirstName,
Customer.Email AS Email,
Customer.FidelityBonus AS FidelityBonus
FROM
Customer
You have the ability to pass a parameter to TOP. The parameter can be:
SELECT TOP {pMaxNumberCustomers}
Customer.CustomerID AS CustomerID,
Customer.LastName AS LastName,
Customer.FirstName AS FirstName,
Customer.Email AS Email,
Customer.FidelityBonus AS FidelityBonus
FROM
Customer
BOTTOM BOTTOM returns the last n records found in the query result. BOTTOM can only be used on a HFSQL data source. Use format:
BOTTOM Number of the last selected record
Example: The following SQL code is used to list the 10 worst customers:
SELECT BOTTOM 10 SUM(ORDERS.TotalIncTax) AS TotalIncTax,
CUSTOMER.CustomerName
FROM CUSTOMER, ORDERS
WHERE CUSTOMER.CustNum = ORDERS.CustNum
GROUP BY CustomerName
ORDER BY TotalIncTax DESC
Remark: - It is advisable to use the BOTTOM function on a sorted query.. Otherwise, the records returned by BOTTOM will be selected according to their record number.
Versions 20 and laterYou have the ability to pass a parameter to BOTTOM. The parameter can be: New in version 20You have the ability to pass a parameter to BOTTOM. The parameter can be: 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. Use format:
LIMIT Number of the last selected record
Example: The following SQL code is used to list the 10 best customers:
SELECT SUM(ORDERS.TotalIncTax) AS TotalIncTax,
CUSTOMER.CustomerName
FROM CUSTOMER, ORDERS
WHERE CUSTOMER.CustNum = ORDERS.CustNum
GROUP BY CustomerName
ORDER BY TotalIncTax DESC
LIMIT 10
Remark: - It is advisable to use the LIMIT function on a sorted query.. Otherwise, the records returned by TOP will be selected according to their record number.
Versions 20 and laterYou have the ability to pass a parameter to LIMIT. The parameter can be: New in version 20You have the ability to pass a parameter to LIMIT. The parameter can be: You have the ability to pass a parameter to LIMIT. The parameter can be:
ASCII ASCII returns the ASCII code: - of character.
- of first character found in a string.
If the character or the character string corresponds to an empty string (""), ASCII returns 0. Use format:
-- ASCII code of character
ASCII(Character)
-- ASCII code of first character found in a string
ASCII(Character string)
UNICODE UNICODE returns the integer value defined by the Unicode standard: - of character.
- of first character found in a string.
Use format:
-- Unicode code of character
UNICODE(Character)
-- Unicode code of first character found in a string
UNICODE(Character string)
Versions 21 and laterCURRENT_USER CURRENT_USER returns the user name for the current connection. Use format: Example: The following code updates the author of the modification performed in CUSTOMER table:
UPDATE CUSTOMER SET USER=CURRENT_USER() WHERE CUSTOMERID=1
New in version 21CURRENT_USER CURRENT_USER returns the user name for the current connection. Use format: Example: The following code updates the author of the modification performed in CUSTOMER table:
UPDATE CUSTOMER SET USER=CURRENT_USER() WHERE CUSTOMERID=1
CURRENT_USER CURRENT_USER returns the user name for the current connection. Use format: Example: The following code updates the author of the modification performed in CUSTOMER table:
UPDATE CUSTOMER SET USER=CURRENT_USER() WHERE CUSTOMERID=1
Versions 22 and laterSYSTEM_USER SYSTEM_USER returns the user name for the current connection. Use format: Example: The following code updates the author of the modification performed in CUSTOMER table:
UPDATE CUSTOMER SET USER=SYSTEM_USER() WHERE CUSTOMERID=1
New in version 22SYSTEM_USER SYSTEM_USER returns the user name for the current connection. Use format: Example: The following code updates the author of the modification performed in CUSTOMER table:
UPDATE CUSTOMER SET USER=SYSTEM_USER() WHERE CUSTOMERID=1
SYSTEM_USER SYSTEM_USER returns the user name for the current connection. Use format: Example: The following code updates the author of the modification performed in CUSTOMER table:
UPDATE CUSTOMER SET USER=SYSTEM_USER() WHERE CUSTOMERID=1
Versions 22 and laterUSER_NAME USER_NAME returns the user name for the current connection. Use format: Example: The following code updates the author of the modification performed in CUSTOMER table:
UPDATE CUSTOMER SET USER=USER_NAME() WHERE CUSTOMERID=1
New in version 22USER_NAME USER_NAME returns the user name for the current connection. Use format: Example: The following code updates the author of the modification performed in CUSTOMER table:
UPDATE CUSTOMER SET USER=USER_NAME() WHERE CUSTOMERID=1
USER_NAME USER_NAME returns the user name for the current connection. Use format: Example: The following code updates the author of the modification performed in CUSTOMER table:
UPDATE CUSTOMER SET USER=USER_NAME() WHERE CUSTOMERID=1
SOUNDEX, SOUND LIKE SOUNDEX and SOUND LIKE return the phonetic representation of a character string (based on an English algorithm). Use format:
SOUNDEX(String)
SOUND LIKE(String)
Example: The following SQL code is used to list the customers whose name phonetically corresponds to "Henry":
SELECT CUSTOMER.CustomerLastName
FROM CUSTOMER
WHERE SOUNDEX(CUSTOMER.CustomerName) = SOUNDEX('Henry')
SELECT CUSTOMER.CustomerLastName
FROM CUSTOMER
WHERE CUSTOMER.CustomerName 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). Use format:
SOUNDEX2(String)
SOUND2 LIKE(String)
Example: The following SQL code is used to list the customers whose city phonetically corresponds to "Montpellier":
SELECT CUSTOMER.CityName
FROM CUSTOMER
WHERE SOUNDEX2(CUSTOMER.CityName) = SOUNDEX2('Montpellier')
SELECT CUSTOMER.CityName
FROM CUSTOMER
WHERE CUSTOMER.CityName SOUND2 LIKE 'Montpellier'
ADD_MONTHS ADD_MONTHS is used to add several months to a specified date. Use format:
ADD_MONTHS(Date,Number of months)
Example: The following SQL code is used to select the orders placed in April 2003.
SELECT ORDDATE,
ADD_MONTHS('20070203',2) AS AM
FROM ORDERS
LAST_DAY LAST_DAY is used to find out the date of the last day for the specified month. Use format: Example: The following SQL code is used to select the orders placed in February 2008:
SELECT LAST_DAY('20080203') AS LD,
ORDDATE
FROM ORDERS
WHERE ORDERS.CUUNIKKEY=2 ORDER BY ORDDATE
Versions 21 and laterDAY DAY returns the day of the month, which means a number included between 1 and 31. Use format: New in version 21DAY DAY returns the day of the month, which means a number included between 1 and 31. Use format: DAY DAY returns the day of the month, which means a number included between 1 and 31. Use format: Versions 21 and laterDAYOFMONTH DAYOFMONTH returns the day in the month (included between 1 and 31). Use format: New in version 21DAYOFMONTH DAYOFMONTH returns the day in the month (included between 1 and 31). Use format: DAYOFMONTH DAYOFMONTH returns the day in the month (included between 1 and 31). Use format: Versions 21 and laterDAYOFWEEK DAYOFWEEK returns the day in the week (1 for Sunday, 2 for Monday, etc.). Use format: New in version 21DAYOFWEEK DAYOFWEEK returns the day in the week (1 for Sunday, 2 for Monday, etc.). Use format: DAYOFWEEK DAYOFWEEK returns the day in the week (1 for Sunday, 2 for Monday, etc.). Use format: Versions 21 and laterDAYOFYEAR DAYOFYEAR returns the day in the year (included between 1 and 366). Use format: New in version 21DAYOFYEAR DAYOFYEAR returns the day in the year (included between 1 and 366). Use format: DAYOFYEAR DAYOFYEAR returns the day in the year (included between 1 and 366). Use format: Versions 22 and laterCURRENT_TIMESTAMP CURRENT_TIMESTAMP returns the local time of server (in date-time format). Use format: New in version 22CURRENT_TIMESTAMP CURRENT_TIMESTAMP returns the local time of server (in date-time format). Use format: CURRENT_TIMESTAMP CURRENT_TIMESTAMP returns the local time of server (in date-time format). Use format: Versions 22 and laterGETDATE GETDATE returns the local time of server (in date-time format). Use format: New in version 22GETDATE GETDATE returns the local time of server (in date-time format). Use format: GETDATE GETDATE returns the local time of server (in date-time format). Use format: Versions 22 and laterGETUTCDATE GETUTCDATE returns the UTC time of server (in date-time format). Use format: New in version 22GETUTCDATE GETUTCDATE returns the UTC time of server (in date-time format). Use format: GETUTCDATE GETUTCDATE returns the UTC time of server (in date-time format). Use format: MONTHS_BETWEEN MONTHS_BETWEEN is used to find out the number of months between two specified dates. Use format:
MONTHS_BETWEEN(Date1, Date2)
Example: The following SQL code is used to select the orders placed between two dates:
SELECT ORDDATE,
MONTHS_BETWEEN('20070203','20070102') AS MB
FROM ORDERS
Example: The following SQL code is used to select the customers according to their age :
SELECT CUSTOMER.CUSTOMERID,
CUSTOMER.LASTNAME,CUSTOMER.FIRSTNAME,
CAST(MONTHS_BETWEEN(SYSDATE,CUSTOMER.DATE_OF_BIRTH)/12 AS FLOAT) AS Age
FROM
CUSTOMER
WHERE
Age >= 18
NEW_TIME NEW_TIME is used to find out a date after converting its time zone. Use format:
NEW_TIME(Date, Time Zone 1, Time Zone 2)
Example:
SELECT NEW_TIME ('200311010145', 'AST', 'MST') AS NTI
FROM CUSTOMER
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. Use format: Example:
SELECT NEXT_DAY('20071007','Sunday') AS NXD
FROM CUSTOMER
ROUND ROUND is used to round the date to the specified format. Use format: Example:
SELECT ORDDATE,
ROUND(ORDDATE,'YYYY') AS TR
FROM ORDERS
SYSDATE SYSDATE is used to find out the current date and time. Use format: Example:
SELECT SYSDATE AS SY FROM CUSTOMER WHERE CUSTOMERID=1
TRUNC TRUNC is used to truncate the date to the specified format. Use 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"
- Month: "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: "HH", "HH12", "HH24"
- Minutes: "MI"
Example:
SELECT ORDDATE,
TRUNC(ORDDATE) AS TR
FROM ORDERS
WHERE ORDUNIKKEY
COALESCE COALESCE is used to find out the first non-null expression among its arguments. Use format:
COALESCE(Param1, Param2, ...)
Example:
SELECT COALESCE(hourly_wage, salary, commission) AS Total_Salary FROM wages
Versions 16 and laterGREATESTGREATEST returns the greatest value of the elements passed in parameter. Use format:
GREATEST(Param1, Param2, ...)
New in version 16GREATESTGREATEST returns the greatest value of the elements passed in parameter. Use format:
GREATEST(Param1, Param2, ...)
GREATEST
GREATEST returns the greatest value of the elements passed in parameter. Use format:
GREATEST(Param1, Param2, ...)
Versions 16 and laterLEASTLEAST returns the lowest value of the elements passed in parameter. Use format:
LEAST(Param1, Param2, ...)
New in version 16LEASTLEAST returns the lowest value of the elements passed in parameter. Use format:
LEAST(Param1, Param2, ...)
LEAST
LEAST returns the lowest value of the elements passed in parameter. Use format:
LEAST(Param1, Param2, ...)
NVL, IF_NULL, IS_NULL NVL is used to replace the null values of a column by a substitution value. IS_NULL and IF_NULL are identical. IS_NULL is used in SQL Server and IF_NULL is used with the MySQL or Progress databases. Use format:
NVL(Column name, Substitution value)
Example:
SELECT hourly_wage AS R1,NVL(hourly_wage,0) AS Total FROM wages
DECODE DECODE is used to find out the operating mode of a IF .. THEN .. ELSE statement. Use format:
DECODE(Column_Name, Compared value 1, Returned value 1, [Compared value 2, ...
Returned value 2][, Default value])
Example: Depending on the selected customer, returns the name corresponding to the specified identifier:
SELECT CUSTOMER_NAME,
DECODE(CUSTOMER_ID, 10000, 'Customer 1',10001,'Customer 2',10002,'Customer 3','Other')
FROM CUSTOMER
CASE CASE is used to find out the operating mode of a IF .. THEN .. ELSE statement. Use format:
CASE Column_Name WHEN Compared value 1 THEN Returned value 1 [WHEN compared value 2 THEN ...
Returned value 2][ELSE Default returned value] END
CASE WHEN Condition 1 THEN Returned value 1 [WHEN Condition 2 THEN Returned value 2] ...
[ELSE Default returned value] 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 itmInt, CASE itmInt WHEN 3 THEN 'three' WHEN 4 THEN 'four' ELSE 'other' END
SELECT itmInt, CASE WHEN itmInt=3 THEN 'three' WHEN itmInt=4 THEN 'four' ELSE 'other' END
MATCH AGAINST MATCH AGAINST is used to find out the pertinence of the record during a full-text search. Use format:
MATCH(List of items) AGAINST [ALL] Value
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.
MyQuery is string = [
SELECT * FROM Contact
WHERE MATCH(Contact.LastName, Contact.FirstName, Contact.HTMLComment,
Contact.RoughTextComment, Contact.Comments, Contact.Phone, Contact.Office,
Contact.Cell, Contact.Email, Contact.MSN, Contact.Internet_site, Contact.Country,
Contact.FaxNum, Contact.City)
AGAINST ('
]
MyQuery = MyQuery + EDT_Find + [
')
AND Contact.UserID =
]
MyQuery = MyQuery + ConnectedUserID + [
ORDER BY LastName DESC
]
HExecuteSQLQuery(QRY_SRCH, hQueryDefault, MyQuery)
FOR EACH QRY_SRCH
TableAddLine(TABLE_Contact_by_category, ...
QRY_SRCH.ContactID,QRY_SRCH.CategoryID, ConnectedUserID, ...
QRY_SRCH.LastName, QRY_SRCH.FirstName)
END
CASE ERROR:
Error(HErrorInfo())
See Search and full-text index for more details. Versions 16 and laterMD5MD5 calculates the MD5 check sum of the string passed in parameter. The returned value is an hexadecimal integer of 32 characters that can be used as hash key for example. Use format: New in version 16MD5MD5 calculates the MD5 check sum of the string passed in parameter. The returned value is an hexadecimal integer of 32 characters that can be used as hash key for example. Use format: MD5
MD5 calculates the MD5 check sum of the string passed in parameter. The returned value is an hexadecimal integer of 32 characters that can be used as hash key for example. Use format: Versions 16 and laterSHA and SHA1SHA and SHA1 calculate the 160-bit SHA1 check sum of the string passed in parameter according to the RFC 3174 standard (Secure Hash Algorithm). The returned value is an hexadecimal string of 40 characters or NULL if the argument is NULL. This function can be used for hashing the keys. Use format: New in version 16SHA and SHA1SHA and SHA1 calculate the 160-bit SHA1 check sum of the string passed in parameter according to the RFC 3174 standard (Secure Hash Algorithm). The returned value is an hexadecimal string of 40 characters or NULL if the argument is NULL. This function can be used for hashing the keys. Use format: SHA and SHA1
SHA and SHA1 calculate the 160-bit SHA1 check sum of the string passed in parameter according to the RFC 3174 standard (Secure Hash Algorithm). The returned value is an hexadecimal string of 40 characters or NULL if the argument is NULL. This function can be used for hashing the keys. Use format: Versions 20 and laterREGEXP or RLIKE or ~ or REGEXP_LIKEThe purpose of REGEXP or RLIKE or ~ or REGEXP_LIKE is to evaluate a regular expression inside a SQL query. Use format:
REGEXP_LIKE(string, 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.
sQuery = "SELECT 'abcde' REGEXP 'a[bcd]{3}e' AS result"
QRY is Data Source
HExecuteSQLQuery(QRY, hQueryDefault, sQuery)
HReadFirst(QRY)
let bResult = QRY.result
// bResult is set to True
sQuery = "SELECT 'abcde' REGEXP 'a[bcd]{2}e' AS result"
HExecuteSQLQuery(QRY, hQueryDefault, sQuery)
HReadFirst(QRY)
bResult = QRY.RESULT
// bResult is set to False
New in version 20REGEXP or RLIKE or ~ or REGEXP_LIKEThe purpose of REGEXP or RLIKE or ~ or REGEXP_LIKE is to evaluate a regular expression inside a SQL query. Use format:
REGEXP_LIKE(string, 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.
sQuery = "SELECT 'abcde' REGEXP 'a[bcd]{3}e' AS result"
QRY is Data Source
HExecuteSQLQuery(QRY, hQueryDefault, sQuery)
HReadFirst(QRY)
let bResult = QRY.result
// bResult is set to True
sQuery = "SELECT 'abcde' REGEXP 'a[bcd]{2}e' AS result"
HExecuteSQLQuery(QRY, hQueryDefault, sQuery)
HReadFirst(QRY)
bResult = QRY.RESULT
// bResult is set to False
REGEXP or RLIKE or ~ or REGEXP_LIKE
The purpose of REGEXP or RLIKE or ~ or REGEXP_LIKE is to evaluate a regular expression inside a SQL query. Use format:
REGEXP_LIKE(string, 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.
sQuery = "SELECT 'abcde' REGEXP 'a[bcd]{3}e' AS result"
QRY is Data Source
HExecuteSQLQuery(QRY, hQueryDefault, sQuery)
HReadFirst(QRY)
let bResult = QRY.result
// bResult is set to True
sQuery = "SELECT 'abcde' REGEXP 'a[bcd]{2}e' AS result"
HExecuteSQLQuery(QRY, hQueryDefault, sQuery)
HReadFirst(QRY)
bResult = QRY.RESULT
// bResult is set to False
This page is also available for…
|
|
|
| |
| Click [Add] to post a comment |
|
| |
|
| |
| |
| |
| |
| |
| |
| | |
| |