Login ProductsSalesSupportDownloadsAbout |

Home » Technical Support » DBISAM Technical Support » Product Manuals » DBISAM Version 4 Manual for RAD Studio XE (Delphi) » SQL Reference » Functions |

Functions

Introduction

DBISAM's SQL provides string functions, numeric functions, boolean functions, aggregate functions (used in conjunction with an SQL SELECT GROUP BY clause), autoinc functions, full text indexing functions, and data conversion functions.

String Functions

Use string functions to manipulate string values in SELECT, INSERT, UPDATE, or DELETE queries. DBISAM's SQL supports the following string functions:

LOWER or LCASE Function

The LOWER or LCASE function converts all characters in a string value to lowercase. The syntax is as follows:

In the following example, the values in the NAME column appear all in lowercase:

The LOWER or LCASE function can be used in WHERE clause string comparisons to cause a case-insensitive comparison. Apply LOWER or LCASE to the values on both sides of the comparison operator (if one of the comparison values is a literal, simply enter it all in lower case).

LOWER or LCASE can only be used with string or memo columns or constants.

UPPER or UCASE Function

The UPPER or UCASE function converts all characters in a string value to uppercase. The syntax is as follows:

Use UPPER or UCASE to convert all of the characters in a table column or character literal to uppercase. In the following example, the values in the NAME column are treated as all in uppercase. Because the same conversion is applied to both the filter column and comparison value in the WHERE clause, the filtering is effectively case-insensitive:

UPPER can only be used with string or memo columns or constants.

LENGTH Function

The LENGTH function returns the length of a string value as an integer value. The syntax is as follows:

In the following example, the length of the values in the Notes column are returned as part of the SELECT statement:

LENGTH can only be used with string or memo columns or constants.

SUBSTRING Function

The SUBSTRING function extracts a substring from a string. The syntax is as follows:

The second FROM parameter is the character position at which the extracted substring starts within the original string. The index for the FROM parameter is based on the first character in the source value being 1.

The FOR parameter is optional, and specifies the length of the extracted substring. If the FOR parameter is omitted, the substring goes from the position specified by the FROM parameter to the end of the string.

In the following example, the SUBSTRING function is applied to the literal string 'ABCDE' and returns the value 'BCD':

In the following example, only the second and subsequent characters of the NAME column are retrieved:

SUBSTRING can only be used with string or memo columns or constants.

LEFT Function

The LEFT function extracts a certain number of characters from the left side of a string. The syntax is as follows:

The FOR parameter specifies the length of the extracted substring.

In the following example, the LEFT function is applied to the literal string 'ABCDE' and returns the value 'ABC':

LEFT can only be used with string or memo columns or constants.

RIGHT Function

The RIGHT function extracts a certain number of characters from the right side of a string. The syntax is as follows:

The FOR parameter specifies the length of the extracted substring.

In the following example, the RIGHT function is applied to the literal string 'ABCDE' and returns the value 'DE':

RIGHT can only be used with string or memo columns or constants.

TRIM Function

The TRIM function removes the trailing or leading character, or both, from a string. The syntax is as follows:

The first parameter indicates the position of the character to be deleted, and has one of the following values:

The trimmed character parameter specifies the character to be deleted. Case-sensitivity is applied for this parameter. To make TRIM case-insensitive, use the UPPER or UCASE function on the column reference or string constant.

The FROM parameter specifies the column or constant from which to delete the character. The column reference for the FROM parameter can be a string column or a string constant.

The following are examples of using the TRIM function:

TRIM can only be used with string or memo columns or constants.

LTRIM Function

The LTRIM function removes any leading spaces from a string. The syntax is as follows:

The first and only parameter specifies the column or constant from which to delete the leading spaces, if any are present. The following is an example of using the LTRIM function:

LTRIM can only be used with string or memo columns or constants.

RTRIM Function

The RTRIM function removes any trailing spaces from a string. The syntax is as follows:

The first and only parameter specifies the column or constant from which to delete the trailing spaces, if any are present. The following is an example of using the RTRIM function:

RTRIM can only be used with string or memo columns or constants.

POS or POSITION Function

The POS or POSITION function returns the position of one string within another string. The syntax is as follows:

If the search string is not present, then 0 will be returned.

In the following example, the POS function is used to select all rows where the literal string 'ABC' exists in the Name column:

POS or POSITION can only be used with string or memo columns or constants.

OCCURS Function

The OCCURS function returns the number of occurrences of one string within another string. The syntax is as follows:

If the search string is not present, then 0 will be returned.

In the following example, the OCCURS function is used to select all rows where the literal string 'ABC' occurs at least once in the Name column:

OCCURS can only be used with string or memo columns or constants.

REPLACE Function

The REPLACE function replaces all occurrences of a given string with a new string within another string. The syntax is as follows:

If the search string is not present, then the result will be the original table column or string constant.

In the following example, the REPLACE function is used to replace all occurrences of 'Mexico' with 'South America':

REPLACE can only be used with string or memo columns or constants.

REPEAT Function

The REPEAT function repeats a given string a specified number of times and returns the concatenated result. The syntax is as follows:

In the following example, the REPEAT function is used to replicate the dash (-) character 60 times to use as a separator in a multi-line string:

REPEAT can only be used with string or memo columns or constants.

CONCAT Function

The CONCAT function concatenates two strings together and returns the concatenated result. The syntax is as follows:

In the following example, the CONCAT function is used to concatenate two strings together:

CONCAT can only be used with string or memo columns or constants.

Numeric Functions

Use numeric functions to manipulate numeric values in SELECT, INSERT, UPDATE, or DELETE queries. DBISAM's SQL supports the following numeric functions:

ABS Function

The ABS function converts a numeric value to its absolute, or non-negative value:

ABS can only be used with numeric columns or constants.

ACOS Function

The ACOS function returns the arccosine of a number as an angle expressed in radians:

ACOS can only be used with numeric columns or constants.

ASIN Function

The ASIN function returns the arcsine of a number as an angle expressed in radians:

ASIN can only be used with numeric columns or constants.

ATAN Function

The ATAN function returns the arctangent of a number as an angle expressed in radians:

ATAN can only be used with numeric columns or constants.

ATAN2 Function

The ATAN2 function returns the arctangent of x and y coordinates as an angle expressed in radians:

ATAN2 can only be used with numeric columns or constants.

CEIL or CEILING Function

The CEIL or CEILING function returns the lowest integer greater than or equal to a number:

CEIL or CEILING can only be used with numeric columns or constants.

COS Function

The COS function returns the cosine of an angle:

COS can only be used with numeric columns or constants.

COT Function

The COT function returns the cotangent of an angle:

COT can only be used with numeric columns or constants.

DEGREES Function

The DEGREES function converts a number representing radians into degrees:

DEGREES can only be used with numeric columns or constants.

EXP Function

The EXP function returns the exponential value of a number:

EXP can only be used with numeric columns or constants.

FLOOR Function

The FLOOR function returns the highest integer less than or equal to a number:

FLOOR can only be used with numeric columns or constants.

LOG Function

The LOG function returns the natural logarithm of a number:

LOG can only be used with numeric columns or constants.

LOG10 Function

The LOG10 function returns the base 10 logarithm of a number:

LOG10 can only be used with numeric columns or constants.

MOD Function

The MOD function returns the modulus of two integers. The modulus is the remainder that is present when dividing the first integer by the second integer:

MOD can only be used with integer columns or constants.

PI Function

The PI function returns the ratio of a circle's circumference to its diameter - approximated as 3.1415926535897932385:

POWER Function

The POWER function returns value of a base number raised to the specified power:

POWER can only be used with numeric columns or constants.

RADIANS Function

The RADIANS function converts a number representing degrees into radians:

RADIANS can only be used with numeric columns or constants.

RAND Function

The RAND function returns a random number:

The range value is optional used to limit the random numbers returned to between 0 and the range value specified. If the range is not specified then any number within the full range of numeric values may be returned.

