SQL-92 Functions and Operators Used In Relational DBMSs

You probably know that (A+B) is an expression, in which + is an operator. If you've programmed in C, then you recognize that func(A+B) is also an expression, with a scalar function func and an arithmetic operator +. Here's a quick look at THE OCELOT SQL DBMS's SQL-92 capabilities in the functions-and-operators line.

Arithmetic operators: With any numeric datatype you can use + for addition, - for substraction, / for division, and * for multiplication. With datetime datatypes you may use subtraction and addition in certain cases. Examples: 17+COLUMN_1, DATE '1994-01-01' + INTERVAL '3' DAY.

Length operators: BIT_LENGTH gives the length in bits, CHAR_LENGTH gives the length in characters, OCTET_LENGTH gives the length in octets. Example: suppose we have a single character in SQL_TEXT, in a column named COLUMN_1. Then BIT_LENGTH(COLUMN_1) returns 16, CHAR_LENGTH(COLUMN_1) returns 1, and OCTET_LENGTH(COLUMN_1) returns 2.

Character fiddlers. Use || (two vertical bars) as a binary operator for concatenating, COLLATE changes the assumed collation, POSITION gives the offset of a string within another string, TRIM removes extraneous characters such as spaces, SUBSTRING takes a portion of a string from a certain offset for a certain size, LOWER gives a string in lower case, UPPER gives a string in upper case. The UPPER function is important because THE OCELOT SQL DBMS's default collations are always case sensitive. That is: 'A' is not equal to 'a' unless you use a case insensitive collation. However, 'A' is equal to UPPER('a') so for a case-insensitive search you need merely enclose your search string within UPPER.

CASE. Equivalent to case in Pascal or C. Example: CASE WHEN COLUMN_1 > 0 THEN 'Yeah' WHEN COLUMN_1 = 0 THEN 'Hmmm' ELSE 'Nope' END.

CAST. Equivalent to cast in C. You'll use this for translating from one datatype to another. For example, if we have a BIT field containing X'4243' (i.e. the hexadecimal 16-bit value 4243h), then CAST(X'4243' AS CHAR(2)) yields 'BC'.

System semi-constants. CURRENT_DATE, CURRENT_TIME and CURRENT_TIMESTAMP give the current date, time, and timestamp respectively. To find out who you are use CURRENT_USER or SESSION_USER or SYSTEM_USER, all of which are SQL_TEXT strings.

Aggregates. SUM gives totals, MIN and MAX give minima and maxima, COUNT gives how-manies, and AVG gives totals divided by how-manies.

Return to Ocelot home page