| Prev | SQL Engine Reference | Next |
Scalar Functions
The SRDE supports ODBC scalar functions which may be included in an SQL statement as a primary expression.
This section lists the scalar functions supported by the SRDE.
String Functions
String functions are used to process and manipulate columns that consist of text information, such as CHAR or LONGVARCHAR data types.
Arguments denoted as string can be the name of column, a string literal, or the result of another scalar function..
Queries containing a WHERE clause with scalar functions RTRIM or LEFT can be optimized. For example, consider the following query:
SELECT * FROM T1, T2 WHERE T1.C1 = LEFT(T2.C1, 2)In this case, both sides of the predicate are optimized if T1.C1 and T2.C2 are index columns. The predicate is the complete search condition following the WHERE keyword. Depending on the size of the tables involved in the join, the optimizer chooses the appropriate table to process first.
RTRIM and LEFT cannot be optimized if they are contained in a complex expression on either side of the predicate.
Examples
The following example creates a new table with an integer and a character column. It inserts 4 rows with values for the character column only, then updates the integer column of those rows with the ASCII character code for each character.
CREATE TABLE numchars(num INTEGER,chr CHAR(1) CASE) INSERT INTO numchars (chr) VALUES('a') INSERT INTO numchars (chr) VALUES('b') INSERT INTO numchars (chr) VALUES('A') INSERT INTO numchars (chr) VALUES('B') UPDATE numchars SET num=ASCII(chr) SELECT * FROM numcharsnum chr ---------- --- 97 a 98 b 65 A 66 B SELECT num FROM numchars WHERE num=ASCII('a')num ------ 97The following example concatenates the first and last names in the Person table and results in "RooseveltBora".
SELECT CONCAT(First_name, Last_name) FROM Person WHERE First_name = 'Roosevelt'The next example changes the case of the first name to lowercase and then to upper case, results in "roosevelt", "ROOSEVELT".
SELECT LCASE(First_name),UCASE(First_name) FROM Person WHERE First_name = 'Roosevelt'The following example results in first name trimmed to three characters beginning from left, the length as 9 and locate results 0. This query results in "Roo", 9, 0
SELECT LEFT(First_name, 3),LENGTH(First_name), LOCATE(First_name, 'a') FROM Person WHERE First_name = 'Roosevelt'The following example illustrates use of LTRIM and RTRIM functions on strings, results in "Roosevelt", "Roosevelt", "elt".
SELECT LTRIM(First_name),RTRIM(First_name), RIGHT(First_name,3) FROM Person WHERE First_name = 'Roosevelt'This substring lists up to three characters starting with the second character in the first name as "oos."
SELECT SUBSTRING(First_name,2, 3) FROM Person WHERE First_name = 'Roosevelt' SELECT ID,first_name FROM Person WHERE LCASE(First_name) = 'bruce'Numeric Functions
Numeric functions are used to process and manipulate columns that consist of strictly numeric information, such as decimal and integer values.
Examples
The following example lists the Modulus of the number and capacity columns in a table named room.
SELECT MOD(Number, Capacity) FROM RoomThe following example selects all salaries from a table named Faculty that are evenly divisible by 100.
SELECT Salary FROM Faculty WHERE MOD(Salary, 100) = 0Time and Date Functions
Date and time functions can be used to generate, process, and manipulate data that consists of date or time data types, such as DATE and TIME.
Inserting Time and Date Function Values
If you wish to insert the value of a function into a table, one method supported by all the time/date functions is to use a SELECT subquery in your INSERT statement, as shown:
INSERT INTO t1 (c1, c2) SELECT CURRENT_DATE(), CURRENT_TIME()Some functions, such as CURDATE(), CURTIME(), and NOW(), also support direct INSERT, as shown:
INSERT INTO t1 (c1) VALUES (CURDATE())
Table 3-23 Time and Date FunctionsExamples
The following example shows how to insert values from time or date functions. Some functions, such as CURDATE(), you can use directly in an INSERT statement. Other functions, however, are not supported in this manner. The method with the widest support is to use INSERT with SELECT. In the example below, the UTC time value returned by CURRENT_TIME() is inserted into table T1:
INSERT INTO T1 (C1) SELECT CURRENT_TIME()The following example illustrates the use of hour.
SELECT c.Name,c.Credit_Hours FROM Course c WHERE c.Name = ANY (SELECT cl.Name FROM Class cl WHERE c.Name = cl.Name AND c.Credit_Hours >(HOUR (Finish_Time - Start_Time) + 1))The following is an example of minute.
SELECT minute(log) FROM billingThe following example illustrates the use of second.
SELECT SECOND(log) FROM billing SELECT log FROM billing where SECOND(log) = 31The following example illustrates the use of now.
SELECT now() - log FROM billingThe following is a complex example that uses month, day, year, hour and minute.
SELECT Name, Section, MONTH(Start_Date), DAY(Start_Date), YEAR(Start_Date), HOUR(Start_Time), MINUTE(Start_Time) FROM ClassThe following example illustrates use of curdate.
SELECT ID, Name, Section FROM Class WHERE (Start_Date - CURDATE()) <= 2 AND (Start_Date - CURDATE()) >= 0The next example gives the day of the month and day of the week of the start date of class from the class table.
SELECT DAYOFMONTH(Start_date), DAYOFWEEK(Start_date) from Class SELECT * FROM person WHERE YEAR(Date_Of_Birth) < 1970System Functions
System functions provide information at a system level.
Table 3-24 System FunctionsExamples
The following examples show how to obtain the name of the current user and database:
SELECT USER( ) SELECT DATABASE( )If you want to obtain this information for every record in a table, use the following (the example uses the Person table in DEMODATA):
SELECT USER( ) FROM person SELECT DATABASE( ) FROM person SELECT USER( ), DATABASE( ) FROM personLogical Functions
Logical functions are used to manipulate data based on certain conditions.
Examples
The system scalar functions IF and NULL are SQL extensions.
IF allows you to enter different values depending on whether the condition is true or false. For example, if you want to display a column with logical values as "true" or "false" instead of a binary representation, you would use the following SQL statement:
SELECT IF(logicalcol=1, 'True', 'False')The system scalar function NULL allows you to set a column as null values. The syntax is:
NULL()For example, the following SQL statement retrieves null values:
SELECT NULL() FROM personThe following statements demonstrate the IFNULL and NULLIF scalar functions. You use these functions when you want to do certain value substitution based on the presence or absence of NULLs and on equality.
CREATE TABLE Demo (col1 CHAR(3)) INSERT INTO Demo VALUES ('abc') INSERT INTO Demo VALUES (NULL) INSERT INTO Demo VALUES ('xyz')Since the second row contains the NULL value, 'foo' is substituted in its place.
SELECT IFNULL(col1, 'foo') FROM DemoThis results in three rows fetched from one column:
"abc" "foo" "xyz" 3 rows fetched from 1 column.The first row contains 'abc,' which matches the second argument of the following NULLIF call.
SELECT NULLIF(col1, 'abc') FROM DemoA NULL is returned in its place:
<Null> <Null> "xyz" 3 rows fetched from 1 column.Conversion Function
The conversion function converts an expression to a data type.
Examples
SELECT CONVERT(id , SQL_CHAR), CONVERT( '1995-06-05', SQL_DATE),CONVERT('10:10:10', SQL_TIME), CONVERT('1990-10-10 10:10:10', SQL_TIMESTAMP),CONVERT('1990-10-10', SQL_TIMESTAMP) FROM FacultySELECT Name FROM Class WHERE Start_date > CONVERT ('1995-05-07', SQL_DATE) + 31
| Prev Grammar Element Definitions |
Contents Up Check for Revisions | Next Global Variables |