PC SOFT

ONLINE HELP
FOR WINDEV, WEBDEV AND WINDEV MOBILE

Version: 19.0

WinDevWebDev - Server codeWebDev - Browser codeWinDev MobileReports and QueriesWindowsLinuxWindows MobileWindows PhoneWindows Store appsAndroidAndroid Widget iPhone/iPadJavaPHPAjaxUser code (UMC)External languageHFSQLHFSQL Client/ServerStored proceduresHyper File 5.5OLE DBODBCNative Accesses

SQL functions that can be used in the SQL queries

Overview
The following SQL functions can be used on the queries written in SQL code (classified by theme):

  • extracting characters:
Versions 16 and later
- ELT
- EXTRACTVALUE
New in version 16
- ELT
- EXTRACTVALUE
- ELT
- EXTRACTVALUE
- LEFT
- RIGHT
- MID,
Versions 16 and later
- SPLIT_PART
New in version 16
- SPLIT_PART
- SPLIT_PART
- SUBSTR and SUBSTRING
  • deleting characters:
- LTRIM
- RTRIM
- TRIM
  • substituting characters:
- REPLACE
Versions 16 and later
- REVERSE
New in version 16
- REVERSE
- REVERSE
- TRANSLATE
  • merging strings:
- CONCAT
Versions 17 and later
- STRING_AGG
New in version 17
- STRING_AGG
- STRING_AGG
  • string completion:
- LPAD
Versions 16 and later
- REPEAT
New in version 16
- REPEAT
- REPEAT
- RPAD
Versions 16 and later
- SPACE
New in version 16
- SPACE
- SPACE
  • modifying the case of a character string:
- LOWER
- UPPER
Versions 16 and later
- LCASE
- UCASE
New in version 16
- LCASE
- UCASE
- LCASE
- UCASE
  • size of a character string:
- LEN and LENGTH
- CHARACTER_LENGTH, CHAR_LENGTH and OCTET_LENGTH
  • position of a character string:
- INSTR
Versions 16 and later
- FIELD
New in version 16
- FIELD
- FIELD
- PATINDEX
- POSITION
  • number of records in a file:
- COUNT
  • calculating numeric values:
- AVG
- MAX
- MIN
- SUM

See the mathematical SQL functions
  • selecting the first n records or the last n records:
- TOP
- BOTTOM
  • ASCII code
- ASCII
  • Unicode code
- UNICODE
  • phonetic
- SOUNDEX, SOUND LIKE
- SOUNDEX2, SOUND2 LIKE
  • managing the dates
- ADD_MONTH
- LAST_DAY
- MONTHS_BETWEEN
- NEW_TIME
- NEXT_DAY
- ROUND
- SYSDATE
- TRUNC
  • comparison functions
- COALESCE
Versions 16 and later
- GREATEST
New in version 16
- GREATEST
- GREATEST
- IF NULL, IS NULL
Versions 16 and later
- LEAST
New in version 16
- LEAST
- LEAST
- NVL
  • Conditional statement
- DECODE
- CASE
  • "Full-text" search
- MATCH AGAINST
  • Checking
Versions 16 and later
- MD5
- SHA and SHA1
New in version 16
- MD5
- SHA and SHA1
- MD5
- SHA and SHA1

See a specific documentation about the SQL language for more details.

To find out all the SQL commands (functions, clauses, operators, ...) that can be used in a SQL query managed by HFSQL, see Commands that can be used in a SQL query managed by HFSQL.

Notes:

  • 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, ...).
Note: From version 19, HFSQL is the new name of HyperFileSQL.

SQL functions

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 later
ELT
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 16
ELT
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
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 later
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 as 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 16
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 as 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 as 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.

Native Accesses This function cannot be used on a PostgreSQL data source.

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 the 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 later
SPLIT_PART
SPLIT_PART divides 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 16
SPLIT_PART
SPLIT_PART divides 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 divides 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

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).
-- Deleting the characters
SELECT LTRIM(NAME, 'Ms.')
FROM CUSTOMER
-- Deleting the space character
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 the 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 the space characters on the left and on the right.
  • without a character string found at the beginning or at the end of the string.
  • without a character string found at the beginning of the string.
  • without a character string found at the end of the 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)

