Scalar Functions

Scalar functions fall into the categories listed in Table 4.2.

 
Categories of Scalar Functions
 

Function Category

Explanation

Built-in

Performs operations on values or settings built into the database.

Oracle uses the term "built-in" to describe all the specialty functions that are provided by Oracle, and thus "built into" their DBMS. This is a distinct and septe usage from the built-in functions described here.

Date & Time

Performs operations on datetime fields and returns values in datetime format.

Numeric

Performs operations on numeric values and returns numeric values.

String

Performs operations on character values (char, varchar, nchar, nvarchar, and CLOB) and returns a string or numeric value.

 

Note that CASE and CAST are both functions. However, they are detailed in Chapter 3 because of their complexity and frequent usage in SQL-data statements.

Built-in Scalar Functions

SQL99 built-in scalar functions identify the current user session, and also characteristics of the current user session, such as the current session privileges. Built-in scalar functions are almost always nondeterministic. The first three functions listed in Table 4.3 are built-in functions that fall into the date-and-time category of functions. Although the four vendors provide many additional functions beyond these SQL built-ins, the SQL standard declares only those listed in Table 4.3 .

 
SQL99 Built-in Scalar Functions
 

Function

Usage

CURRENT_DATE

Identifies the current date.

CURRENT_TIME

Identifies the current time.

CURRENT_TIMESTAMP

Identifies the current date and time.

CURRENT_USER

Identifies the currently active user within the database server.

SESSION_USER

Identifies the currently active Authorization ID, if it differs from the user.

SYSTEM_USER

Identifies the currently active user within the host operating systm.

 

Microsoft SQL Server supports all the built-in scalar functions. Oracle does not support the built-in scalar functions shown above; however, it supports USER as a synonym of CURRENT_USER and SYSDATE as a synonym of CURRENT_TIMESTAMP. MySQL supports all the SQL99 built-in scalar functions, plus both of Oracle's variants. PostgreSQL supports USER, as defined in SQL99, as a synonym for CURRENT_USER. In addition, MySQL supports NOW( ) and UNIX_TIMESTAMP (MySQL) as synonyms of the function CURRENT_TIMESTAMP. PostgreSQL supports all the SQL99 built-in scalar functions except SESSION_USER.

 
Examples

The following queries retrieve the values from built-in functions. Notice that the various vendors return dates in their native formats:

/* On MySQL */

SELECT CURRENT_TIMESTAMP;
-> '2001-12-15 23:50:26'

/* On Microsoft SQL Server */

SELECT CURRENT_TIMESTAMP
GO
-> 'Dec 15,2001 23:50:26'

/* On Oracle */

SELECT USER FROM dual;
-> dylan
Numeric Scalar Functions

The list of official SQL99 numeric functions is rather small. The various vendors provide quite a large supplement of mathematical and statistical functions. MySQL supports many of these commands in its SQL99 incarnations. The other database products offer the same capabilities of numeric scalar functions through their own internally defined functions, but they do not share the same name as those declared by the SQL standard. The supported numeric functions and syntax are listed in Table 4.4 .

 
SQL99 Numeric Functions
 

Function

Usage

BIT_LENGTH(expression)

Returns an integer value representing the number of bits in an expression.

CHAR_LENGTH(expression)

Returns an integer value representing the number of characters in an expression.

EXTRACT(datetime_expression datepart FROM expression)

Allows the datepart to be extracted (YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, or TIMEZONE_MINUTE ) from an expression.

OCTET_LENGTH(expression)

Returns an integer value representing the number of octets in an expression. This value is the same as BIT_LENGTH/8.

POSITION(starting_string IN search_string)

Returns an integer value representing the starting position of a string within the search string.

 
BIT_LENGTH, CHAR_LENGTH, and OCTET_LENGTH

The closest any of the vendors get to the BIT_LENGTH function is Oracle. Oracle supports the LENGTHB function, which returns an integer value representing the number of bytes in an expression.

MySQL and PostgreSQL support CHAR_LENGTH and the SQL99 synonym CHARACTER_LENGTH( ). PostgreSQL also supports EXTRACT( ), OCTET_LENGTH( ), and POSITION( ) as per the SQL99 standard. The other two vendors each have a similar function that provides identical functionality. SQL Server provides the LEN function and Oracle provides the LENGTH function.