ROUND Function

The ROUND function rounds a numeric value to a specified number of decimal places:

The number of decimal places is optional, and if not specified the value returned will be rounded to 0 decimal places.

ROUND can only be used with numeric columns or constants.

SIGN Function

The SIGN function returns -1 if a number is less than 0, 0 if a number is 0, or 1 if a number is greater than 0:

SIGN can only be used with numeric columns or constants.

SIN Function

The SIN function returns the sine of an angle:

SIN can only be used with numeric columns or constants.

SQRT Function

The SQRT function returns the square root of a number:

SQRT can only be used with numeric columns or constants.

TAN Function

The TAN function returns the tangent of an angle:

TAN can only be used with numeric columns or constants.

TRUNC or TRUNCATE Function

The TRUNC or TRUNCATE function truncates a numeric value to a specified number of decimal places:

The number of decimal places is optional, and if not specified the value returned will be truncated to 0 decimal places.

TRUNC or TRUNCATE can only be used with numeric columns or constants.

Boolean Functions

Use boolean functions to manipulate any values in SELECT, INSERT, UPDATE, or DELETE queries. DBISAM's SQL supports the following boolean functions:

IF Function

The IF function performs inline IF..ELSE boolean expression handling:

Both result expressions must be of the same resultant data type. Use the CAST function to ensure that both expressions are of the same data type.

In the following example, if the Category column contains the value 'WRASSE', then the column value returned will be the Common_Name column, otherwise it will be the Species Name column:

The IF function can be used in WHERE clause comparisons to cause a conditional comparison:

IFNULL Function

The IFNULL function performs inline IF..ELSE boolean expression handling specifically on NULL values:

Both result expressions must be of the same resultant data type. Use the CAST function to ensure that both expressions are of the same data type.

In the following example, if the Category column contains a NULL value, then the column value returned will be the Common_Name column, otherwise it will be the Species Name column:

The IFNULL function can be used in WHERE clause comparisons to cause a conditional comparison:

NULLIF Function

The NULLIF function returns a NULL if the two values passed as parameters are equal:

Both expressions must be of the same data type. Use the CAST function to ensure that both expressions are of the same data type.

In the following example, if the EmpNo column contains the value 14, then the value returned will be NULL, otherwise it will be the EmpNo column value:

The NULLIF function can be used in WHERE clause comparisons to cause a conditional comparison:

COALESCE Function

The COALESCE function returns the first non-NULL value from a list of expressions:

All expressions must be of the same resultant data type. Use the CAST function to ensure that all expressions are of the same data type.

In the following example, if the Category column contains a NULL value, then the column value returned will be the Common_Name column. If the Common_name column contains a NULL, then the literal string 'No Name' will be returned:

Aggregate Functions

Use aggregate functions to perform aggregate calculations on values in SELECT queries containing a GROUP BY clause. DBISAM's SQL supports the following aggregate functions:

AVG Function

The AVG function returns the average of the values in a specified column or expression. The syntax is as follows:

Use AVG to calculate the average value for a numeric column. As an aggregate function, AVG performs its calculation aggregating values in the same column(s) across all rows in a dataset. The dataset may be the entire table, a filtered dataset, or a logical group produced by a GROUP BY clause. Column values of zero are included in the averaging, so values of 1, 2, 3, 0, 0, and 0 result in an average of 1. NULL column values are not counted in the calculation. The following is an example of using the AVG function to calculate the average order amount for all orders:

AVG returns the average of values in a column or the average of a calculation using a column performed for each row (a calculated field). The following example shows how to use the AVG function to calculate an average order amount and tax amount for all orders:

When used with a GROUP BY clause, AVG calculates one value for each group. This value is the aggregation of the specified column for all rows in each group. The following example aggregates the average value for the ItemsTotal column in the Orders table, producing a subtotal for each company in the Customer table:

AVG operates only on numeric values.

COUNT Function

The COUNT function returns the number of rows that satisfy a query’s search condition or the number of rows where the specified column is not NULL. The syntax is as follows:

Use COUNT to count the number of rows retrieved by a SELECT statement. The SELECT statement may be a single-table or multi-table query. The value returned by COUNT reflects a reduced row count produced by a filtered dataset. The following example returns the total number of rows in the Averaging source table with a non-NULL Amount column:

The following example returns the total number of rows in the filtered Orders source table irrespective of any NULL column values:

MAX Function

The MAX function returns the largest value in the specified column. The syntax is as follows:

Use MAX to calculate the largest value for a string, numeric, date, time, or timestamp column. As an aggregate function, MAX performs its calculation aggregating values in the same column(s) across all rows in a dataset. The dataset may be the entire table, a filtered dataset, or a logical group produced by a GROUP BY clause. Column values of zero are included in the aggregation. NULL column values are not counted in the calculation. If the number of qualifying rows is zero, MAX returns a NULL value. The following is an example of using the MAX function to calculate the largest order amount for all orders:

MAX returns the largest value in a column or a calculation using a column performed for each row (a calculated field). The following example shows how to use the MAX function to calculate the largest order amount and tax amount for all orders:

When used with a GROUP BY clause, MAX returns one calculation value for each group. This value is the aggregation of the specified column for all rows in each group. The following example aggregates the largest value for the ItemsTotal column in the Orders table, producing a subtotal for each company in the Customer table:

MAX can be used with all string, numeric, date, time, and timestamp columns. The return value is of the same type as the column.

MIN Function

The MIN function returns the smallest value in the specified column. The syntax is as follows:

Use MIN to calculate the smallest value for a string, numeric, date, time, or timestamp column. As an aggregate function, MAX performs its calculation aggregating values in the same column(s) across all rows in a dataset. The dataset may be the entire table, a filtered dataset, or a logical group produced by a GROUP BY clause. Column values of zero are included in the aggregation. NULL column values are not counted in the calculation. If the number of qualifying rows is zero, MAX returns a NULL value. The following is an example of using the MAX function to calculate the smallest order amount for all orders:

MIN returns the smallest value in a column or a calculation using a column performed for each row (a calculated field). The following example shows how to use the MIN function to calculate the smallest order amount and tax amount for all orders:

When used with a GROUP BY clause, MIN returns one calculation value for each group. This value is the aggregation of the specified column for all rows in each group. The following example aggregates the smallest value for the ItemsTotal column in the Orders table, producing a subtotal for each company in the Customer table:

MIN can be used with all string, numeric, date, time, and timestamp columns. The return value is of the same type as the column.

STDDEV Function

The STDDEV function returns the standard deviation of the values in a specified column or expression. The syntax is as follows:

Use STDDEV to calculate the standard deviation value for a numeric column. As an aggregate function, STDDEV performs its calculation aggregating values in the same column(s) across all rows in a dataset. The dataset may be the entire table, a filtered dataset, or a logical group produced by a GROUP BY clause. NULL column values are not counted in the calculation. The following is an example of using the STDDEV function to calculate the standard deviation for a set of test scores:

When used with a GROUP BY clause, STDDEV calculates one value for each group. This value is the aggregation of the specified column for all rows in each group.

STDDEV operates only on numeric values.

SUM Function

The SUM function calculates the sum of values for a column. The syntax is as follows:

Use SUM to sum all the values in the specified column. As an aggregate function, SUM performs its calculation aggregating values in the same column(s) across all rows in a dataset. The dataset may be the entire table, a filtered dataset, or a logical group produced by a GROUP BY clause. Column values of zero are included in the aggregation. NULL column values are not counted in the calculation. If the number of qualifying rows is zero, SUM returns a NULL value. The following is an example of using the SUM function to calculate the total order amount for all orders:

SUM returns the total sum of a column or a calculation using a column performed for each row (a calculated field). The following example shows how to use the SUM function to calculate the total order amount and tax amount for all orders:

When used with a GROUP BY clause, SUM returns one calculation value for each group. This value is the aggregation of the specified column for all rows in each group. The following example aggregates the total value for the ItemsTotal column in the Orders table, producing a subtotal for each company in the Customer table:

SUM operates only on numeric values.

RUNSUM Function

The RUNSUM function calculates the sum of values for a column in a running total. The syntax is as follows:

Use RUNSUM to sum all the values in the specified column in a continuous running total. The RUNSUM function is identical to the SUM function except for the fact that it does not reset itself when sub-totalling.

LIST Function

The LIST function calculates the concatenation of string values for a column, using a delimiter to separate each value. The syntax is as follows:

Use LIST to concatenate all the string values in the specified column into a single string value, using a delimiter to separate one value from the next. If the delimiter is not specified, then the default delimiter is the comma (,).

AutoInc Functions

Use autoinc functions to return the last autoinc value from a given table in INSERT, UPDATE, or DELETE queries. DBISAM's SQL supports the following autoinc functions:

LASTAUTOINC Function

The LASTAUTOINC function returns the last autoinc value from a specified table. The syntax is as follows:

The LASTAUTOINC function will return the last autoinc value from the specified table relative to the start of the SQL statement currently referencing the LASTAUTOINC function. Because of this, it is possible for LASTAUTOINC to not return the most recent last autoinc value for the specified table. It is usually recommended that you only use this function within the scope of a transaction in order to guarantee that you have retrieved the correct last autoinc value from the table. The following example illustrates how this would be accomplished using an SQL script and a master-detail insert:

Full Text Indexing Functions

Use full text indexing functions to search for specific words in a given column in SELECT, INSERT, UPDATE, or DELETE queries. The word search is controlled by the text indexing parameters for the table in which the column resides. DBISAM's SQL supports the following word search functions:

TEXTSEARCH Function

The TEXTSEARCH function searches a column for a given set of words in a search string constant. The syntax is as follows:

The optimization of the TEXTSEARCH function is controlled by whether the column being searched is part of the full text index for the table in which the column resides. If the column is not part of the full text index then the search will resort to a brute-force scan of the contents of the column in every record that satisifies any prior conditions in the WHERE clause. Also, the parsing of the list of words in the search string constant is controlled by the text indexing parameters for the table in which the column being searched resides. Please see the Full Text Indexing topic for more information.

In the following example, the words 'DATABASE QUERY SPEED' are searched for in the TextBody column:

TEXTSEARCH returns a boolean value indicating whether the list of words exists in the column for a given record. TEXTSEARCH can only be used with string or memo columns.

TEXTOCCURS Function

The TEXTOCCURS function searches a column for a given set of words in a search string constant and returns the number of times the words occur in the column. The syntax is as follows:

TEXTOCCURS is always a brute-force operation and accesses the actual column contents to perform its functionality, unlike the TEXTSEARCH function which can be optimized by adding the column being searched to the full text index for the table. Also, the parsing of the list of words in the search string constant is controlled by the text indexing parameters for the table in which the column being searched resides. Please see the Full Text Indexing topic for more information.

In the following example, the number of occurrences of the words 'DATABASE QUERY SPEED' in the TextBody column are used to order the results of a TEXTSEARCH query in order to provide ranking for the text search:

TEXTOCCURS returns an integer value indicating the total number of times the list of words occurs in the column for a given record. TEXTOCCURS can only be used with string or memo columns.

Data Conversion Functions

Use data conversion functions to convert values from one type to another in SELECT, INSERT, UPDATE, or DELETE queries. DBISAM's SQL supports the following data conversion functions:

EXTRACT Function

The EXTRACT function returns a specific value from a date, time, or timestamp value. The syntax is as follows:

Use EXTRACT to return the year, month, week, day of week, day, hours, minutes, seconds, or milliseconds from a date, time, or timestamp column. EXTRACT returns the value for the specified element as an integer.

The extract_value parameter may contain any one of the specifiers:

The specifiers YEAR, MONTH, WEEK, DAYOFWEEK, DAYOFYEAR, and DAY can only be used with date and timestamp columns. The following example shows how to use the EXTRACT function to display the various elements of the SaleDate column:

The following example uses a DOB column (containing birthdates) to filter those rows where the date is in the month of May. The month field from the DOB column is retrieved using the EXTRACT function and compared to 5, May being the fifth month:

EXTRACT operates only on date, time, and timestamp values.

CAST Function

The CAST function converts a specified value to the specified data type. The syntax is as follows:

Use CAST to convert the value in the specified column to the data type specified. CAST can also be applied to literal and calculated values. CAST can be used in the columns list of a SELECT statement, in the predicate for a WHERE clause, or to modify the update atom of an UPDATE statement.

The data type parameter may be any valid SQL data type that is a valid as a destination type for the source data being converted. Please see the Data Types and NULL Support topic for more information.

The statement below converts a timestamp column value to a date column value:

Converting a column value with CAST allows use of other functions or predicates on an otherwise incompatible data type, such as using the SUBSTRING function on a date column:

YEARSFROMMSECS Function

The YEARSFROMMSECS function takes milliseconds and returns the number of years. The syntax is as follows:

Use YEARSFROMMSECS to return the number of years present in a milliseconds value as an integer value.

DAYSFROMMSECS Function

The DAYSFROMMSECS function takes milliseconds and returns the number of days as a remainder of the number of years present in the milliseconds. The syntax is as follows:

Use DAYSFROMMSECS to return the number of days present in a milliseconds value as an integer value. The number of days is represented as the remainder of days once the number of years is removed from the milliseconds value using the YEARSFROMMSECS function.

HOURSFROMMSECS Function

The HOURSFROMMSECS function takes milliseconds and returns the number of hours as a remainder of the number of years and days present in the milliseconds. The syntax is as follows:

Use HOURSFROMMSECS to return the number of hours present in a milliseconds value as an integer value. The number of hours is represented as the remainder of hours once the number of years and days is removed from the milliseconds value using the YEARSFROMMSECS and DAYSFROMMSECS functions.

MINSFROMMSECS Function

The MINSFROMMSECS function takes milliseconds and returns the number of minutes as a remainder of the number of years, days, and hours present in the milliseconds. The syntax is as follows:

Use MINSFROMMSECS to return the number of minutes present in a milliseconds value as an integer value. The number of minutes is represented as the remainder of minutes once the number of years, days, and hours is removed from the milliseconds value using the YEARSFROMMSECS, DAYSFROMMSECS, and HOURSFROMMSECS functions.

SECSFROMMSECS Function

The SECSFROMMSECS function takes milliseconds and returns the number of seconds as a remainder of the number of years, days, hours, and minutes present in the milliseconds. The syntax is as follows:

Use SECSFROMMSECS to return the number of seconds present in a milliseconds value as an integer value. The number of seconds is represented as the remainder of seconds once the number of years, days, hours, and minutes is removed from the milliseconds value using the YEARSFROMMSECS, DAYSFROMMSECS, HOURSFROMMSECS, and MINSFROMMSECS functions.

MSECSFROMMSECS Function

The MSECSFROMMSECS function takes milliseconds and returns the number of milliseconds as a remainder of the number of years, days, hours, minutes, and seconds present in the milliseconds. The syntax is as follows:

Use MSECSFROMMSECS to return the number of milliseconds present in a milliseconds value as an integer value. The number of milliseconds is represented as the remainder of milliseconds once the number of years, days, hours, minutes, and seconds is removed from the milliseconds value using the YEARSFROMMSECS, DAYSFROMMSECS, HOURSFROMMSECS, MINSFROMMSECS, and SECSFROMMSECS functions.

DBISAM's SQL provides string functions, numeric functions, boolean functions, aggregate functions (used in conjunction with an SQL SELECT GROUP BY clause), autoinc functions, full text indexing functions, and data conversion functions.

String Functions

Use string functions to manipulate string values in SELECT, INSERT, UPDATE, or DELETE queries. DBISAM's SQL supports the following string functions:

Function | Description |

LOWER or LCASE | Forces a string to lowercase. |

UPPER or UCASE | Forces a string to uppercase. |

LENGTH | Returns the length of a string value. |

SUBSTRING | Extracts a portion of a string value. |

LEFT | Extracts a certain number of characters from the left side of a string value. |

RIGHT | Extracts a certain number of characters from the right side of a string value. |

TRIM | Removes repetitions of a specified character from the left, right, or both sides of a string. |

LTRIM | Removes any leading space characters from a string. |

RTRIM | Removes any trailing space characters from a string. |

POS or POSITION | Finds the position of one string value within another string value. |

OCCURS | Finds the number of times one string value is present within another string value. |

REPLACE | Replaces all occurrences of one string value with a new string value within another string value. |

REPEAT | Repeats a string value a specified number of times. |

CONCAT | Concatenates two string values together. |

LOWER or LCASE Function

The LOWER or LCASE function converts all characters in a string value to lowercase. The syntax is as follows:

LOWER(column_reference or string constant) LCASE(column_reference or string constant)

In the following example, the values in the NAME column appear all in lowercase:

SELECT LOWER(Name) FROM Country

The LOWER or LCASE function can be used in WHERE clause string comparisons to cause a case-insensitive comparison. Apply LOWER or LCASE to the values on both sides of the comparison operator (if one of the comparison values is a literal, simply enter it all in lower case).

SELECT * FROM Names WHERE LOWER(Lastname) = 'smith'

LOWER or LCASE can only be used with string or memo columns or constants.

UPPER or UCASE Function

The UPPER or UCASE function converts all characters in a string value to uppercase. The syntax is as follows:

UPPER(column_reference or string constant) UCASE(column_reference or string constant)

Use UPPER or UCASE to convert all of the characters in a table column or character literal to uppercase. In the following example, the values in the NAME column are treated as all in uppercase. Because the same conversion is applied to both the filter column and comparison value in the WHERE clause, the filtering is effectively case-insensitive:

SELECT Name, Capital, Continent FROM Country WHERE UPPER(Name) LIKE UPPER('PE%')

UPPER can only be used with string or memo columns or constants.

LENGTH Function

The LENGTH function returns the length of a string value as an integer value. The syntax is as follows:

LENGTH(column_reference or string constant)

In the following example, the length of the values in the Notes column are returned as part of the SELECT statement:

SELECT Notes, LENGTH(Notes) AS "Num Chars" FROM Biolife

LENGTH can only be used with string or memo columns or constants.

SUBSTRING Function

The SUBSTRING function extracts a substring from a string. The syntax is as follows:

SUBSTRING(column_reference or string constant FROM start_index [FOR length]) SUBSTRING(column_reference or string constant, start_index[,length])

The second FROM parameter is the character position at which the extracted substring starts within the original string. The index for the FROM parameter is based on the first character in the source value being 1.

The FOR parameter is optional, and specifies the length of the extracted substring. If the FOR parameter is omitted, the substring goes from the position specified by the FROM parameter to the end of the string.

In the following example, the SUBSTRING function is applied to the literal string 'ABCDE' and returns the value 'BCD':

SELECT SUBSTRING('ABCDE' FROM 2 FOR 3) AS Sub FROM Country

In the following example, only the second and subsequent characters of the NAME column are retrieved:

SELECT SUBSTRING(Name FROM 2) FROM Country

SUBSTRING can only be used with string or memo columns or constants.

LEFT Function

The LEFT function extracts a certain number of characters from the left side of a string. The syntax is as follows:

LEFT(column_reference or string constant FOR length) LEFT(column_reference or string constant,length)

The FOR parameter specifies the length of the extracted substring.

In the following example, the LEFT function is applied to the literal string 'ABCDE' and returns the value 'ABC':

SELECT LEFT('ABCDE' FOR 3) AS Sub FROM Country

LEFT can only be used with string or memo columns or constants.

RIGHT Function

The RIGHT function extracts a certain number of characters from the right side of a string. The syntax is as follows:

RIGHT(column_reference or string constant FOR length) RIGHT(column_reference or string constant,length)

The FOR parameter specifies the length of the extracted substring.

In the following example, the RIGHT function is applied to the literal string 'ABCDE' and returns the value 'DE':

SELECT RIGHT('ABCDE' FOR 2) AS Sub FROM Country

RIGHT can only be used with string or memo columns or constants.

TRIM Function

The TRIM function removes the trailing or leading character, or both, from a string. The syntax is as follows:

TRIM([LEADING|TRAILING|BOTH] trimmed_char FROM column_reference or string constant) TRIM([LEADING|TRAILING|BOTH] trimmed_char, column_reference or string constant)

The first parameter indicates the position of the character to be deleted, and has one of the following values:

Keyword | Description |

LEADING | Deletes the character at the left end of the string. |

TRAILING | Deletes the character at the right end of the string. |

BOTH | Deletes the character at both ends of the string. |

The trimmed character parameter specifies the character to be deleted. Case-sensitivity is applied for this parameter. To make TRIM case-insensitive, use the UPPER or UCASE function on the column reference or string constant.

The FROM parameter specifies the column or constant from which to delete the character. The column reference for the FROM parameter can be a string column or a string constant.

The following are examples of using the TRIM function:

TRIM(LEADING '_' FROM '_ABC_') will return 'ABC_' TRIM(TRAILING '_' FROM '_ABC_') will return '_ABC' TRIM(BOTH '_' FROM '_ABC_') will return 'ABC' TRIM(BOTH 'A' FROM 'ABC') will return 'BC'

TRIM can only be used with string or memo columns or constants.

LTRIM Function

The LTRIM function removes any leading spaces from a string. The syntax is as follows:

LTRIM(column_reference or string constant)

The first and only parameter specifies the column or constant from which to delete the leading spaces, if any are present. The following is an example of using the LTRIM function:

LTRIM(' ABC') will return 'ABC'

LTRIM can only be used with string or memo columns or constants.

RTRIM Function

The RTRIM function removes any trailing spaces from a string. The syntax is as follows:

RTRIM(column_reference or string constant)

The first and only parameter specifies the column or constant from which to delete the trailing spaces, if any are present. The following is an example of using the RTRIM function:

RTRIM('ABC ') will return 'ABC'

RTRIM can only be used with string or memo columns or constants.

POS or POSITION Function

The POS or POSITION function returns the position of one string within another string. The syntax is as follows:

POS(string constant IN column_reference or string constant) POSITION(string constant IN column_reference or string constant) POS(string constant,column_reference or string constant) POSITION(string constant,column_reference or string constant)

If the search string is not present, then 0 will be returned.

In the following example, the POS function is used to select all rows where the literal string 'ABC' exists in the Name column:

SELECT * FROM Country WHERE POS('ABC' IN Name) > 0

POS or POSITION can only be used with string or memo columns or constants.

OCCURS Function

The OCCURS function returns the number of occurrences of one string within another string. The syntax is as follows:

OCCURS(string constant IN column_reference or string constant) OCCURS(string constant, column_reference or string constant)

If the search string is not present, then 0 will be returned.

In the following example, the OCCURS function is used to select all rows where the literal string 'ABC' occurs at least once in the Name column:

SELECT * FROM Country WHERE OCCURS('ABC' IN Name) > 0

OCCURS can only be used with string or memo columns or constants.

REPLACE Function

The REPLACE function replaces all occurrences of a given string with a new string within another string. The syntax is as follows:

REPLACE(string constant WITH new string constant IN column_reference or string constant) REPLACE(string constant,new string constant, column_reference or string constant)

If the search string is not present, then the result will be the original table column or string constant.

In the following example, the REPLACE function is used to replace all occurrences of 'Mexico' with 'South America':

UPDATE biolife SET notes=REPLACE('Mexico' WITH 'South America' IN notes)

REPLACE can only be used with string or memo columns or constants.

