Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Product Manuals » DBISAM Version 4 Manual for Delphi 5 » SQL Reference » 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(column_reference or string constant) LCASE(column_reference or string constant)
SELECT LOWER(Name) FROM Country
SELECT * FROM Names WHERE LOWER(Lastname) = 'smith'
UPPER(column_reference or string constant) UCASE(column_reference or string constant)
SELECT Name, Capital, Continent FROM Country WHERE UPPER(Name) LIKE UPPER('PE%')
LENGTH(column_reference or string constant)
SELECT Notes, LENGTH(Notes) AS "Num Chars" FROM Biolife
SUBSTRING(column_reference or string constant FROM start_index [FOR length]) SUBSTRING(column_reference or string constant, start_index[,length])
SELECT SUBSTRING('ABCDE' FROM 2 FOR 3) AS Sub FROM Country
SELECT SUBSTRING(Name FROM 2) FROM Country
LEFT(column_reference or string constant FOR length) LEFT(column_reference or string constant,length)
SELECT LEFT('ABCDE' FOR 3) AS Sub FROM Country
RIGHT(column_reference or string constant FOR length) RIGHT(column_reference or string constant,length)
SELECT RIGHT('ABCDE' FOR 2) AS Sub FROM Country
TRIM([LEADING|TRAILING|BOTH] trimmed_char FROM column_reference or string constant) TRIM([LEADING|TRAILING|BOTH] trimmed_char, column_reference or string constant)
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. |
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'
LTRIM(column_reference or string constant)
LTRIM(' ABC') will return 'ABC'
RTRIM(column_reference or string constant)
RTRIM('ABC ') will return 'ABC'
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)
SELECT * FROM Country WHERE POS('ABC' IN Name) > 0
OCCURS(string constant IN column_reference or string constant) OCCURS(string constant, column_reference or string constant)
SELECT * FROM Country WHERE OCCURS('ABC' IN Name) > 0
REPLACE(string constant WITH new string constant IN column_reference or string constant) REPLACE(string constant,new string constant, column_reference or string constant)
UPDATE biolife SET notes=REPLACE('Mexico' WITH 'South America' IN notes)
REPEAT(column_reference or string constant FOR number_of_occurrences) REPEAT(column_reference or string constant, number_of_occurrences)
UPDATE biolife SET notes='Notes'+#13+#10+ REPEAT('-' FOR 60)+#13+#10+#13+#10+ 'These are the notes'
CONCAT(column_reference or string constant WITH column_reference or string constant) CONCAT(column_reference or string constant, column_reference or string constant)
UPDATE biolife SET notes=CONCAT(Notes WITH #13+#10+#13+#10+'End of Notes')
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(column_reference or numeric constant)
ACOS(column_reference or numeric constant)
ASIN(column_reference or numeric constant)
ATAN(column_reference or numeric constant)
ATAN2(column_reference or numeric constant, column_reference or numeric constant)
CEIL(column_reference or numeric constant) CEILING(column_reference or numeric constant)
COS(column_reference or numeric constant)
COT(column_reference or numeric constant)
DEGREES(column_reference or numeric constant)
EXP(column_reference or numeric constant)
FLOOR(column_reference or numeric constant)
LOG(column_reference or numeric constant)
LOG10(column_reference or numeric constant)
MOD(column_reference or integer constant, column_reference or integer constant)
PI()
POWER(column_reference or numeric constant TO column_reference or numeric constant) POWER(column_reference or numeric constant, column_reference or numeric constant)
RADIANS(column_reference or numeric constant)
RAND([RANGE range of random values])
ROUND(column_reference or numeric constant [TO number of decimal places]) ROUND(column_reference or numeric constant [, number of decimal places])
SIGN(column_reference or numeric constant)
SIN(column_reference or numeric constant)
SQRT(column_reference or numeric constant)
TAN(column_reference or numeric constant)
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])
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(boolean expression THEN result expression ELSE result expression) IF(boolean expression, result expression, result expression)
SELECT IF(Upper(Category)='WRASSE' THEN Common_Name ELSE "Species Name") AS Name FROM Biolife
SELECT * FROM Employee WHERE IF(LastName='Young' THEN PhoneExt='233' ELSE PhoneExt='22')
IFNULL(expression THEN result expression ELSE result expression) IFNULL(expression, result expression, result expression)
SELECT IFNULL(Category THEN Common_Name ELSE "Species Name") AS Name FROM Biolife
SELECT * FROM Employee WHERE IFNULL(Salary THEN 10000 ELSE Salary) > 8000
NULLIF(expression,expression)
SELECT NULLIF(EmpNo,14) AS EmpNo FROM Orders
SELECT * FROM Employee WHERE NULLIF(Salary,10000) > 8000
COALESCE(expression [, expression [, expression]])
SELECT COALESCE(Category,Common_Name,'No Name') AS Name FROM Biolife
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(column_reference or expression)
SELECT AVG(ItemsTotal) FROM Orders
SELECT AVG(ItemsTotal) AS AverageTotal, AVG(ItemsTotal * 0.0825) AS AverageTax FROM Orders
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"
COUNT(* | column_reference or expression)
SELECT COUNT(Amount) FROM Averaging
SELECT COUNT(*) FROM Orders WHERE (Orders.ItemsTotal > 5000)
MAX(column_reference or expression)
SELECT MAX(ItemsTotal) FROM Orders
SELECT MAX(ItemsTotal) AS HighestTotal, MAX(ItemsTotal * 0.0825) AS HighestTax FROM Orders
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(column_reference or expression)
SELECT MIN(ItemsTotal) FROM Orders
SELECT MIN(ItemsTotal) AS LowestTotal, MIN(ItemsTotal * 0.0825) AS LowestTax FROM Orders
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"
STDDEV(column_reference or expression)
SELECT STDDEV(TestScore) FROM Scores
SUM(column_reference or expression)
SELECT SUM(ItemsTotal) FROM Orders
SELECT SUM(ItemsTotal) AS Total, SUM(ItemsTotal * 0.0825) AS TotalTax FROM orders
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"
RUNSUM(column_reference or expression)
LIST(column_reference or expression[,delimiter])
Function | Description |
LASTAUTOINC | Returns the last autoinc value from a specified table. |
IDENT_CURRENT | Same as LASTAUTOINC, with a different name. |
LASTAUTOINC(table name constant)
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;
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(search string constant IN column_reference) TEXTSEARCH(search string constant, column_reference)
SELECT GroupNo, No FROM article WHERE TEXTSEARCH('DATABASE QUERY SPEED' IN TextBody)
TEXTOCCURS(search string constant IN column_reference) TEXTOCCURS(search string constant, column_reference)
SELECT GroupNo, No, TEXTOCCURS('DATABASE QUERY SPEED' IN TextBody) AS NumOccurs FROM article WHERE TEXTSEARCH('DATABASE QUERY SPEED' IN TextBody) ORDER BY 3 DESC
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(extract_value FROM column_reference or expression) EXTRACT(extract_value, column_reference or expression)
YEAR MONTH WEEK DAYOFWEEK DAYOFYEAR DAY HOUR MINUTE SECOND MSECOND
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
SELECT DOB, LastName, FirstName FROM People WHERE (EXTRACT(MONTH FROM DOB) = 5)
CAST(column_reference AS data_type) CAST(column_reference,data_type)
SELECT CAST(SaleDate AS DATE) FROM ORDERS
SELECT SaleDate, SUBSTRING(CAST(CAST(SaleDate AS DATE) AS CHAR(10)) FROM 1 FOR 1) FROM Orders
YEARSFROMMSECS(column_reference or expression)
DAYSFROMMSECS(column_reference or expression)
HOURSFROMMSECS(column_reference or expression)
MINSFROMMSECS(column_reference or expression)
SECSFROMMSECS(column_reference or expression)
MSECSFROMMSECS(column_reference or expression)
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 |