MySQL and PostgreSQL also fully support the OCTET_LENGTH function.

 
Example

The following example determines the length of a string and a value retrieved from a column:

/* On MySQL and PostgreSQL */

SELECT CHAR_LENGTH('hello');
SELECT OCTET_LENGTH(book_title) FROM titles;

/* On Microsoft SQL Server */

SELECT DATALENGTH(title)
FROM titles
WHERE type = 'popular_comp'
GO

/* On Oracle */

SELECT LENGTH('HORATIO') "Length of characters"
FROM dual;
 
EXTRACT

The EXTRACT function is not supported by the database vendors, except for PostgreSQL and MySQL.

Each vendor supports a separate command to accomplish the same functionality. Oracle uses the TO_CHAR function to extract a portion of a date into a character string. SQL Server uses the CONVERT function to extract a portion of a date.

MySQL implementation is extended somewhat beyond the SQL99 standard. The SQL99 standard does not have a provision for returning multiple fields in the same call to EXTRACT( ) (e.g., "DAY_HOUR"). The MySQL extensions try to accomplish what the combination DATE_TRUNC( ) and DATE_PART( ) do in PostgreSQL. MySQL supports the dateparts listed in Table 4.5 .

 
MySQL Dateparts
 

Type value

Meaning

Expected format

SECOND

Seconds

SECONDS

MINUTE

Minutes

MINUTES

HOUR

Hours

HOURS

DAY

Days

DAYS

MONTH

Months

MONTHS

YEAR

Years

YEARS

MINUTE_SECOND

Minutes and seconds

"MINUTES:SECONDS"

HOUR_MINUTE

Hours and minutes

"HOURS:MINUTES"

DAY_HOUR

Days and hours

"DAYS HOURS"

YEAR_MONTH

Years and months

"YEARS-MONTHS"

HOUR_SECOND

Hours, minutes, seconds

"HOURS:MINUTES:SECONDS"

DAY_MINUTE

Days, hours, minutes

"DAYS HOURS:MINUTES"

DAY_SECOND

Days, hours, minutes, seconds

"DAYSHOURS:MINUTES:SECONDS"

 
Example

This example extracts dateparts from several datetime values:

/* On MySQL */

SELECT EXTRACT(YEAR FROM "2013-07-02");
-> 1999
SELECT EXTRACT(YEAR_MONTH FROM "2013-07-02 01:02:03");
-> 199907
SELECT EXTRACT(DAY_MINUTE FROM "2013-07-02 01:02:03");
-> 20102
 
POSITION

The POSITION function returns an integer that indicates the starting position of a string within the search string. MySQL and PostgreSQL support the POSITION function with no variation from the SQL99 syntax. PostgreSQL has a synonymous function, TEXTPOS, while MySQL has the synonymous function, LOCATE.

Oracle's equivalent function is called INSTR. Microsoft SQL Server has both CHARINDEX and PATINDEX. The CHARINDEX and PATINDEX are very similar, except that PATINDEX allows the use of wildcard characters in the search criteria. For example:

/* On MySQL */
SELECT LOCATE('bar', 'foobar');
-> 4

/* On MySQL and PostgreSQL */

SELECT POSITION('fu' IN 'snafhu'); -> 0

/* On Microsoft SQL Server */

SELECT CHARINDEX( 'de', 'abcdefg' ) GO -> 4 SELECT PATINDEX( '%fg', 'abcdefg' ) GO -> 6
String Functions

Basic string functions offer a number of capabilities and return a string value as a result set. Some string functions are dyadic, indicating that they operate on two strings at once. SQL99 supports the string functions listed in Table 4.6 .

 
SQL String Functions
 

Function

Usage

CONCATENATE(expression || expression)

Appends two or more literal expressions, column values, or variables together into one string.

CONVERT

Converts a string to a different representation within the same character set.

LOWER

Converts a string to all lowercase characters.

SUBSTRING

Extracts a portion of a string.

TRANSLATE

Converts a string from one character set to another.

TRIM

Removes leading characters, trailing characters, or both from a character string.

UPPER

Converts a string to all uppercase characters.

 
CONCATENATE

SQL99 defines a concatenation operator ( || ), which joins two distinct strings into one string value. The CONCATENATE function appends two or more strings together, producing a single output string. PostgreSQL and Oracle support the double-concatenation operator. Microsoft SQL Server uses the plus sign (+) concatenation operator.