REPLACE

REPLACE returns a character string:
  • by replacing all the occurrences of a word found in character 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 later
REVERSE
REVERSE returns a character string in which the order of characters is the reversed order of the initial string.

Use format:

REVERSE(Initial String)
New in version 16
REVERSE
REVERSE returns a character string in which the order of characters is the reversed order of the initial string.

Use format:

REVERSE(Initial String)
REVERSE
REVERSE returns a character string in which the order of characters is the reversed order of the initial string.

Use format:

REVERSE(Initial String)

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 the 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 strings together.

Use format:

CONCAT(String 1, String 2 [,..., String N])

Native Accesses CONCAT is not supported by Sybase.

Versions 17 and later

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 the delivery file:
  • Shipping company
  • Express Delivery
  • Certified
  • Picked up
Result returned by the STRING_AGG function: "ShippingCompany;Express Delivery;Certified;Pick up".
New in version 17

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 the delivery file:
  • Shipping company
  • Express Delivery
  • Certified
  • Picked up
Result returned by the STRING_AGG function: "ShippingCompany;Express Delivery;Certified;Pick up".

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 the delivery file:
  • Shipping company
  • Express Delivery
  • Certified
  • Picked up
Result returned by the STRING_AGG function: "ShippingCompany;Express Delivery;Certified;Pick up".

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 later
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
New in version 16
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
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 later
SPACE
SPACE returns a string containing N space characters.

Use format:

SPACE(N)
New in version 16
SPACE
SPACE returns a string containing N space characters.

Use format:

SPACE(N)
SPACE
SPACE returns a string containing N space characters.

Use format:

SPACE(N)

LOWER

LOWER converts an expression into lowercase characters.

LOWER cannot be used on 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 on 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 later
LCASE
LCASE returns a string with all the characters 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 16
LCASE
LCASE returns a string with all the characters 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 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 later
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
New in version 16
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
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:

LEN(Initial expression)

Example: The following SQL code is used to find out the size of the customer names:

SELECT LEN(NAME)
FROM CUSTOMER

LENGTH

LENGTH can only be used 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 later
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, ...)
New in version 16
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, ...)
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, ...)

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 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.

Use format:

MAX(Item)  

MAX(DISTINCT Item)

Example: The following SQL code is used to find out the maximum salary of the employees:

SELECT MAX(Salary)
FROM EMPLOYEE
-- Equivalent to:
-- SELECT MAX(DISTINCT Salary)
-- FROM EMPLOYEE

MIN

MIN returns the lowest 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 the employees:

SELECT MIN(Salary)
FROM EMPLOYEE
-- Equivalent to:
-- SELECT MIN(DISTINCT 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 the different salaries:
SELECT SUM(DISTINCT Salary)
FROM EMPLOYEE

Note:

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©
FROM MYFILE

This syntax must be replaced by the following syntax:

SELECT (A*B) AS C, SUM(A*B)
FROM MYFILE

TOP

TOP returns the first n records found in the result of a query.

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

Note: TOP should be used on a sorted query. Otherwise, the records returned by TOP will be selected according to their record number.

BOTTOM

BOTTOM returns the last n records found in the result of a query.

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

Note: BOTTOM should be used with a sorted query. Otherwise, the records returned by BOTTOM will be selected according to their record number.

ASCII

ASCII returns the ASCII code:
  • of a character.
  • of the 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 a character
ASCII(Character)
-- ASCII code of the first character found in a string
ASCII(Character string)

UNICODE

UNICODE returns the integer value defined by the Unicode standard:
  • of a character.
  • of the first character found in a string.
Use format:

-- Unicode code of a character
UNICODE(Character)
-- Unicode code of the first character found in a string
UNICODE(Character string)

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'

Note: SOUNDEX used on different databases (HFSQL, Oracle, MySQL, ...) may return different results according to the database used.

Native Accesses SOUND LIKE is not supported by Oracle, MySQL, Progress or Informix.

SOUNDEX is not supported by Informix.

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'

Native Accesses SOUNDEX2 and SOUNDEX2 LIKE are not supported by Oracle, SQL Server, MySQL, Progress, Informix or DB2.

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

Native Accesses ADD_MONTHS is not supported by SQL Server, MySQL, Informix, DB2 or Sybase.

LAST_DAY

LAST_DAY is used to find out the date of the last day for the specified month.

Use format:

LAST_DAY(Date)

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

Native Accesses LAST_DAY is not supported by Informix, DB2 or Sybase.

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

Native Accesses MONTHS_BETWEEN is not supported by MySQL, Informix, DB2 or Sybase.

NEW_TIME

NEW_TIME is used to find out a date after conversion of time zone.

Use format:

NEW_TIME(Date, Time Zone 1, Time Zone 2)

Example:

SELECT NEW_TIME ('200311010145', 'AST', 'MST') AS NTI
FROM CUSTOMER

Note: If the time zones correspond to an empty string (""), the result will be a DateTime value to 0.

Native Accesses NEW_TIME is not supported by SQL Server, MySQL, Progress, Informix, DB2 or Sybase.

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:

NEXT_DAY(Date, Day)

Example:

SELECT NEXT_DAY('20071007','Sunday') AS NXD
FROM CUSTOMER

Native Accesses NEXT_DAY is not supported by SQL Server, MySQL, Progress, Informix, DB2 or Sybase.

ROUND

ROUND is used to round the date to the specified format.

Use format:

ROUND(Date, Format)

Example:

SELECT ORDDATE,
ROUND(ORDDATE,'YYYY') AS TR
FROM ORDERS

Native Accesses ROUND is not supported by Progress, DB2 and Sybase.

SYSDATE

SYSDATE is used to find out the current date and time.

Use format:

SYSDATE

Example:

SELECT SYSDATE AS SY FROM CUSTOMER WHERE CUSTOMERID=1

Native Accesses SYSDATE is not supported by Informix and Sybase.

TRUNC

TRUNC is used to truncate the date to the specified format.

Use format:

TRUNC(Date, 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"
  • Months: "MM", "MON", "MONTH"
  • First day of the month that is the same day of the 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

Native Accesses TRUNC is not supported by SQL Server, MySQL, Progress, DB2 and Sybase.

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

Native Accesses COALESCE is not supported by Progress or Informix.

Versions 16 and later
GREATEST
GREATEST returns the greatest value of the elements passed in parameter.

Use format:

GREATEST(Param1, Param2, ...)
New in version 16
GREATEST
GREATEST 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 later
LEAST
LEAST returns the lowest value of the elements passed in parameter.

Use format:

LEAST(Param1, Param2, ...)
New in version 16
LEAST
LEAST 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

Native Accesses DECODE is not supported by SQL Server, MySQL, Progress, Informix, DB2, Sybase and ACCESS.

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 wordThe 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 characterSearches for one of the words.

Example: "WinDev WebDev" searches for texts containing either "WinDev" or "WebDev".

A word preceded by the "+" signThe specified word is mandatory.

Example: "+WinDev" searches for the texts that necessarily contain "WinDev".

A word preceded by the "-" signThe 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 "~" signIf the text contains the specified word, the relevance will be reduced.
One or more words enclosed in quotesThe 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 "*" signThe 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 Name 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 Full-text search and index for more details.

Versions 16 and later
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:

MD5(String)
New in version 16
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:

MD5(String)
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:

MD5(String)

Versions 16 and later
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:

SHA(String)

SHA1(String)
New in version 16
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:

SHA(String)

SHA1(String)
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:

SHA(String)

SHA1(String)
Minimum required version
  • Version 11
This page is also available for…
Comments
Click [Add] to post a comment