REPEAT Function

The REPEAT function repeats a given string a specified number of times and returns the concatenated result. The syntax is as follows:

REPEAT(column_reference or string constant FOR number_of_occurrences) REPEAT(column_reference or string constant, number_of_occurrences)

In the following example, the REPEAT function is used to replicate the dash (-) character 60 times to use as a separator in a multi-line string:

UPDATE biolife SET notes='Notes'+#13+#10+ REPEAT('-' FOR 60)+#13+#10+#13+#10+ 'These are the notes'

REPEAT can only be used with string or memo columns or constants.

CONCAT Function

The CONCAT function concatenates two strings together and returns the concatenated result. The syntax is as follows:

CONCAT(column_reference or string constant WITH column_reference or string constant) CONCAT(column_reference or string constant, column_reference or string constant)

In the following example, the CONCAT function is used to concatenate two strings together:

UPDATE biolife SET notes=CONCAT(Notes WITH #13+#10+#13+#10+'End of Notes')

CONCAT can only be used with string or memo columns or constants.

Numeric Functions

Use numeric functions to manipulate numeric values in SELECT, INSERT, UPDATE, or DELETE queries. DBISAM's SQL supports the following numeric functions:

Function | Description |

ABS | Converts a number to its absolute value (non-negative). |

ACOS | Returns the arccosine of a number as an angle expressed in radians. |

ASIN | Returns the arcsine of a number as an angle expressed in radians. |

ATAN | Returns the arctangent of a number as an angle expressed in radians. |

ATAN2 | Returns the arctangent of x and y coordinates as an angle expressed in radians. |

CEIL or CEILING | Returns the lowest integer greater than or equal to a number. |

COS | Returns the cosine of an angle. |

COT | Returns the cotangent of an angle. |

DEGREES | Converts a number representing radians into degrees. |

EXP | Returns the exponential value of a number. |

FLOOR | Returns the highest integer less than or equal to a number. |

LOG | Returns the natural logarithm of a number. |

LOG10 | Returns the base 10 logarithm of a number. |

MOD | Returns the modulus of two integers as an integer. |

PI | Returns the ratio of a circle's circumference to its diameter - approximated as 3.1415926535897932385. |

POWER | Returns the value of a base number raised to the specified power. |

RADIANS | Converts a number representing degrees into radians. |

RAND | Returns a random number. |

ROUND | Rounds a number to a specified number of decimal places. |

SIGN | Returns -1 if a number is less than 0, 0 if a number is 0, or 1 if a number is greater than 0. |

SIN | Returns the sine of an angle. |

SQRT | Returns the square root of a number. |

TAN | Returns the tangent of an angle. |

TRUNC or TRUNCATE | Truncates a numeric argument to the specified number of decimal places |

ABS Function

The ABS function converts a numeric value to its absolute, or non-negative value:

ABS(column_reference or numeric constant)

ABS can only be used with numeric columns or constants.

ACOS Function

The ACOS function returns the arccosine of a number as an angle expressed in radians:

ACOS(column_reference or numeric constant)

ACOS can only be used with numeric columns or constants.

ASIN Function

The ASIN function returns the arcsine of a number as an angle expressed in radians:

ASIN(column_reference or numeric constant)

ASIN can only be used with numeric columns or constants.

ATAN Function

The ATAN function returns the arctangent of a number as an angle expressed in radians:

ATAN(column_reference or numeric constant)

ATAN can only be used with numeric columns or constants.

ATAN2 Function

The ATAN2 function returns the arctangent of x and y coordinates as an angle expressed in radians:

ATAN2(column_reference or numeric constant, column_reference or numeric constant)

ATAN2 can only be used with numeric columns or constants.

CEIL or CEILING Function

The CEIL or CEILING function returns the lowest integer greater than or equal to a number:

CEIL(column_reference or numeric constant) CEILING(column_reference or numeric constant)

CEIL or CEILING can only be used with numeric columns or constants.

COS Function

The COS function returns the cosine of an angle:

COS(column_reference or numeric constant)

COS can only be used with numeric columns or constants.

COT Function

The COT function returns the cotangent of an angle:

COT(column_reference or numeric constant)

COT can only be used with numeric columns or constants.

DEGREES Function

The DEGREES function converts a number representing radians into degrees:

DEGREES(column_reference or numeric constant)

DEGREES can only be used with numeric columns or constants.

EXP Function

The EXP function returns the exponential value of a number:

EXP(column_reference or numeric constant)

EXP can only be used with numeric columns or constants.

FLOOR Function

The FLOOR function returns the highest integer less than or equal to a number:

FLOOR(column_reference or numeric constant)

FLOOR can only be used with numeric columns or constants.

LOG Function

The LOG function returns the natural logarithm of a number:

LOG(column_reference or numeric constant)

LOG can only be used with numeric columns or constants.

LOG10 Function

The LOG10 function returns the base 10 logarithm of a number:

LOG10(column_reference or numeric constant)

LOG10 can only be used with numeric columns or constants.

MOD Function

The MOD function returns the modulus of two integers. The modulus is the remainder that is present when dividing the first integer by the second integer:

MOD(column_reference or integer constant, column_reference or integer constant)

MOD can only be used with integer columns or constants.

PI Function

The PI function returns the ratio of a circle's circumference to its diameter - approximated as 3.1415926535897932385:

PI()

POWER Function

The POWER function returns value of a base number raised to the specified power:

POWER(column_reference or numeric constant TO column_reference or numeric constant) POWER(column_reference or numeric constant, column_reference or numeric constant)

POWER can only be used with numeric columns or constants.

RADIANS Function

The RADIANS function converts a number representing degrees into radians:

RADIANS(column_reference or numeric constant)

RADIANS can only be used with numeric columns or constants.

RAND Function

The RAND function returns a random number:

RAND([RANGE range of random values])

The range value is optional used to limit the random numbers returned to between 0 and the range value specified. If the range is not specified then any number within the full range of numeric values may be returned.

ROUND Function

The ROUND function rounds a numeric value to a specified number of decimal places:

ROUND(column_reference or numeric constant [TO number of decimal places]) ROUND(column_reference or numeric constant [, number of decimal places])

The number of decimal places is optional, and if not specified the value returned will be rounded to 0 decimal places.

ROUND can only be used with numeric columns or constants.

The ROUND function performs "normal" rounding where the number is rounded up if the fractional portion beyond the number of decimal places being rounded to is greater than or equal to 5 and down if the fractional portion is less than 5. Also, if using the ROUND function with floating-point values, it is possible to encounter rounding errors due to the nature of floating-point values and their inability to accurately express certain numbers. If you want to eliminate this possibility you should use the CAST function to convert the floating-point column or constant to a BCD value (DECIMAL or NUMERIC data type in SQL). This will allow for the rounding to occur as desired since BCD values can accurately represent these numbers without errors.

SIGN Function

The SIGN function returns -1 if a number is less than 0, 0 if a number is 0, or 1 if a number is greater than 0:

SIGN(column_reference or numeric constant)

SIGN can only be used with numeric columns or constants.

SIN Function

The SIN function returns the sine of an angle:

SIN(column_reference or numeric constant)

SIN can only be used with numeric columns or constants.

SQRT Function

The SQRT function returns the square root of a number:

SQRT(column_reference or numeric constant)

SQRT can only be used with numeric columns or constants.

TAN Function

The TAN function returns the tangent of an angle:

TAN(column_reference or numeric constant)

TAN can only be used with numeric columns or constants.

TRUNC or TRUNCATE Function

The TRUNC or TRUNCATE function truncates a numeric value to a specified number of decimal places:

TRUNC(column_reference or numeric constant [TO number of decimal places]) TRUNCATE(column_reference or numeric constant [TO number of decimal places]) TRUNC(column_reference or numeric constant [, number of decimal places]) TRUNCATE(column_reference or numeric constant [, number of decimal places])

The number of decimal places is optional, and if not specified the value returned will be truncated to 0 decimal places.

TRUNC or TRUNCATE can only be used with numeric columns or constants.

If using the TRUNC or TRUNCATE function with floating-point values, it is possible to encounter truncation errors due to the nature of floating-point values and their inability to accurately express certain numbers. If you want to eliminate this possibility you should use the CAST function to convert the floating-point column or constant to a BCD value (DECIMAL or NUMERIC data type in SQL). This will allow for the truncation to occur as desired since BCD values can accurately represent these numbers without errors.

Boolean Functions

Use boolean functions to manipulate any values in SELECT, INSERT, UPDATE, or DELETE queries. DBISAM's SQL supports the following boolean functions:

Function | Description |

IF | Performs IF..ELSE type of inline expression handling. |

IFNULL | Performs IF..ELSE type of inline expression handling specifically for NULL values. |

NULLIF | Returns a NULL if two values are equivalent. |

COALESCE | Returns the first non-NULL value from a list of expressions. |

IF Function

The IF function performs inline IF..ELSE boolean expression handling:

IF(boolean expression THEN result expression ELSE result expression) IF(boolean expression, result expression, result expression)

Both result expressions must be of the same resultant data type. Use the CAST function to ensure that both expressions are of the same data type.

In the following example, if the Category column contains the value 'WRASSE', then the column value returned will be the Common_Name column, otherwise it will be the Species Name column:

SELECT IF(Upper(Category)='WRASSE' THEN Common_Name ELSE "Species Name") AS Name FROM Biolife

The IF function can be used in WHERE clause comparisons to cause a conditional comparison:

SELECT * FROM Employee WHERE IF(LastName='Young' THEN PhoneExt='233' ELSE PhoneExt='22')

IFNULL Function

The IFNULL function performs inline IF..ELSE boolean expression handling specifically on NULL values:

IFNULL(expression THEN result expression ELSE result expression) IFNULL(expression, result expression, result expression)

Both result expressions must be of the same resultant data type. Use the CAST function to ensure that both expressions are of the same data type.

In the following example, if the Category column contains a NULL value, then the column value returned will be the Common_Name column, otherwise it will be the Species Name column:

SELECT IFNULL(Category THEN Common_Name ELSE "Species Name") AS Name FROM Biolife

The IFNULL function can be used in WHERE clause comparisons to cause a conditional comparison:

SELECT * FROM Employee WHERE IFNULL(Salary THEN 10000 ELSE Salary) > 8000

NULLIF Function

The NULLIF function returns a NULL if the two values passed as parameters are equal:

NULLIF(expression,expression)

Both expressions must be of the same data type. Use the CAST function to ensure that both expressions are of the same data type.

In the following example, if the EmpNo column contains the value 14, then the value returned will be NULL, otherwise it will be the EmpNo column value:

SELECT NULLIF(EmpNo,14) AS EmpNo FROM Orders

The NULLIF function can be used in WHERE clause comparisons to cause a conditional comparison:

SELECT * FROM Employee WHERE NULLIF(Salary,10000) > 8000

COALESCE Function

The COALESCE function returns the first non-NULL value from a list of expressions:

COALESCE(expression [, expression [, expression]])

All expressions must be of the same resultant data type. Use the CAST function to ensure that all expressions are of the same data type.

In the following example, if the Category column contains a NULL value, then the column value returned will be the Common_Name column. If the Common_name column contains a NULL, then the literal string 'No Name' will be returned:

SELECT COALESCE(Category,Common_Name,'No Name') AS Name FROM Biolife

Aggregate Functions

Use aggregate functions to perform aggregate calculations on values in SELECT queries containing a GROUP BY clause. DBISAM's SQL supports the following aggregate functions:

Function | Description |

AVG | Averages all numeric values in a column. |

COUNT | Counts the total number of rows or the number of rows where the specified column is not NULL. |

MAX | Determines the maximum value in a column. |

MIN | Determines the minimum value in a column. |

STDDEV | Calculates the standard deviation of all numeric values in a column. |

SUM | Totals all numeric values in a column. |

RUNSUM | Totals all numeric values in a column in a running total. |

LIST | Concatenates all string values in a column using a delimeter |

AVG Function

The AVG function returns the average of the values in a specified column or expression. The syntax is as follows:

AVG(column_reference or expression)

Use AVG to calculate the average value for a numeric column. As an aggregate function, AVG performs its calculation aggregating values in the same column(s) across all rows in a dataset. The dataset may be the entire table, a filtered dataset, or a logical group produced by a GROUP BY clause. Column values of zero are included in the averaging, so values of 1, 2, 3, 0, 0, and 0 result in an average of 1. NULL column values are not counted in the calculation. The following is an example of using the AVG function to calculate the average order amount for all orders:

SELECT AVG(ItemsTotal) FROM Orders

AVG returns the average of values in a column or the average of a calculation using a column performed for each row (a calculated field). The following example shows how to use the AVG function to calculate an average order amount and tax amount for all orders:

SELECT AVG(ItemsTotal) AS AverageTotal, AVG(ItemsTotal * 0.0825) AS AverageTax FROM Orders

When used with a GROUP BY clause, AVG calculates one value for each group. This value is the aggregation of the specified column for all rows in each group. The following example aggregates the average value for the ItemsTotal column in the Orders table, producing a subtotal for each company in the Customer table:

SELECT c."Company", AVG(o."ItemsTotal") AS Average, MAX(o."ItemsTotal") AS Biggest, MIN(o."ItemsTotal") AS Smallest FROM "Customer.dat" c, "Orders.dat" o WHERE (c."CustNo" = o."CustNo") GROUP BY c."Company" ORDER BY c."Company"

AVG operates only on numeric values.

COUNT Function

The COUNT function returns the number of rows that satisfy a query’s search condition or the number of rows where the specified column is not NULL. The syntax is as follows:

COUNT(* | column_reference or expression)

Use COUNT to count the number of rows retrieved by a SELECT statement. The SELECT statement may be a single-table or multi-table query. The value returned by COUNT reflects a reduced row count produced by a filtered dataset. The following example returns the total number of rows in the Averaging source table with a non-NULL Amount column:

SELECT COUNT(Amount) FROM Averaging

The following example returns the total number of rows in the filtered Orders source table irrespective of any NULL column values:

SELECT COUNT(*) FROM Orders WHERE (Orders.ItemsTotal > 5000)

MAX Function

The MAX function returns the largest value in the specified column. The syntax is as follows:

MAX(column_reference or expression)

Use MAX to calculate the largest value for a string, numeric, date, time, or timestamp column. As an aggregate function, MAX performs its calculation aggregating values in the same column(s) across all rows in a dataset. The dataset may be the entire table, a filtered dataset, or a logical group produced by a GROUP BY clause. Column values of zero are included in the aggregation. NULL column values are not counted in the calculation. If the number of qualifying rows is zero, MAX returns a NULL value. The following is an example of using the MAX function to calculate the largest order amount for all orders:

SELECT MAX(ItemsTotal) FROM Orders

MAX returns the largest value in a column or a calculation using a column performed for each row (a calculated field). The following example shows how to use the MAX function to calculate the largest order amount and tax amount for all orders:

SELECT MAX(ItemsTotal) AS HighestTotal, MAX(ItemsTotal * 0.0825) AS HighestTax FROM Orders

When used with a GROUP BY clause, MAX returns one calculation value for each group. This value is the aggregation of the specified column for all rows in each group. The following example aggregates the largest value for the ItemsTotal column in the Orders table, producing a subtotal for each company in the Customer table:

SELECT c."Company", AVG(o."ItemsTotal") AS Average, MAX(o."ItemsTotal") AS Biggest, MIN(o."ItemsTotal") AS Smallest FROM "Customer.dat" c, "Orders.dat" o WHERE (c."CustNo" = o."CustNo") GROUP BY c."Company" ORDER BY c."Company"

MAX can be used with all string, numeric, date, time, and timestamp columns. The return value is of the same type as the column.

MIN Function

The MIN function returns the smallest value in the specified column. The syntax is as follows:

MIN(column_reference or expression)

Use MIN to calculate the smallest value for a string, numeric, date, time, or timestamp column. As an aggregate function, MAX performs its calculation aggregating values in the same column(s) across all rows in a dataset. The dataset may be the entire table, a filtered dataset, or a logical group produced by a GROUP BY clause. Column values of zero are included in the aggregation. NULL column values are not counted in the calculation. If the number of qualifying rows is zero, MAX returns a NULL value. The following is an example of using the MAX function to calculate the smallest order amount for all orders:

SELECT MIN(ItemsTotal) FROM Orders

MIN returns the smallest value in a column or a calculation using a column performed for each row (a calculated field). The following example shows how to use the MIN function to calculate the smallest order amount and tax amount for all orders:

SELECT MIN(ItemsTotal) AS LowestTotal, MIN(ItemsTotal * 0.0825) AS LowestTax FROM Orders

When used with a GROUP BY clause, MIN returns one calculation value for each group. This value is the aggregation of the specified column for all rows in each group. The following example aggregates the smallest value for the ItemsTotal column in the Orders table, producing a subtotal for each company in the Customer table:

SELECT c."Company", AVG(o."ItemsTotal") AS Average, MAX(o."ItemsTotal") AS Biggest, MIN(o."ItemsTotal") AS Smallest FROM "Customer.dat" c, "Orders.dat" o WHERE (c."CustNo" = o."CustNo") GROUP BY c."Company" ORDER BY c."Company"

MIN can be used with all string, numeric, date, time, and timestamp columns. The return value is of the same type as the column.

STDDEV Function

The STDDEV function returns the standard deviation of the values in a specified column or expression. The syntax is as follows:

STDDEV(column_reference or expression)

Use STDDEV to calculate the standard deviation value for a numeric column. As an aggregate function, STDDEV performs its calculation aggregating values in the same column(s) across all rows in a dataset. The dataset may be the entire table, a filtered dataset, or a logical group produced by a GROUP BY clause. NULL column values are not counted in the calculation. The following is an example of using the STDDEV function to calculate the standard deviation for a set of test scores:

SELECT STDDEV(TestScore) FROM Scores

When used with a GROUP BY clause, STDDEV calculates one value for each group. This value is the aggregation of the specified column for all rows in each group.

STDDEV operates only on numeric values.

SUM Function

The SUM function calculates the sum of values for a column. The syntax is as follows:

SUM(column_reference or expression)

Use SUM to sum all the values in the specified column. As an aggregate function, SUM performs its calculation aggregating values in the same column(s) across all rows in a dataset. The dataset may be the entire table, a filtered dataset, or a logical group produced by a GROUP BY clause. Column values of zero are included in the aggregation. NULL column values are not counted in the calculation. If the number of qualifying rows is zero, SUM returns a NULL value. The following is an example of using the SUM function to calculate the total order amount for all orders:

SELECT SUM(ItemsTotal) FROM Orders

SUM returns the total sum of a column or a calculation using a column performed for each row (a calculated field). The following example shows how to use the SUM function to calculate the total order amount and tax amount for all orders:

SELECT SUM(ItemsTotal) AS Total, SUM(ItemsTotal * 0.0825) AS TotalTax FROM orders

When used with a GROUP BY clause, SUM returns one calculation value for each group. This value is the aggregation of the specified column for all rows in each group. The following example aggregates the total value for the ItemsTotal column in the Orders table, producing a subtotal for each company in the Customer table:

SELECT c."Company", SUM(o."ItemsTotal") AS SubTotal FROM "Customer.dat" c, "Orders.dat" o WHERE (c."CustNo" = o."CustNo") GROUP BY c."Company" ORDER BY c."Company"

SUM operates only on numeric values.

RUNSUM Function

The RUNSUM function calculates the sum of values for a column in a running total. The syntax is as follows:

RUNSUM(column_reference or expression)

Use RUNSUM to sum all the values in the specified column in a continuous running total. The RUNSUM function is identical to the SUM function except for the fact that it does not reset itself when sub-totalling.

The running total is only calculated according to the implicit order of the GROUP BY fields and is not affected by an ORDER BY statement.

LIST Function

The LIST function calculates the concatenation of string values for a column, using a delimiter to separate each value. The syntax is as follows:

LIST(column_reference or expression[,delimiter])

Use LIST to concatenate all the string values in the specified column into a single string value, using a delimiter to separate one value from the next. If the delimiter is not specified, then the default delimiter is the comma (,).

AutoInc Functions

Use autoinc functions to return the last autoinc value from a given table in INSERT, UPDATE, or DELETE queries. DBISAM's SQL supports the following autoinc functions:

Function | Description |

LASTAUTOINC | Returns the last autoinc value from a specified table. |

IDENT_CURRENT | Same as LASTAUTOINC, with a different name. |

LASTAUTOINC Function

The LASTAUTOINC function returns the last autoinc value from a specified table. The syntax is as follows:

LASTAUTOINC(table name constant)

The LASTAUTOINC function will return the last autoinc value from the specified table relative to the start of the SQL statement currently referencing the LASTAUTOINC function. Because of this, it is possible for LASTAUTOINC to not return the most recent last autoinc value for the specified table. It is usually recommended that you only use this function within the scope of a transaction in order to guarantee that you have retrieved the correct last autoinc value from the table. The following example illustrates how this would be accomplished using an SQL script and a master-detail insert:

START TRANSACTION; INSERT INTO customer (company) VALUES ('Test'); INSERT INTO orders (custno,empno) VALUES (LASTAUTOINC('customer'),100); INSERT INTO orders (custno,empno) VALUES (LASTAUTOINC('customer'),200); COMMIT FLUSH;

Full Text Indexing Functions

Use full text indexing functions to search for specific words in a given column in SELECT, INSERT, UPDATE, or DELETE queries. The word search is controlled by the text indexing parameters for the table in which the column resides. DBISAM's SQL supports the following word search functions:

Function | Description |

TEXTSEARCH | Performs an optimized text word search on a field, if the field is part of the full text index for the table, or a brute-force text word search if not. |

TEXTOCCURS | Counts the number of times a list of words appears in a field based upon the full text indexing parameters for the table. |

TEXTSEARCH Function

The TEXTSEARCH function searches a column for a given set of words in a search string constant. The syntax is as follows:

TEXTSEARCH(search string constant IN column_reference) TEXTSEARCH(search string constant, column_reference)

The optimization of the TEXTSEARCH function is controlled by whether the column being searched is part of the full text index for the table in which the column resides. If the column is not part of the full text index then the search will resort to a brute-force scan of the contents of the column in every record that satisifies any prior conditions in the WHERE clause. Also, the parsing of the list of words in the search string constant is controlled by the text indexing parameters for the table in which the column being searched resides. Please see the Full Text Indexing topic for more information.

In the following example, the words 'DATABASE QUERY SPEED' are searched for in the TextBody column:

SELECT GroupNo, No FROM article WHERE TEXTSEARCH('DATABASE QUERY SPEED' IN TextBody)

TEXTSEARCH returns a boolean value indicating whether the list of words exists in the column for a given record. TEXTSEARCH can only be used with string or memo columns.

TEXTOCCURS Function

The TEXTOCCURS function searches a column for a given set of words in a search string constant and returns the number of times the words occur in the column. The syntax is as follows:

TEXTOCCURS(search string constant IN column_reference) TEXTOCCURS(search string constant, column_reference)

TEXTOCCURS is always a brute-force operation and accesses the actual column contents to perform its functionality, unlike the TEXTSEARCH function which can be optimized by adding the column being searched to the full text index for the table. Also, the parsing of the list of words in the search string constant is controlled by the text indexing parameters for the table in which the column being searched resides. Please see the Full Text Indexing topic for more information.

In the following example, the number of occurrences of the words 'DATABASE QUERY SPEED' in the TextBody column are used to order the results of a TEXTSEARCH query in order to provide ranking for the text search:

SELECT GroupNo, No, TEXTOCCURS('DATABASE QUERY SPEED' IN TextBody) AS NumOccurs FROM article WHERE TEXTSEARCH('DATABASE QUERY SPEED' IN TextBody) ORDER BY 3 DESC

TEXTOCCURS returns an integer value indicating the total number of times the list of words occurs in the column for a given record. TEXTOCCURS can only be used with string or memo columns.

Data Conversion Functions

Use data conversion functions to convert values from one type to another in SELECT, INSERT, UPDATE, or DELETE queries. DBISAM's SQL supports the following data conversion functions:

Function | Description |

EXTRACT | Extracts the year, month, week, day of week, or day value of a date or the hours, minutes, or seconds value of a time. |

CAST | Converts a given data value from one data type to another. |

YEARSFROMMSECS | Takes milliseconds and returns the number of years. |

DAYSFROMMSECS | Takes milliseconds and returns the number of days (as a remainder of the above years, not as an absolute). |

HOURSFROMMSECS | Takes milliseconds and returns the number of hours (as a remainder of the above years and days, not as an absolute). |

MINSFROMMSECS | Takes milliseconds and returns the number of minutes (as a remainder of the above years, days, and hours, not as an absolute). |

SECSFROMMSECS | Takes milliseconds and returns the number of seconds (as a remainder of the above years, days, hours, and minutes, not as an absolute). |

MSECSFROMMSECS | Takes milliseconds and returns the number of milliseconds (as a remainder of the above years, days, hours, minutes, and seconds, not as an absolute). |

EXTRACT Function

The EXTRACT function returns a specific value from a date, time, or timestamp value. The syntax is as follows:

EXTRACT(extract_value FROM column_reference or expression) EXTRACT(extract_value, column_reference or expression)

Use EXTRACT to return the year, month, week, day of week, day, hours, minutes, seconds, or milliseconds from a date, time, or timestamp column. EXTRACT returns the value for the specified element as an integer.

The extract_value parameter may contain any one of the specifiers:

YEAR MONTH WEEK DAYOFWEEK DAYOFYEAR DAY HOUR MINUTE SECOND MSECOND

The specifiers YEAR, MONTH, WEEK, DAYOFWEEK, DAYOFYEAR, and DAY can only be used with date and timestamp columns. The following example shows how to use the EXTRACT function to display the various elements of the SaleDate column:

SELECT SaleDate, EXTRACT(YEAR FROM SaleDate) AS YearNo, EXTRACT(MONTH FROM SaleDate) AS MonthNo, EXTRACT(WEEK FROM SaleDate) AS WeekNo, EXTRACT(DAYOFWEEK FROM SaleDate) AS WeekDayNo, EXTRACT(DAYOFYEAR FROM SaleDate) AS YearDayNo, EXTRACT(DAY FROM SaleDate) AS DayNo FROM Orders

The following example uses a DOB column (containing birthdates) to filter those rows where the date is in the month of May. The month field from the DOB column is retrieved using the EXTRACT function and compared to 5, May being the fifth month:

SELECT DOB, LastName, FirstName FROM People WHERE (EXTRACT(MONTH FROM DOB) = 5)

The WEEK and DAYOFWEEK parameters will return the week number and the day of the week according to ANSI/ISO standards. This means that the first week of the year (week 1) is the first week that contains the first Thursday in January and January 4th and the first day of the week (day 1) is Monday. Also, while ANSI-standard SQL provides the EXTRACT function specifiers TIMEZONE_HOUR and TIMEZONE_MINUTE, these specifiers are not supported in DBISAM's SQL.

EXTRACT operates only on date, time, and timestamp values.

CAST Function

The CAST function converts a specified value to the specified data type. The syntax is as follows:

CAST(column_reference AS data_type) CAST(column_reference,data_type)

Use CAST to convert the value in the specified column to the data type specified. CAST can also be applied to literal and calculated values. CAST can be used in the columns list of a SELECT statement, in the predicate for a WHERE clause, or to modify the update atom of an UPDATE statement.

The data type parameter may be any valid SQL data type that is a valid as a destination type for the source data being converted. Please see the Data Types and NULL Support topic for more information.

The statement below converts a timestamp column value to a date column value:

SELECT CAST(SaleDate AS DATE) FROM ORDERS

Converting a column value with CAST allows use of other functions or predicates on an otherwise incompatible data type, such as using the SUBSTRING function on a date column:

SELECT SaleDate, SUBSTRING(CAST(CAST(SaleDate AS DATE) AS CHAR(10)) FROM 1 FOR 1) FROM Orders

All conversions of dates or timestamps to strings are done using the 24-hour clock (military time).

YEARSFROMMSECS Function

The YEARSFROMMSECS function takes milliseconds and returns the number of years. The syntax is as follows:

YEARSFROMMSECS(column_reference or expression)

Use YEARSFROMMSECS to return the number of years present in a milliseconds value as an integer value.

DAYSFROMMSECS Function

The DAYSFROMMSECS function takes milliseconds and returns the number of days as a remainder of the number of years present in the milliseconds. The syntax is as follows:

DAYSFROMMSECS(column_reference or expression)

Use DAYSFROMMSECS to return the number of days present in a milliseconds value as an integer value. The number of days is represented as the remainder of days once the number of years is removed from the milliseconds value using the YEARSFROMMSECS function.

HOURSFROMMSECS Function

The HOURSFROMMSECS function takes milliseconds and returns the number of hours as a remainder of the number of years and days present in the milliseconds. The syntax is as follows:

HOURSFROMMSECS(column_reference or expression)

Use HOURSFROMMSECS to return the number of hours present in a milliseconds value as an integer value. The number of hours is represented as the remainder of hours once the number of years and days is removed from the milliseconds value using the YEARSFROMMSECS and DAYSFROMMSECS functions.

MINSFROMMSECS Function

The MINSFROMMSECS function takes milliseconds and returns the number of minutes as a remainder of the number of years, days, and hours present in the milliseconds. The syntax is as follows:

MINSFROMMSECS(column_reference or expression)

Use MINSFROMMSECS to return the number of minutes present in a milliseconds value as an integer value. The number of minutes is represented as the remainder of minutes once the number of years, days, and hours is removed from the milliseconds value using the YEARSFROMMSECS, DAYSFROMMSECS, and HOURSFROMMSECS functions.

SECSFROMMSECS Function

The SECSFROMMSECS function takes milliseconds and returns the number of seconds as a remainder of the number of years, days, hours, and minutes present in the milliseconds. The syntax is as follows:

SECSFROMMSECS(column_reference or expression)

Use SECSFROMMSECS to return the number of seconds present in a milliseconds value as an integer value. The number of seconds is represented as the remainder of seconds once the number of years, days, hours, and minutes is removed from the milliseconds value using the YEARSFROMMSECS, DAYSFROMMSECS, HOURSFROMMSECS, and MINSFROMMSECS functions.

MSECSFROMMSECS Function

The MSECSFROMMSECS function takes milliseconds and returns the number of milliseconds as a remainder of the number of years, days, hours, minutes, and seconds present in the milliseconds. The syntax is as follows:

MSECSFROMMSECS(column_reference or expression)

Use MSECSFROMMSECS to return the number of milliseconds present in a milliseconds value as an integer value. The number of milliseconds is represented as the remainder of milliseconds once the number of years, days, hours, minutes, and seconds is removed from the milliseconds value using the YEARSFROMMSECS, DAYSFROMMSECS, HOURSFROMMSECS, MINSFROMMSECS, and SECSFROMMSECS functions.

More Support Options

This web page was last updated on Thursday, November 16, 2023 at 10:39 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |