SQL functions that can be used in SQL queries

ONLINE HELP
WINDEV, WEBDEV AND WINDEV MOBILE

Version:

Home | Sign in | English

Help / Editors / Query editor / SQL

Overview
SQL functions
Scalar expression
LEFT
RIGHT
MID, SUBSTR and SUBSTRING
MID
SUBSTR
BTRIM
LTRIM
RTRIM
TRIM
OVERLAY
REPLACE
TRANSLATE
CONCAT
GROUP_CONCAT
STRING_AGG
LPAD
RPAD
LOWER
UPPER
LEN/LENGTH
LEN
LENGTH
INSTR
PATINDEX
POSITION
COUNT
AVG
MAX
MIN
SUM
EVERY
TOP
BOTTOM
LIMIT
ASCII
UNICODE
CURRENT_USER
SYSTEM_USER
USER_NAME
SOUNDEX, SOUND LIKE
SOUNDEX2, SOUND2 LIKE
ADD_MONTHS
LAST_DAY
DAY
DAYOFMONTH
DAYOFWEEK
DAYOFYEAR
EOMONTH
YEAR and MONTH
CURRENT_TIMESTAMP
GETDATE
GETUTCDATE
DATEADD
DATEDIFF and DATEDIFF_BIG
DATEPART
DATETIMEFROMPARTS
DATEFROMPARTS
MONTHS_BETWEEN
NEW_TIME
NEXT_DAY
ROUND
SYSDATE
TRUNC
ISDATE
COALESCE
NVL, IFNULL, ISNULL
NULLIF
DECODE
CASE
MATCH AGAINST
Bitwise operators and functions
IS JSON XXX
JSON_VALUE
JSON_QUERY
JSON_EXISTS
JSON_OBJECT
JSON_OBJECTAGG
JSON_ARRAY
JSON_ARRAYAGG

WINDEV

WEBDEV

WINDEV Mobile

Others

See also

SQL functions that can be used in SQL queries

Overview

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


extract characters:	ELT EXTRACTVALUE LEFT RIGHT MID, SPLIT_PART SUBSTR and SUBSTRING
delete characters:	BTRIM LTRIM RTRIM TRIM
substitute characters:	OVERLAY REPLACE REVERSE TRANSLATE
merge strings:	CONCAT GROUP_CONCAT STRING_AGG
string padding:	LPAD REPEAT RPAD SPACE
convert to string:	TO_CHAR CHAR CHR CAST CONVERT
change the case of a string:	INITCAP LOWER UPPER LCASE UCASE
string length:	LEN and LENGTH CHARACTER_LENGTH, CHAR_LENGTH and BYTE_LENGTH
position of character string:	INSTR FIELD FIND_IN_SET PATINDEX POSITION
number of records in a file:	COUNT
calculate numeric values:	AVG MAX MIN SUM EVERY See the mathematical SQL functions.
select the first or last n records n records:	TOP BOTTOM LIMIT
ASCII code	ASCII
Miscellaneous	UNICODE CURRENT_USER SYSTEM_USER USERNAME
phonetic	SOUNDEX, SOUND LIKE SOUNDEX2, SOUND2 LIKE
managing dates	ADD_MONTHS LAST_DAY MONTHS_BETWEEN NEW_TIME NEXT_DAY ROUND SYSDATE TRUNC DAY DAYOFMONTH DAYOFWEEK DAYOFYEAR CURRENT_TIMESTAMP GETDATE GETUTCDATE ISDATE EOMONTH DATEADD DATEDIFF, DATEDIFF_BIG DATETIMEFROMPARTS, DATEFROMPARTS, DATEPART MONTH, YEAR
comparison functions	COALESCE GREATEST IFNULL, ISNULL NULLIF LEAST NVL
Conditional statement	DECODE CASE
"Full-text" search	MATCH AGAINST
Verification	MD5 SHA and SHA1
Regular expression	REGEXP or NOT REGEXP (negation) RLIKE or NOT RLIKE (negation) ~ or!~ (negation) REGEXP_LIKE or NOT REGEXP_LIKE (negation)
Bitwise functions and operators	BITAND, BITOR, BITXOR, BITNOT BITANDNOT
JSON operators	JSON_OBJECT, JSON_OBJECTAGG JSON_ARRAY, JSON_ARRAYAGG IS JSON JSON_EXISTS JSON_VALUE JSON_QUERY

