 |
 Warning: Content of this document will be soon extended.
| Displayed version: |
|
| | 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):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 HyperFileSQL, see Commands that can be used in a SQL query managed by HyperFileSQL. Notes: - These statements can be used:
- in the SQL code of the queries created in the query editor. Then, these queries will be run by HExecuteQuery.
- in the SQL code of the queries run by HExecuteSQLQuery.
- Unless stated otherwise, these functions can be used with all types of data sources (Oracle, Access, SQL Server, ...).
|
|
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 laterELTELT 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 16ELTELT 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 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 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 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.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 only be used 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 with one of the following data sources: Oracle, HyperFileSQL Classic or HyperFileSQL Client/Server.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 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 16SPLIT_PARTSPLIT_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 the left to the 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 the space characters on the right.
- without a list of characters.
The characters are deleted from the right to the 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 | | | 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 the right to the 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 the right to the 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: REVERSE(Initial String) 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(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 the right to the 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]) |
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 ShippingCompany Express Delivery Certified Pick up Result returned by the STRING_AGG function:
ShippingCompany;Express Delivery;Certified;Pick up 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 ShippingCompany Express Delivery Certified Pick 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
ShippingCompany
Express Delivery
Certified
Pick upResult 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 given character or 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 given character or 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: SPACE(N) New in version 16SPACESPACE 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 laterLCASELCASE 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 16LCASELCASE 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 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: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 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, ...) |
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 only be used on a HyperFileSQL 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())
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
|
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
|
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
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 |
BOTTOM
BOTTOM returns the last n records found in the result of a query.BOTTOM can only be used on a HyperFileSQL 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 |
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' |
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: 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 |
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 |
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 |
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 |
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 |
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 |
TRUNC
TRUNC is used to truncate the date to the specified format.Use format: TRUNC(Date, Format) 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 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 space characters to be replaced 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 Full-text search and 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: MD5(String) 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(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 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: SHA(String)
SHA1(String) 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(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) |
|
| | | |
| |
Keywords: ADD_MONTH (SQL language), ASCII, AVG (SQL language), BOTH (SQL language), BOTTOM (SQL language), CASE (SQL language), CHAR_LENGTH (SQL language), CHARACTER_LENGTH (SQL language), COALESCE (SQL language), CONCAT (SQL language), More keywords... , COUNT (SQL language), DECODE (SQL language), DISTINCT (SQL language), ELT (SQL language), EXTRACTVALUE (SQL language), FIELD (SQL language), WLanguage functions, GREATEST (SQL language), IF NULL (SQL language), INSTR (SQL language), IS NULL (SQL language), LAST_DAY (SQL language), LCASE (SQL language), LEADING (SQL language), LEAST (SQL language), LEFT (SQL language), LEN (SQL language), LENGTH (SQL language), LOWER (SQL language), LPAD (SQL language), LTRIM (SQL language), MATCH AGAINST (SQL language), Max, MD5 (SQL language), MID (SQL language), MIN (SQL language), MONTHS_BETWEEN (SQL language), NEW_TIME (SQL language), NEXT_DAY (SQL language), NVL (SQL language), OCTET_LENGTH (SQL language), PATINDEX (SQL language), POSITION (SQL language), REPEAT (SQL language), REPLACE (SQL language), REVERSE (SQL language), RIGHT (SQL language), ROUND (SQL language), RPAD (SQL language), RTRIM (SQL language), SHA (SQL language), SHA1 (SQL language), SOUND LIKE (SQL language), SOUND2 LIKE (SQL language), SOUNDEX (SQL language), SOUNDEX2 (SQL language), SPACE (SQL language), SPLIT_PART (SQL language), SQL, STRING_AGG (SQL language), SUBSTR (SQL language), SUBSTRING (SQL language), SUM (SQL language), SYSDATE (SQL language), TOP (SQL language), TRAILING (SQL language), TRANSLATE (SQL language), TRIM (SQL language), TRUNC (SQL language), UCASE (SQL language), Unicode, UPPER (SQL language)
Minimum required version: 11
Community: 0 annotation   Please login to annotate | Terms of service
| |
| |
| | | |