Accuracer: The First And Only BDE Alternative
Client-Server Single-File Embedded Database for Delphi / C++Builder / Kylix
Introduction
Accuracer supports string, date, aggregate and data conversion functions.
String Functions
UPPER
|
Returns a character expression with lowercase character data converted to uppercase.
|
LOWER
|
Returns a character expression after converting uppercase character data to lowercase.
|
TRIM
|
Returns a character string after truncating all leading and trailing blanks.
|
LTRIM
|
Returns a character expression after removing leading blanks.
|
RTRIM
|
Returns a character string after truncating all trailing blanks.
|
SUBSTRING
|
Returns a substring of a string.
|
POS
|
Returns the index value of the first character in a specified substring that occurs in a given string. Pos is case-sensitive.
|
LENGTH
|
Returns number of characters in a string excluding the null terminator.
|
UPPER Function
Returns a character expression with lowercase character data converted to uppercase.
Syntax:
UPPER ( expression )
Arguments
expression
Is an expression of string or wide string types.
Example:
SELECT UPPER(Company)
FROM Customer
LOWER Function
Returns a character expression after converting uppercase character data to lowercase.
Syntax:
LOWER ( expression )
Arguments
expression
Is an expression of string or wide string types.
Example:
SELECT LOWER(Company)
FROM Customer
TRIM Function
Returns a character string after truncating all leading and trailing blanks.
Syntax:
TRIM ( expression )
Arguments
expression
Is an expression of string or wide string types.
Example:
SELECT TRIM(Company)
FROM Customer
LTRIM Function
Returns a character expression after removing leading blanks.
Syntax:
LTRIM ( expression )
Arguments
expression
Is an expression of string or wide string types.
Example:
SELECT LTRIM(Company)
FROM Customer
RTRIM Function
Returns a character string after truncating all trailing blanks.
Syntax:
RTRIM ( expression )
Arguments
expression
Is an expression of string or wide string types.
Example:
SELECT RTRIM(Company)
FROM Customer
SUBSTRING Function
Returns a substring of a string.
Syntax:
SUBSTRING (string, startindex [, length] )
Arguments
string
Is a an expression of string or wide string type.
startindex
Is a constant that specifies the character position at which the extracted substring starts within the original string.
length
Is a constant that specifies number of characters being extracted from source string.
Example:
SELECT SUBSTRING(Company,2,5)
FROM Customer
POS Function
Returns the index value of the first character in a specified substring that occurs in a given string. Pos is case-sensitive.
Syntax:
POS ( substring, string )
Arguments
substring
Is a an expression of string or wide string type that specifies substring for searching in the specified string.
string
Is a an expression of string or wide string type that specifies source string.
Example:
SELECT *
FROM Customer
WHERE Pos('AidAim',Company) > 0
LENGTH Function
Returns number of characters in a string excluding the null terminator.
Syntax:
LENGTH ( string )
Arguments
string
Is a an expression of string or wide string type.
Example:
SELECT *
FROM Customer
WHERE LENGTH(Company) > 5
Date Functions
SYSDATE
|
Returns current system date and time.
|
NOW
|
Returns current system date and time.
|
CURRENT_TIMESTAMP
|
Returns current system date and time.
|
CURRENT_DATE
|
Returns current system date.
|
CURRENT_TIME
|
Returns current system time.
|
TOSTRING
|
Converts date to string using specified format.
|
TODATE
|
Converts string to date using specified format.
|
SYSDATE, NOW AND CURRENT_TIMESTAMP Functions
Returns current system date and time.
Syntax:
SYSDATE
NOW
CURRENT_TIMESTAMP
Example:
SELECT LastInvoiceDate, NOW as CurDate
FROM Customer
WHERE LastInvoiceDate < NOW
CURRENT_DATE Function
Returns current system date.
Syntax:
CURRENT_DATE
Example:
SELECT LastInvoiceDate, CURRENT_DATE as CurDate
FROM Customer
WHERE LastInvoiceDate < NOW
CURRENT_TIME Function
Returns current system time.
Syntax:
CURRENT_TIME
Example:
SELECT LastInvoiceDate, CURRENT_TIME as CurTime
FROM Customer
WHERE LastInvoiceDate < NOW
TOSTRING Function
Converts date to string using specified format.
Syntax:
TOSTRING( DateValue, DateFormat )
Arguments
DateValue
Is an expression of date type that specifies the source date.
DateFormat
Is an expression of string or wide string type that specifies date format for conversion DateValue to string.
DateFormat strings are composed from specifiers that represent values to be inserted into the formatted string. Some specifiers (such as "d"), simply format numbers or strings. Other specifiers (such as "/") refer to locale-specific strings.
In the following table, specifiers are given in upper case. Case is ignored in formats.
Specifier
|
Displays
|
-
|
Displays date separator '-'.
|
/
|
Displays date separator '/'.
|
.
|
Displays date separator '.'.
|
,
|
Displays date separator ','.
|
:
|
Displays date separator ':'.
|
;
|
Displays date separator ';'.
|
'TEXT'
|
Displays the text that will be included in the result of TOSTRING function without any conversion. The leading and trailing quotes will be omitted.
|
YYYY
or
YEAR
|
Displays the year as a four-digit number (0000-9999)
|
YY
|
Displays the year as a two-digit number (00-99)
|
Q
|
Displays the quarter of the year (1-4). 1 means months January, February and March, 2 means months April, May and June, 3 means months July, August and September, 4 means months October, November and December.
|
MONTH
|
Displays the month as a full name (January-December).
|
MON
|
Displays the month as an abbreviation (Jan-Dec).
|
MM
|
Displays the month as a number with a leading zero (01-12).
|
M
|
Displays the month as a number without a leading zero (1-12).
|
RM
|
Displays the month in roman numeric format (I - XII).
|
DDD
|
Displays the day of the year (1-366) without a leading zero.
|
DD
|
Displays the day of the month (01-31) with a leading zero.
|
D
|
Displays the day of the month (1-31) without a leading zero.
|
DAY
|
Displays the day as an abbreviation (Sun-Sat).
|
DY
|
Displays the day as an 2 symbol abbreviation (Su-Sa).
|
DW
|
Displays the day of week (1-7)
|
HH
HH12
|
Displays the hour with a leading zero (01-12).
|
HH24
|
Displays the hour with a leading zero (01-24).
|
H
H12
|
Displays the hour without a leading zero (1-12).
|
H24
|
Displays the hour without a leading zero (1-24).
|
NN
|
Displays the minute with a leading zero (00:59).
|
N
|
Displays the minute without a leading zero (0:59).
|
SS
|
Displays the second with a leading zero (00:59).
|
S
|
Displays the second without a leading zero (00:59).
|
AMPM
|
Displays the meridian indicator AM.
|
Example:
SELECT TOSTRING(LastInvoiceDate,"'Today is' mm/dd/yyyy hh24:nn:ss ' Wow !!!'") Formated_Date, LastInvoiceDate
FROM Customer
TODATE Function
Converts string to date using specified format.
Syntax:
TODATE( StringValue, DateFormat )
Arguments
StringValue
Is an expression of string or wide string type that specifies the source string.
DateFormat
Is an expression of string or wide string type that specifiec date format for the StringValue.
DateFormat strings are composed from specifiers that represent values to be inserted into the formatted string. Some specifiers (such as "d"), simply format numbers or strings. Other specifiers (such as "/") refer to locale-specific strings.
In the following table, specifiers are given in upper case. Case is ignored in formats.
Specifier
|
Specifies
|
-
|
Specifies date separator '-'.
|
/
|
Specifies date separator '/'.
|
.
|
Specifies date separator '.'
|
,
|
Specifies date separator ','.
|
:
|
Specifies date separator ':'.
|
;
|
Specifies date separator ';'.
|
'text'
|
Specifies the text that will be ignored by TODATE function. The leading and trailing quotes will be omitted.
|
YYYY
or
YEAR
|
Specifies the year as a four-digit number (0000-9999)
|
YY
|
Specifies the year as a two-digit number (00-99)
|
Q
|
Specifies the quarter of the year (1-4). 1 means months January, February and March, 2 means months April, May and June, 3 means months July, August and September, 4 means months October, November and December.
|
MM
|
Specifies the month as a number with a leading zero (01-12).
|
M
|
Specifies the month as a number without a leading zero (1-12).
|
DDD
|
Specifies the day of the year (1-366) without a leading zero.
|
DD
|
Specifies the day of the month (01-31) with a leading zero.
|
D
|
Specifies the day of the month (1-31) without a leading zero.
|
DW
|
Specifies the day of week (1-7)
|
HH
HH12
|
Specifies the hour with a leading zero (01-12).
|
HH24
|
Specifies the hour with a leading zero (01-24).
|
H
H12
|
Specifies the hour without a leading zero (1-12).
|
H24
|
Specifies the hour without a leading zero (1-24).
|
NN
|
Specifies the minute with a leading zero (00:59).
|
N
|
Specifies the minute without a leading zero (0:59).
|
SS
|
Specifies the second with a leading zero (00:59).
|
S
|
Specifies the second without a leading zero (00:59)
|
AMPM
|
Specifies the meridian indicator AM.
|
Examples:
SELECT LastInvoiceDate, NOW as CurDate
FROM Customer
WHERE LastInvoiceDate < TODATE('12/16/2002 11:10:30 am','MM/DD/YYYY hh:nn:ss ampm')
Aggregate Functions
Operate on a collection of values but return a single, summarizing value.
COUNT
|
Returns the number of items in a group.
|
SUM
|
Returns the sum of the values in the expression.
|
AVG
|
Returns the average of the values in a group.
|
MIN
|
Returns the minimum value in the expression.
|
MAX
|
Returns the maximum value in the expression.
|
COUNT Function
Returns the number of items in a group.
Syntax:
COUNT ( expression | * )
Arguments
expression
Is an expression of any type except Blob types. Aggregate functions and subqueries are not permitted.
*
Specifies that all rows should be counted to return the total number of rows in a table. COUNT(*) takes no parameters and cannot be used with DISTINCT. COUNT(*) does not require an expression parameter because, by definition, it does not use information about any particular column. COUNT(*) returns the number of rows in a specified table without eliminating duplicates. It counts each row separately, including rows that contain null values.
Examples:
SELECT COUNT(*)
FROM Orders
SELECT COUNT(OrderNo), ShipVIA
FROM Orders
GROUP BY ShipVIA
SUM Function
Returns the sum of all the values in the expression. SUM can be used with numeric columns only. Null values are ignored.
Syntax
SUM ( expression )
Arguments
expression
Is a constant, column, or function, and any combination of arithmetic operators. expression is an expression of the exact numeric or approximate numeric data type category. Aggregate functions and subqueries are not permitted.
Example:
SELECT SUM(AmountPaid)
FROM Orders
WHERE PaymentMethod='Visa'
AVG Function
Returns the average of the values in a group. Null values are ignored.
Syntax
AVG ( expression )
Arguments
expression
Is an expression of the exact numeric or approximate numeric data type category. Aggregate functions and subqueries are not permitted.
Example:
SELECT AVG(AmountPaid)
FROM Orders
WHERE PaymentMethod='Cash'
MIN Function
Returns the minimum value in the expression.
Syntax
MIN ( expression )
Arguments
expression
Is a constant, column name, or function, and any combination of arithmetic operators. MIN can be used with numeric columns only. Aggregate functions and subqueries are not permitted.
Example:
SELECT MIN(OrderNo)
FROM Orders
MAX Function
Returns the maximum value in the expression.
Syntax
MAX ( expression )
Arguments
expression
Is a constant, column name, or function, and any combination of arithmetic operators. MAX can be used with numeric columns only. Aggregate functions and subqueries are not permitted.
Example:
SELECT MAX(SaleDate)
FROM Orders
CAST Function
The CAST function converts a specified value to the specified data type.
Syntax
CAST( value, data_type )
Arguments
value
Is an expression of any valid data type.
data_type
Is a constant that specifies data type for converting the value specified by Value.
CAST function can be used with the following data types:
Data type
|
Description
|
AutoInc
|
Auto incremental 32-bit unsigned integer.
|
Currency
|
Floating point number.
|
Date
|
Date value.
|
DateTime
|
DateTime value.
|
Float
|
Floating point number.
|
Integer
|
32-bit signed integer.
|
LargeInt
|
64-bit signed integer.
|
Logical
|
Boolean value.
|
SmallInt
|
16-bit signed integer
|
String
|
Fixed length string (may be up to 2^32 symbols)
|
Time
|
Time value.
|
WideString
|
Fixed length Unicode string (may be up to 2^32 symbols)
|
Word
|
16-bit unsigned integer.
|
Example:
SELECT Customer.*, CAST(CURRENT_DATE,STRING) as CurDate
FROM Customer
TOBLOB Function
The TOBLOB function converts a specified string value to the BLOB value.
Syntax
TOBLOB( value [, format] )
Arguments
value
Is a string value that can be converted to a BLOB value using specified format.
format
Two formats are supported:
MIME64 - MIME64 standard format (used in e-mail)
HEX - upper case hexadecimal numbers
Default format is MIME64 (typically provides smaller string length).
Example:
INSERT INTO jpeg VALUES (
'AidAim',
TOBLOB ('QWlkQWltIFNvZnR3YXJlDQpIZXJlIHRvIEhlbHANCg==',MIME64),
NULL, 1);
LASTAUTOINC Function
The LASTAUTOINC function returns the last autoinc value from a specified table.
Syntax
LASTAUTOINC( table_name, column_name )
Arguments
table_name
Is a string constant that specifies table name for getting the last autoinc value.
column_name
Is a string constant that specifies autoinc field name for getting the last autoinc value.
Example:
INSERT INTO Employee (Name,DeptID)
VALUES ('John Smith',LASTAUTOINC( Department, ID ))
|