SQLMemTable: Embedded BDE Alternative
In-Memory SQL Database for Delphi / C++Builder



Functions
Previous  Top  Next


Introduction

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



© AidAim Software SQLMemTable: In-memory Sql Database Delph