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:

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:


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:


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.


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.


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:

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:


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.


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:


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:


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:


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)

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

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.