MySQL supports a similar function, CONCAT( ). Refer to Concatenation Operators in Chapter 3 , for more information on concatenation within Oracle, PostgreSQL, and Microsoft SQL Server.

 
SQL99 Syntax

CONCATENATE('string1' || 'string2')

 
MySQL Syntax
CONCAT(str1, str2, [,...n])

If any of the concatenation values are null, the entire returned string is null. Also, if a numeric value is concatenated, it is implicitly converted to a character string:

SELECT CONCAT('My ', 'bologna ', 'has ', 'a ', 'first ', 'name...');
-> 'My bologna has a first name...'
SELECT CONCAT('My ', NULL, 'has ', 'first ', 'name...');
-> NULL
 
CONVERT and TRANSLATE

The CONVERT function alters the representation of a character string within its character set and collation. For example, CONVERT might be used to alter the number of bits per character.

TRANSLATE alters the character set of a string value from one base-character set to another. Thus, TRANSLATE might be used to translate a value from the English character set to a Kanji (Japanese) or Russian character set. The translation must already exist, either by default or having been created using the CREATE TRANSLATION command.

 
SQL99 Syntax

CONVERT (char_value target_char_set USING form_of_use source_char_name) TRANSLATE(char_value target_char_set USING translation_name)

Among the database vendors, only Oracle supports CONVERT and TRANSLATE with the same meaning as SQL99. Oracle's implementation of TRANSLATE is very similar to SQL99, but not identical. In its implementation, Oracle accepts only two arguments and allows translating only between the database character set or the national language support character set.

MySQL's implementation of the CONVERT function only translates numbers from one base to another. In contrast, Microsoft SQL Server's implementation of CONVERT is a very rich utility that alters the base datatype of an expression, but is otherwise dissimilar to the SQL99 CONVERT function. PostgreSQL does not support CONVERT, and its implementation of TRANSLATE serves to morph any occurrence of a character string to any other character string.

 
MySQL Syntax and Variations
CONV(int, from_base, to_base)

MySQL does not support TRANSLATE. This implementation of CONVERT returns a string value representing the number as it is converted from the from_base value to the to_base value. If any of the numbers are NULL, then the function returns NULL. Following are some examples:

SELECT CONV("a",16,2);
-> '1010'
SELECT CONV("6E",18,8);
-> '172'
SELECT CONV(-17,10,-18);
-> '-H'
 
Microsoft SQL Server Syntax and Variations
CONVERT (data_type[(length) | (precision,scale)], expression[,style])

Microsoft SQL Server does not support TRANSLATE. Microsoft's implementation of the CONVERT function does not follow the SQL99 specification. Instead, it is functionally equivalent to the CAST function. The style clause is used to define the format of a date conversion. Refer to the vendor documentation for more information. Following is an example:

SELECT title, CONVERT(char(7), ytd_sales) FROM titles ORDER BY title GO
 
Oracle Syntax and Variations
CONVERT('char_value', target_char_set, source_char_set) TRANSLATE('char_value', 'from_text', 'to_text')

Under Oracle's implementation, the CONVERT function returns the char_value in the target character set. The char_value is the string being converted, while the target_char_set is the name of the character set where the char_value is converted. Source_char_set is the name of the character set where the char_value was originally stored.

Both the target and source character set can be either literals strings, variables, or columns containing the name of the character set. Note that inadequate replacement characters might be substituted when converting from or to a character set that does not support a representation of all the characters used in the conversion.

Oracle supports several common character sets including US7ASCII, WE8DECDEC, WE8HP, F7DEC, WE8EBCDIC500, WE8PC850, and WE8ISO8859P1. For example:

SELECT CONVERT('Groß', 'US7ASCII', 'WE8HP')
FROM DUAL;
->Gross
 
PostgreSQL Syntax and Variations
TRANSLATE (character_string, from_text, to_text)

PostgreSQL does not support CONVERT. PostgreSQL's implementation of the TRANSLATE function offers a large superset of functions compared to that found in the SQL99 specification. Instead, it converts any occurrence of one text string to another within another specified string. Here is an example:

SELECT TRANSLATE('12345abcde', '5a', 'XX');
-> 1234XXbcde

SELECT TRANSLATE(title, 'Computer', 'PC')
FROM  titles
WHERE type = 'Personal_computer'
 
LOWER and UPPER

The functions LOWER and UPPER allow the case of a string to be altered quickly and easily, so that all the characters are lower- or uppercase, respectively. These functions are supported in all the database implementations covered in this book.

 
Example
SELECT LOWER('You Talkin To ME?'), UPPER('you talking to me?!');
-> you talking to me?, YOU TALKIN TO ME?!

The various database vendors also support a variety of other text formatting functions that are specific to their implementation.

 
SUBSTRING

The SUBSTRING function allows one character string to be extracted from another.

 
SQL99 Syntax
SUBSTRING(extraction_string FROM starting_position [FOR length]
[COLLATE collation_name])

If any of the inputs are NULL, the SUBSTRING function returns a NULL. The extraction_string is where the character value is extracted from. It may be a literal string, a column in a table with a character datatype, or a variable with a character datatype. The starting_position is an integer value telling the function at which position to perform the extract. The optional length is an integer value that tells the function how many characters to extract, starting at the starting_position.

 
MySQL Syntax and Variations
SUBSTRING(extraction_string FROM starting_position)

MySQL's implementation assumes that the characters are to be extracted from the starting position continuing to the end of the character string.

 
Microsoft SQL Server Syntax and Variations
SUBSTRING(extraction_string [FROM starting_position] [FOR length])

Microsoft SQL Server largely supports the SQL99 standard, except that it does not allow the COLLATE clause. Microsoft allows this command to be applied to text, image, and binary datatypes; however, the starting_position and length represent the number of bytes rather than the number of characters to count.

 
Oracle Syntax and Variations
SUBSTR(extraction_string, starting_position [, length])

Oracle's implementation, SUBSTR, largely functions the same way as SQL99. It does not support the COLLATE clause. When a starting_value is a negative number, Oracle counts from the end of the extraction_string. If length is omitted, the remainder of the string (starting at starting_position) is returned.

 
PostgreSQL Syntax and Variations
SUBSTRING(extraction_string [FROM starting_position] [FOR length])

PostgreSQL largely supports the SQL99 standard, except that it does not accept the COLLATE clause.

 
Examples

These examples generally work on any one of the four database vendors profiled in this book. Only the second Oracle example, with a negative starting position, fails on the others (assuming, of course, that Oracle's SUBSTR is translated into SUBSTRING ):

/* On Oracle, counting from the left */

SELECT SUBSTR('ABCDEFG',3,4) FROM DUAL; -> CDEF

/* On Oracle, counting from the right */ SELECT SUBSTR('ABCDEFG',-5,4) FROM DUAL; -> CDEF

/* On MySQL */

SELECT SUBSTRING('Be vewy, vewy quiet',5); -> 'wy, vewy quiet''

/* On PostgreSQL or SQL Server */

SELECT au_lname, SUBSTRING(au_fname, 1, 1) FROM authors WHERE au_lname = 'Carson' -> Carson C
 
TRIM

The TRIM function removes leading spaces, trailing characters, or both from a specified character string. This function also removes other types of characters from a specified character string. The default function is to trim the specified character from both sides of the character string. If no removal string is specified, TRIM removes spaces by default.

 
SQL99 Syntax
TRIM( [ [{LEADING | TRAILING | BOTH}] [removal_string] FROM ]
  target_string
  [COLLATE collation_name])

The removal_string is the character string to be stripped out. The target _string is the character string from which characters are to be taken. If a removal_string is not specified, then TRIM strips out spaces. The COLLATE clause forces the result set of the function into another preexisting collation set.

MySQL, PostgreSQL, and Oracle support the SQL99 syntax of TRIM.

Microsoft SQL Server (and the other vendors for that matter) provide the functions LTRIM and RTRIM to trim off leading spaces or trailing spaces, respectively. LTRIM and RTRIM cannot be used to trim other types of characters.

 
Examples
SELECT TRIM('   wamalamadingdong  ');
-> 'wamalamadingdong'
SELECT TRIM(LEADING '19' FROM '1976 AMC GREMLIN');
-> '76 AMC GREMLIN'
SELECT TRIM(BOTH 'x' FROM 'xxxWHISKEYxxx');
-> 'WHISKEY'
SELECT TRIM(TRAILING 'snack' FROM 'scooby snack');
-> 'scooby '