For more details on SQL functions, please refer to a specific SQL documentation.

To discover all the SQL commands (functions, clauses, operators, etc.) that can be used in an SQL query managed by HFSQL, see Commands that can be used in an SQL query managed by HFSQL.

Remarks:

These statements can be used:
- in the SQL code of queries created in the query editor. Then, these queries will be run by HExecuteQuery.
- in the SQL code of queries run by HExecuteSQLQuery.
Unless stated otherwise, these functions can be used with all the types of data sources (Oracle, Access, SQL Server, and so on).

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"

ELT
ELT returns the nth character string found in a list of strings.

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')

EXTRACTVALUE
EXTRACTVALUE is used to handle XML strings. This function returns the text (CDATA) of the first text node that is a child of the element corresponding to the XPATH expression. If several correspondences are found, the content of the first child text node of each node corresponding to the XPATH expression is returned in the format of a string delimited by space characters.

Format:

EXTRACTVALUE(XML fragment, XPATH expression)

<XML fragment> must be a valid XML fragment. It must contain a unique root.

Example: The following code is used to count the elements found:

SELECT EXTRACTVALUE('<a><b/></a>', 'count(/a/b)')
FROM CUSTOMER
WHERE CUUNIKKEY=1

LEFT

LEFT extracts the left part (which means the first characters) of an expression.

This function cannot be used on a PostgreSQL data source.

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.

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 substring found in the content of an expression from a given position.

If the given position corresponds to:

a negative number, the extraction will start from the end of string.
0, the extraction will start from the beginning of the string (equivalent to position 1).

If the absolute value of the given position (returned by ABS) is greater than the number of characters found in the initial expression, an empty string is returned.

Example: The following SQL code is used to extract the cities whose second character is 'A':

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

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.

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

SPLIT_PART
SPLIT_PART divides a character string according to the specified separator and returns the nth part of the string.

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

BTRIM

BTRIM deletes a character string found at the beginning or at the end of a string.

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 space characters on the left.
without a list of characters.

The characters are deleted from left to right. This deletion is case sensitive (lowercase/uppercase characters). This deletion stops when a character that does not belong to the specified list is found.

The deletions of specific characters cannot be performed on an Access or SQL Server data source.

Format:

-- 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 that was found between the title and the name).

-- Deleting 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 space characters on the right.
without a list of characters.

The characters are deleted from right to left. This deletion is case sensitive (lowercase/uppercase characters). This deletion stops when a character that does not belong to the specified list is found.

The deletions of specific characters cannot be performed on an Access or SQL Server data source.

Format:

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

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)

OVERLAY

OVERLAY replaces a string with another one.

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 occurrences of a word found in a string.

The replacement is performed from right to left. This replacement is case sensitive (uppercase/lowercase characters). This replacement stops when a character that does not belong to the specified string is found.

Format:

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

REVERSE
REVERSE returns a character string in which the order of characters is the reversed order of the initial string.

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

Format:

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

Format:

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

CONCAT is not supported by Sybase.

GROUP_CONCAT

GROUP_CONCAT concatenates the non-null values of an item from a series of records into a string.

Each value can be separated using a specific separator (spaces, ";", etc.).

This function can be used to group results into one line.

If the GROUP BY statement is used, the different values can be grouped by a different criterion.

Format:

GROUP_CONCAT(<Column name> SEPARATOR <Separator>)

where:

<Column name> represents the item that contains the values to be grouped.
<Separator> represents the character that separates each value.

Example: The following SQL code gets the list of weekdays and weekend days, each in a line. Days are separated by a semicolon.

The "DAYOFWEEK" table handled is as follows:

DayType	DayName

Weekday	Monday
Weekday	Tuesday
Weekday	Wednesday
Weekday	Thursday
Weekday	Friday
Weekend	Saturday
Weekend	Sunday

SELECT DayType, GROUP_CONCAT(DayName SEPARATOR ';')
FROM DAYOFWEEK
GROUP BY DayType

The result will be displayed as follows:

'Weekday', 'Monday;Tuesday;Wednesday;Thrusday;Friday'
'Weekend', 'Saturday;Sunday'

STRING_AGG

STRING_AGG is used to concatenate non-null strings from a list of values.

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
Pick up

Result returned by the STRING_AGG function: "Shipping company;Express delivery;Certified;Pick up".

LPAD

LPAD returns a string whose size is defined. To reach the requested size, the string is left-padded:

by space characters.
by a character or by a given string.

Format:

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

REPEAT
REPEAT returns a character string containing n times the repetition of the initial string.

If n is less than or equal to 0, the function returns an empty string.
If the initial string or n is NULL, the function returns NULL.

Format:

REPEAT(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 right-padded:

by space characters.
by a character or by a given string.

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)

SPACE
SPACE returns a string containing N space characters.

Format:

SPACE(N)

TO_CHAR
TO_CHAR is used to convert into character string:

a datetime,
a numeric value.

Format:

1. Convert a datetime value:

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.
- D: Day in the Julian calendar. The number of days since January 1st, 4712 BC.
- MI: Minutes (0-59).
- MM: Month (01-12 ; January = 01).
- MON: Abbreviated month.
- MONTH: Full month in letters.
- PM, P.M.: Indicator of PM meridian for the time (Post Meridian).
- Q: Quarter (1, 2, 3, 4 ; January - March = 1).
- RM: Month in Roman numbers (I-XII ; January = I).
- SS: Seconds (0-59).
- SSSSS: Number of seconds passed since midnight (0-86399).
- WW: Number of the week (1-53) with week 1 starting from the first day of the year.
- W: Number of the week in the month (1-5) with week 1 starting from the first day of the month.
- X: Separator for the fraction of seconds. Example: 'HH:MI:SSXFF'.
- Y,YYY: Year with a comma. The comma is used to separate the grouping of the local.
- YEAR, SYEAR: Year S means BC, Year signed with a minus sign.
- YYYY, SYYYY: Year on 4 digits ; S means BC, Year signed with a minus sign.
- YYY, YY, Y: Last number of the year 3, 2, or 1 number.
The <datetime options> parameter is a character string containing the following keywords:
- "NLS_DATE_LANGUAGE=language in en"
- "NLS_NUMERIC_CHARACTERS ='dg'": 'dg' is a 2-character string whose first one corresponds to the decimal separator and whose second one corresponds to the group separator (between thousand and hundred for example).
  Example: ‘NLS_DATE_LANGUAGE=french, NLS_NUMERIC_CHARACTERS =, ‘

2. Converting a numeric value:

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

CHAR
CHAR is used to convert an ASCII code (integer) into character.

Format:

CHAR(<ASCII Code>)

<ASCII code> is a numeric and corresponds to the ASCII character to convert, between 0 and 255. Otherwise, the character returned by the function is NULL.

The result of the function is the character corresponding to the ASCII code of <ASCII code>.

Remark: The result depends on the current character set.

CHR
CHR is used to convert an ASCII code (integer) into character.

CHR(<ASCII Code>)

<ASCII code> is a numeric and corresponds to the ASCII character to convert, between 0 and 255. Otherwise, the character returned by the function is NULL.

The result of the function is the character corresponding to the ASCII code of <ASCII code>.

Remarks:

The result depends on the current character set.
In UTF8, the integer sent is interpreted as a "code point" ; otherwise, the character returned corresponds to the character modulo 256.

CAST
CAST is used to convert a data from a type to another one.

CAST(Expression AS Type)

<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 of size
VARCHAR(Size)	String of size
CHARACTER VARYING(Size)	String of size
CHAR VARYING(Size)	String of size
NVARCHAR(Size)	Unicode string on size
VARCHAR(Size) BINARY	Binary string of size
BINARY(Size)	Binary string of size
VARBINARY(Size)	Binary string of size
BLOB	Binary memo
CLOB	Text memo
TEXT	Text memo
NCLOB	Unicode memo
NTEXT	Unicode memo
NUMBER(Precision)	Integer
NUMBER(Precision, scale)	Integer
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:

CAST(126.12 AS INTEGER)

This code returns: "126".

CONVERT

CONVERT is used to convert a character string from a character set to another one.

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.

INITCAP
INITCAP returns a character string where the first letter of each word is written in uppercase character and all the other letters are written in lowercase characters.

Format:

INITCAP(<String>)

Example:

INITCAP ('grEat Weather today')

This code returns: 'Great Weather Today'.

LOWER

LOWER converts an expression to lowercase.

LOWER cannot be used on an Access data source.

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

UPPER cannot be used on an Access data source.

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

LCASE
LCASE returns a string with all the characters in lowercase according to the current character set.

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

UCASE
UCASE returns a string with all the characters in uppercase according to the current set of characters.

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 (the number of characters) of an expression.

This size includes all the characters, including the space characters and the binary 0.

LEN

LEN can be used on all the types of data sources excluding the Oracle data sources. For the Oracle data sources, use LENGTH.

Format:

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.

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.

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

FIELD
FIELD returns the index of the sought string in the list.

If the string is not found, the function returns 0.

Format:

FIELD(String to Find, String1, String2, ...)

FIND_IN_SET
FIND_IN_SET returns the position of a string in a list of values.

If the string is not found, the function returns 0.

Format:

FIND_IN_SET(<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 an HFSQL Classic or SQL Server data source.

Format:

PATINDEX(Value to Find, Expression)

Example: The table below presents the position of the first occurrence found according to the sought values:

	Search value

City name	'%E%'	'%E_'	'%AR%'
MONTPELLIER	6	10	0
PARIS	0	0	2
TARBES	5	5	2
TOULOUSE	8	0	0
VIENNE	3	0	0

POSITION

POSITION returns the position of a character string in an expression.

Format:

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

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.

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.

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

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

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 with the following syntax:

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

EVERY

EVERY is an aggregate function (like SUM for example), which means that the functions acts on a group of data and returns a value.

EVERY returns:

True if all the received arguments are checked and true.
False if at least one of the arguments is not checked.

Format:

EVERY(Expression 1, Expression 2, ..., Expression N)

Example: The following SQL code is used to get the list of companies with employees whose salary is greater than 10000:

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

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.
You have the ability to pass a parameter to TOP. The parameter can be:
- defined in the query editor. For more details, see Select query: Displaying the first/last n records.
- defined in the code editor directly. For example:
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 result of a query.

BOTTOM can only be used on an HFSQL data source.

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 recommend to use BOTTOM on a sorted query. Otherwise, the records returned by BOTTOM will be selected according to their record number.
You have the ability to pass a parameter to BOTTOM. The parameter can be:
- defined in the query editor. For more details, see Select query: Displaying the first/last n records.
- defined in the code editor directly.

LIMIT

LIMIT returns the first n records of the query result.

LIMIT cannot be used on an Oracle or PostgreSQL data source.

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 not recommended to use LIMIT on a sorted query. Otherwise, the records returned by TOP will be selected according to their record number.
You have the ability to pass a parameter to LIMIT. The parameter can be:
- defined in the query editor. For more details, see Select query: Displaying the first/last n records.
- defined in the code editor directly.

ASCII

ASCII returns the ASCII code:

of a character.
of first character found in a string.

If the character or the character string corresponds to an empty string (""), ASCII returns 0.

Format:

-- 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 a character.
of first character found in a string.

Format:

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

CURRENT_USER

CURRENT_USER returns the username for the current connection.

Format:

CURRENT_USER()

Example: The following code updates the author of the modification performed in CUSTOMER table:

UPDATE CUSTOMER SET USER=CURRENT_USER() WHERE CUSTOMERID=1

SYSTEM_USER

SYSTEM_USER returns the username for the current connection.

Format:

SYSTEM_USER()

Example: The following code updates the author of the modification performed in CUSTOMER table:

UPDATE CUSTOMER SET USER=SYSTEM_USER() WHERE CUSTOMERID=1

USER_NAME

USER_NAME returns the username for the current connection.

Format:

USER_NAME()

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

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.

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

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'

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.

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

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.

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

LAST_DAY is not supported by Informix, DB2 or Sybase.

DAY

DAY returns the day of the month, which means a number included between 1 and 31.

Format:

DAY(Date)

DAYOFMONTH

DAYOFMONTH returns the day in the month (included between 1 and 31).

Format:

DAYOFMONTH(Date)

DAYOFWEEK

DAYOFWEEK returns the day in the week (1 for Sunday, 2 for Monday, etc.).

Format:

DAYOFWEEK(Date)

DAYOFYEAR

DAYOFYEAR returns the day in the year (included between 1 and 366).

Format:

DAYOFYEAR(Date)

EOMONTH

EOMONTH returns the last day of the month of the specified date.

Format:

EOMONTH(Start date [, Number of months to add ] )

where:

Start date: Date for which to return the last day of the month.
Number of months to add: Number of months to add to the start date before calculating the last day of the month.

YEAR and MONTH

YEAR and MONTH get the year and the month of a date, respectively.

Format:

YEAR date

MONTH date

where date corresponds to:

the date written out: YEAR - MONTH - DAY (YYYYMMDD or YYYY-MM-DD)
a partial date. in this case, missing data is represented by 0
only a year (YEAR)
only a year and a month (YEAR-MONTH)

Remark:

Dates are decoded from left to right: for a date to be valid, all available information must be valid.
If the format is invalid, the function returns NULL.

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP returns the local time of server (in datetime format).

Format:

CURRENT_TIMESTAMP()

GETDATE

GETDATE returns the local time of server (in datetime format).

Format:

GETDATE()

GETUTCDATE

GETUTCDATE returns the UTC time of server (in datetime format).

Format:

GETUTCDATE()

DATEADD

DATEADD adds a value to the start date and returns the new date.

Format:

DATEADD(Part to add, number, date )

where:

Part to add: Part of the date to which the number will be added. This parameter can be:
Date part Abbreviations
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw, w
hour hh
minute mi, n
second ss, s
millisecond ms
microsecond mcs
nanosecond ns
Number: integer corresponding to the number of units to be added.
date: date or datetime to be used.

DATEDIFF and DATEDIFF_BIG

DATEDIFF calculates the difference between two dates in a given unit. The return value is a signed integer.

DATEDIFF_BIG calculates the difference between two dates in a given unit. The return value is a signed big integer.

Format:

DATEDIFF(Part used, Start date, End date)

DATEDIFF_BIG(Part used, Start date, End date)

where:

Part used: Part of the date on which the calculation will be performed. This parameter can be:
Date part Abbreviations
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw, w
hour hh
minute mi, n
second ss, s
millisecond ms
microsecond mcs
nanosecond ns
Start date: start date or datetime for the calculation.
End date: end date or datetime for the calculation.

DATEPART

DATEPART returns the integer corresponding to the requested part of the specified datetime.

Format:

DATEPART(Part used, date)

where:

Part used: Part of the date to be extracted. This parameter can be:
Date part Abbreviations
year yy, yyyy
quarter qq, q
month mm, m
dayofyear dy, y
day dd, d
week wk, ww
weekday dw, w
hour hh
minute mi, n
second ss, s
millisecond ms
microsecond mcs
nanosecond ns
Start date: date or datetime used.

DATETIMEFROMPARTS

DATETIMEFROMPARTS returns a datetime value that corresponds to the specified elements.

Format:

DATETIMEFROMPARTS(Year, Month, Day, Hours, Minutes, Seconds, Milliseconds)

DATEFROMPARTS

DATEFROMPARTS returns a date value that corresponds to the specified elements.

Format:

DATETIMEFROMPARTS(Year, Month, Days)

MONTHS_BETWEEN

MONTHS_BETWEEN is used to find out the number of months between two specified dates.

Format:

MONTHS_BETWEEN(Date1, Date2)

Example: The following SQL code is used to select the orders placed between two dates:

SELECT 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,
-- Number of months divided by 12 in real: number of years
CAST(MONTHS_BETWEEN(SYSDATE,CUSTOMER.DATE_OF_BIRTH)/12 AS FLOAT) AS Age
FROM
CUSTOMER
WHERE
-- customers over 18 only
Age >= 18

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

NEW_TIME

NEW_TIME is used to find out a date after converting its time zone.

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.

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.

Format:

NEXT_DAY(Date, Day)

Example:

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

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.

Format:

ROUND(Date, Format)

Example:

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

ROUND is not supported by Progress, DB2 and Sybase.

SYSDATE

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

Format:

SYSDATE

Example:

SELECT SYSDATE AS SY FROM CUSTOMER WHERE CUSTOMERID=1

SYSDATE is not supported by Informix and Sybase.

TRUNC

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

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"
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"
Hour: "HH", "HH12", "HH24"
Minutes: "MI"

Example:

SELECT ORDDATE,
TRUNC(ORDDATE) AS TR
FROM ORDERS
WHERE ORDUNIKKEY

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

ISDATE

ISDATE is used to determine if an expression corresponds to a date. This function returns:

1 if the expression corresponds to a date or datetime
0 otherwise.

Format:

ISSDATE(Expression)

Example:

SELECT Date, ISDATE(Date) FROM ORDER WHERE OrderID=50

COALESCE

COALESCE is used to find out the first non-null expression among its arguments.

Format:

COALESCE(Param1, Param2, ...)

Example:

SELECT COALESCE(hourly_wage, salary, commission) AS Total_Salary FROM wages

COALESCE is not supported by Progress or Informix.

GREATEST
GREATEST returns the greatest value of the elements passed as parameter.

Format:

GREATEST(Param1, Param2, ...)

LEAST
LEAST returns the lowest value of the elements passed as parameter.

Format:

LEAST(Param1, Param2, ...)

NVL, IFNULL, ISNULL

NVL is used to replace the null values of a column by a substitution value. ISNULL and IFNULL are identical.

ISNULL is used in SQL Server and IFNULL is used in MySQL or Progress databases.

Format:

NVL(Column name, Substitution value)

Example:

SELECT hourly_wage AS R1,NVL(hourly_wage,0) AS Total FROM wages

NULLIF

NULLIF returns a NULL value if the two specified expressions are equal.

Format:

NULLIF(expression1, expression2)

DECODE

DECODE is used to find out the operating mode of a IF .. THEN .. ELSE statement.

Format:

DECODE(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

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.

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.

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".
Word preceded by the "-" sign	The text must not contain the specified word. 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())

For more details on full-text search, see Search and full-text index.

MD5
MD5 calculates the MD5 check sum of the string passed as parameter. The returned value is a hexadecimal integer of 32 characters that can be used as hash key for example.

Format:

MD5(String)

SHA and SHA1
SHA and SHA1 calculate the 160-bit SHA1 check sum of the string passed as parameter according to the RFC 3174 standard (Secure Hash Algorithm). The returned value is a hexadecimal string of 40 characters or NULL if the argument is NULL. This function can be used for hashing the keys.

Format:

SHA(String)

SHA1(String)

REGEXP or RLIKE or ~ or REGEXP_LIKE

The purpose of REGEXP or RLIKE or ~ or REGEXP_LIKE is to evaluate a regular expression inside an SQL query.

Format:

string REGEXP expression

string RLIKE expression

string ~ expression

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

Bitwise operators and functions

The following are bitwise operators:

The corresponding functions are as follows:

BITAND,
BITOR,
BITXOR,
BITNOT,
BITANDNOT.

Example:

dsQuery is Data Source
sSQL is string = [
SELECT
1 | 2 AS op_or, -- 3
BITOR(1, 2) AS fct_or, -- 3
3 & 6 AS op_and, -- 2
BITAND(3, 6) AS fct_and, -- 2
~CAST(240 AS TINYINT) AS op_not, -- 15
BITNOT(CAST(240 AS TINYINT)) AS fct_not, -- 15
5 ^ 6 AS op_xor, -- 3
BITXOR(5, 6) AS fct_xor, -- 3
BITANDNOT(3,1) AS fct_andnot, -- 2
1 << 2 AS sl, -- 4
16 >> 2 AS sr -- 4
]

HExecuteSQLQuery(dsQuery, sSQL)

Trace("Expected:")
Trace("3 - 3 - 2 - 2 - 15 - 15 - 3 - 3 - 2 - 4 - 4")
Trace("Received:")
FOR EACH dsQuery
Trace(Replace(HRecordToString(dsQuery), TAB, " - "))
END

IS JSON XXX

"IS JSON xxx" commands are used to determine if an item is:

a JSON content (IS JSON),
a JSON content that represents an object (IS JSON OBJECT),
a JSON content that represents an array (IS JSON ARRAY),
...

Format:

IS [NOT] JSON [OBJECT|ARRAY|SCALAR|VALUE] (expression)

The command returns 1 if the expression contains valid data, 0 otherwise.

Example:

SELECT
Product.Characteristics IS JSON AS ItemISJSON,
Product.Characteristics IS JSON OBJECT AS ItemISJSONOBJECT,
Product.Characteristics IS JSON ARRAY AS ItemISJSONARRAY,
Product.Characteristics IS JSON SCALAR AS ItemISJSONSCALAR,
Product.Characteristics IS JSON VALUE AS ItemISJSONVALUE
FROM
Product

JSON_VALUE

The SQL "JSON_VALUE" command gets the value of an element contained in the JSON item.
Format:

JSON_VALUE(expression, path)

where:

expression corresponds to a variable containing JSON text
path corresponds to the property to extract.

Example:

SELECT
Product.Reference,
Product.Name,
Product.Characteristics,
JSON_VALUE(Product.Characteristics,
'$.brand' DEFAULT 'no brand' ON ERROR) AS Brand
FROM
Product

JSON_QUERY

The SQL "JSON_QUERY" command extracts an object or an array from a JSON string.
Format:

JSON_QUERY(expression, path)

where:

expression corresponds to a variable that contains JSON text
path specifies the object or the array to extract.

Example:

SELECT
Product.Reference,
Product.Name,
Product.Characteristics,
JSON_QUERY(Product.Characteristics, '$.colors' ) AS Colors
FROM
Product

JSON_EXISTS

The SQL "JSON_EXISTS" command allows you to get the records with a JSON item that contains a particular value.

Format:

JSON_EXISTS(expression, filter)

where:

expression corresponds to a variable that contains JSON text
filter corresponds to the data to be extracted.

Example:

SELECT
Product.Reference,
Product.Name,
Product.Characteristics
FROM
Product
WHERE
JSON_EXISTS(Product.Characteristics, '$.heel?(@ == true)')

JSON_OBJECT

The SQL "JSON_OBJECT" command returns a JSON object from any item. The retrieved JSON content is an object.

Example:

SELECT
Contact.ContactID,
JSON_OBJECT('FullName': Contact.lastname+' '+Contact.firstname,
'Address': Address+' '+PostalCode+' '+city+' '+country)
AS JSONContent
FROM
Contact
WHERE
ContactID <= 3

JSON_OBJECTAGG

The SQL "JSON_OBJECTAGG" command returns a JSON object containing key-value pairs for each specific key and value in a set of SQL values.

Example:

SELECT
JSON_OBJECTAGG(Contact.lastname+' '+Contact.firstname VALUE Address+' '+
PostalCode+' '+city+' '+country) AS JSONContent
FROM
Contact
WHERE
ContactID <= 3

JSON_ARRAY

The SQL "JSON_ARRAY" command returns a JSON array from any item. The JSON content retrieved is an array ( [xxx, ...] ).

Example:

SELECT
Contact.ContactID,
JSON_ARRAY(Contact.lastname+' '+Contact.firstname) AS JSONContent
FROM
Contact
WHERE
ContactID <= 3

JSON_ARRAYAGG

The SQL "JSON_ARRAYAGG" command returns a JSON array containing key-value pairs for each specific key and value in a set of SQL values. The JSON content retrieved is an array ( [xxx, ...] ).

Example:

SELECT country,
JSON_ARRAYAGG(customer.name)
FROM customer
GROUP BY country -> [ "romaric", "bob", "joe" ]