SQL in a Nutshell, 2nd Edition [Electronic resources] نسخه متنی

4.4 ANSI SQL Scalar Functions

The
ANSI SQL scalar functions return a
single value each time they are invoked. The SQL standard provides
many scalar functions that can be used to manipulate date and time
types, strings, and numbers, as well as retrieve system information
such as the current user or login name.
Scalar functions fall into the categories
listed in Table 4-2.

Function category

Explanation

4.4.1 Built-in Scalar Functions

SQL2003


built-in scalar functions identify
both the current user session and the characteristics of the current
user session, such as the current session privileges. Built-in scalar
functions are always nondeterministic. The
CURRENT_DATE, CURRENT_TIME,
and CURRENT_TIMESTAMP functions listed in Table 4-3 are built-in functions that fall into the
date-and-time category of functions. Although the five platforms
provide many additional functions beyond these SQL built-ins, the SQL
standard defines only those listed in Table 4-3.

Function

Usage

DB2 supports the SQL2003 built-in scalar functions
CURRENT_DATE, CURRENT_TIME,
CURRENT_TIMESTAMP,
CURRENT_USER, and USER. The
SQL2003 scalar functions CURRENT_ROLE,
LOCAL_TIME,
LOCAL_TIMESTAMP,
SESSION_USER, and
SYSTEM_USER are not supported.

MySQL

MySQL supports all the SQL2003 built-in scalar functions, plus
Oracle''''s variant SYSDATE. In
addition, MySQL supports NOW( ) as a synonym of
the function CURRENT_TIMESTAMP.

Oracle

Oracle supports USER and
SYSDATE as a synonym of
CURRENT_TIMESTAMP.

PostgreSQL

PostgreSQL supports all the SQL2003 built-in scalar functions except
SESSION_USER.

SQL Server

SQL Server supports all the built-in scalar functions.

Examples

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

/* On MySQL */
SELECT CURRENT_TIMESTAMP;''''2001-12-15 23:50:26''''
/* DB2 */
VALUES CURRENT_TIMESTAMP''''2001-12-15 23.50.26.000000''''
/* On Microsoft SQL Server */
SELECT CURRENT_TIMESTAMPGO''''Dec 15,2001 23:50:26''''
/* On Oracle */
SELECT USER FROM dual;dylan

4.4.2 CASE and CAST Functions

ANSI SQL2003 provides a function named CASE that
can be used to create IF-THEN flow-control logic
within queries or update statements. The CAST
function is for converting between datatypes and is also in the ANSI
standard. All of the databases covered by this book provide ANSI
standard support for both the CASE and
CAST functions.

The CASE function provides
IF-THEN-ELSE functionality within a
SELECT or UPDATE statement.
It evaluates a list of conditions and returns one value out of
several possible values.

CASE has two usages: simple and searched. Simple
CASE expressions compares one value, the
input_value, with a list of other values
and return a result associated with the first matching value.
Searched CASE expressions allow the analysis of
several logical conditions and return a result associated with the
first one that is true.

All vendors provide the ANSI SQL2003 syntax for
CASE.

SQL2003 Syntax and Description

-- Simple comparison operation
CASE input_valueWHEN when_condition THEN resulting_value[...n]
[ELSE else_result_value]
END
-- Boolean searched operation
CASE
WHEN Boolean_condition THEN resulting_value[...n]
[ELSE else_result_expression]
END

In the simple CASE function, the
input_value is evaluated against each
WHEN clause. The
resulting_value is returned for the first
TRUE instance of input_value =
when_condition. If no
when_condition evaluates as TRUE, the
else_result_value is returned. If no
else_result_value is specified, then NULL
is returned.

In the more elaborate Boolean searched operation, the structure is
essentially the same as the simple comparison operation except that
each WHEN clause has its own Boolean comparison
operation.

In either usage, multiple WHEN clauses are used,
though only one ELSE clause is necessary.

Examples

Here is a simple comparison operation where the
CASE function alters the display of the
contract column to make it more
understandable:

SELECT  au_fname,
au_lname,
CASE contract
WHEN 1 THEN ''''Yes''''
ELSE ''''No''''
END ''''contract''''
FROM    authors
WHERE   state = ''''CA''''

Here is an elaborate searched CASE function in a
SELECT statement that will report how many
titles have been sold in different year-to-date sales ranges:

SELECT CASE
WHEN ytd_sales IS NULL  THEN ''''Unknown''''
WHEN ytd_sales <=   200 THEN ''''Not more than 200''''
WHEN ytd_sales <=  1000 THEN ''''Between  201 and  1000''''
WHEN ytd_sales <=  5000 THEN ''''Between 1001 and  5000''''
WHEN ytd_sales <= 10000 THEN ''''Between 5001 and 10000''''
ELSE ''''Over 10000''''
END ''''YTD Sales'''',
COUNT(*) ''''Number of Titles''''
FROM   titles
GROUP BY CASE
WHEN ytd_sales IS NULL  THEN ''''Unknown''''
WHEN ytd_sales <=   200 THEN ''''Not more than 200''''
WHEN ytd_sales <=  1000 THEN ''''Between  201 and  1000''''
WHEN ytd_sales <=  5000 THEN ''''Between 1001 and  5000''''
WHEN ytd_sales <= 10000 THEN ''''Between 5001 and 10000''''
ELSE ''''Over 10000''''
END
ORDER BY MIN( ytd_sales )

The results are:

YTD Sales              Number of Titles
---------------------- ----------------
Unknown                2
Not more than 200      1
Between  201 and  1000 2
Between 1001 and  5000 9
Between 5001 and 10000 1
Over 10000             3

Next is an UPDATE statement that applies
discounts to all of the titles. This more complicated command will
discount all personal computer-related titles by 25%, all other
titles by 10%, and apply only a 5% discount to titles with
year-to-date sales exceeding 10,000 units. This query uses a searched
CASE expression to perform price adjustment:

UPDATE  titles
SET     price = price *
CASE
WHEN ytd_sales > 10000     THEN 0.95  -- 5% discount
WHEN type = ''''popular_comp'''' THEN 0.75  -- 25% discount
ELSE 0.9                              -- 10% discount
END
WHERE   pub_date IS NOT NULL

The update has now completed three separate
UPDATE operations in a single statement.

The CAST command explicitly converts an
expression of one datatype to another. All vendors provide the ANSI
SQL2003 syntax for CAST.

SQL2003 Syntax and Description

CAST(expression AS data_type[(length)])

The CAST function converts any expression, such
as a column value or variable, into another defined datatype. The
length of the datatype may be optionally supplied for those datatypes
(such as CHAR or VARCHAR)
that support lengths.

Examples

This example retrieves the year-to-date sales as a
CHAR and concatenates it with a literal string
and a portion of the title of the book. It converts ytd_sales to CHAR(5),
plus it shortens the length of the title to make the results more readable.

SELECT CAST(ytd_sales AS CHAR(5)) + '''' Copies sold of '''' + CAST(title AS VARCHAR(30))
FROM titles
WHERE ytd_sales IS NOT NULL
AND ytd_sales > 10000
ORDER BY ytd_sales DESC

The results are:

---------------------------------------------------
22246 Copies sold of The Gourmet Microwave
18722 Copies sold of You Can Combat Computer Stress
15096 Copies sold of Fifty Years in Buckingham Pala

4.4.3 Numeric Scalar Functions

The list of official
SQL2003 numeric functions is rather
small, and the different platforms provide supplementary mathematical
and statistical functions. MySQL directly supports many of these
functions. The other database platforms 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.

Function

Usage

All platforms have standard SQL2003 support for the
ABS function.

SQL2003 Syntax

ABS( expression )

ABS returns the absolute value of the number in
expression.

Example

The following shows how to use the ABS function:

/* SQL2003 */
SELECT ABS(-1) FROM NUMBERS1

All platforms stray from the ANSI standard in their support for the
scalar functions for determining the length of expressions. While the
platform support is nonstandard, the equivalent functionality exists
under different names.

SQL2003 Syntax

The SQL2003 scalar functions for getting the length of a value take
an expression to calculate the value and
return the length as an integer. The BIT_LENGTH
function returns the number of bits contained within the value of
expression. The
CHAR_LENGTH is the number of characters in the
string expression.
OCTET_LENGTH returns the number of octets within
the string expression. All three of these
functions will return NULL if expression
is NULL.

BIT_LENGTH( expression )
CHAR_LENGTH( expression )
OCTET_LENGTH( expression )

DB2

DB2 does not support BIT_LENGTH,
CHAR_LENGTH, or
OCTET_LENGTH. DB2 does support a scalar function
named LENGTH( ), which provides similar
functionality for nongraphic string types. For graphic string types,
LENGTH returns the number of double-byte
characters in a string.

MySQL

MySQL supports CHAR_LENGTH and the SQL2003
synonym CHARACTER_LENGTH( ).

Oracle

The closest any of the platforms 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. For the
length of an expression in characters, Oracle provides a
LENGTH( ) function as a synonym for
CHAR_LENGTH.

PostgreSQL

PostgreSQL supports CHAR_LENGTH and the SQL2003
synonym CHARACTER_LENGTH( ).

SQL Server

SQL Server provides the LEN function.

Example

The following example, shown for different databases, 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 titlesWHERE type = ''''popular_comp''''GO
/* On Oracle or DB2 */
SELECT LENGTH(''''HORATIO'''') "Length of characters"FROM dual;

The CEIL function returns the smallest integer
greater than an input value that you specify.

SQL2003 Syntax

SQL2003 supports the following two forms of the function:

CEIL( expression )
CEILING ( expression )

DB2

DB2 supports both variations of the SQL2003 syntax.

MySQL

MySQL supports only CEILING.

Oracle

Oracle supports only CEIL.

PostgreSQL

PostgreSQL has no support for either CEIL or
CEILING.

SQL Server

SQL Server supports only CEILING.

Examples

When you pass a positive, noninteger number, the effect of
CEIL is to round up to the next highest integer:

SELECT CEIL(100.1) FROM dual;
FLOOR(100.1)
------------
101

Remember, though, that with negative numbers, rounding
"up" results in a lower absolute
value:

SELECT CEIL(-100.1) FROM dual;
FLOOR(-100.1)
-------------
-100

Use FLOOR to get behavior opposite to that of
CEIL.

The EXP function returns the value of the
mathematical constant e (approximately 2.718281)
raised to the power of a specified number.

SQL2003 Syntax

All platforms support the SQL2003 syntax:

EXP( expression )

Example

The following example uses EXP to return an
approximation of e:

SELECT EXP(1) FROM dual;
EXP(1)
----------
2.71828183
Use LN to go in the opposite direction.

The EXTRACT function is supported by Oracle,
PostgreSQL, and MySQL. The other platforms support a separate command
to accomplish the same functionality.

SQL2003 Syntax

EXTRACT( date_part FROM expression )

The SQL2003 scalar function for extracting parts from a date is
EXTRACT. The SQL2003 EXTRACT function takes a
date_part and an
expression that evaluates to a date time
value.

DB2

DB2 provides unique functions that provide the same behavior as
EXTRACT. Those functions are
DAY, DAYNAME,
DAYOFWEEK, DAYOFWEEK_ISO,
DAYOFYEAR, DAYS,
HOUR, JULIAN_DAY,
MICROSECOND,
MIDNIGHT_SECONDS, MINUTE,
MONTH, MONTHNAME,
SECOND, TIME,
WEEK, WEEK_ISO, and
YEAR. Look to the list shown later in this
chapter in Section 4.5.1 for examples
on how to use these functions. As with Oracle, DB2 also offers a
TO_CHAR function that can be used to format a
string from a date value.

MySQL

MySQL''''s implementation is extended somewhat beyond
the ANSI standard. The ANSI standard does not have a provision for
returning multiple fields from 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.

Type value

Meaning

Oracle

Oracle supports the SQL2003 syntax with the dateparts listed in Table 4-6.

Type value

Meaning

PostgreSQL

PostgreSQL supports the SQL2003 syntax with a few extra dateparts.
PostgreSQL supports the dateparts listed in Table 4-7.

Type value

Meaning

SQL Server

SQL Server provides the function
DATEPART(date_part,
expression) as a synonym for the SQL2003
function
EXTRACT(date_part
FROM expression). SQL
Server supports the dateparts listed in Table 4-8.

Type value

Meaning

Example

This example extracts dateparts from several date time values:

/* On MySQL  */
SELECT EXTRACT(YEAR FROM "2013-07-02");2013
SELECT EXTRACT(YEAR_MONTH FROM "2013-07-02 01:02:03");201307
SELECT EXTRACT(DAY_MINUTE FROM "2013-07-02 01:02:03");20102
/* On PostgreSQL */
SELECT EXTRACT(HOUR FROM TIMESTAMP ''''2001-02-16 20:38:40'''');
20

The FLOOR function returns the largest integer
less than an input value that you specify.

SQL2003 Syntax

All platforms support the SQL2003 syntax:

FLOOR( expression )

Examples

When you pass a positive number, the effect of
FLOOR is to eliminate anything after the decimal
point:

SELECT FLOOR(100.1) FROM dual;
FLOOR(100.1)
------------
100

Remember, though, that with negative numbers, going in the
"less than" direction corresponds
to increasingly larger absolute values:

SELECT FLOOR(-100.1) FROM dual;
FLOOR(-100.1)
-------------
-101

Use CEIL to get behavior opposite to
FLOOR.

The LN function returns the natural logarithm of
a number, which is the power to which you would need to raise the
mathematical constant e (approximately 2.718281)
in order to get the number in question as the result.

SQL2003 Syntax

LN( expression )

DB2, Oracle, PostgreSQL

DB2, Oracle, and PostgreSQL support the SQL2003 syntax for the
LN function. DB2 and PostgreSQL also support the
use of LOG as a synonym for
LN.

MySQL and SQL Server

MySQL and SQL Server call their natural logarithm function
LOG:

LOG( expression )

Example

The following, Oracle-based example shows the natural logarithm of a
number closely approximating the mathematical constant known as
e:

SELECT LN(2.718281) FROM dual;
LN(2.718281)
------------
.999999695

Use the EXP function to go in the other
direction.

The MOD function returns the remainder of a
dividend divided by a divider. All platforms support the SQL2003
syntax for the MOD function.

SQL2003 Syntax

MOD( dividend, divider )

The standard syntax for the MOD function is to
return the remainder of dividend divided by the divider; returns the
dividend if the divider is 0.

Example

The following shows how to use the MOD function
from within a SELECT statement:

SELECT MOD(12, 5) FROM NUMBERS
2

The POSITION function returns an integer that
indicates the starting position of a string within the search string.

SQL2003 Syntax

POSITION( string1 IN string2 )

The standard syntax for the POSITION function is
to return the first location of string1
within string2.
POSITION returns 0 if
string1 does not occur within
string2 and NULL if either argument is
NULL.

DB2

DB2''''s equivalent function is
POSSTR.

MySQL

MySQL supports POSITION as defined in SQL2003.

Oracle

Oracle''''s equivalent function is called
INSTR.

PostgreSQL

PostgreSQL supports POSITION as defined in
SQL2003.

SQL Server

SQL Server has both CHARINDEX and
PATINDEX. CHARINDEX and
PATINDEX are very similar, except that
PATINDEX allows the use of wildcard characters
in the search criteria.

Examples

/* DB2 */
SELECT POSSTR(''''bar'''', ''''foobar'''');
4
/* 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'''' )GO4
SELECT PATINDEX( ''''%fg'''', ''''abcdefg'''' )GO6

Use POWER to raise a number to a specific value.

SQL2003 Syntax

POWER( base, exponent )

The result of the POWER function is
base raised to the
exponent power, or
baseexponent. If
base is negative, then
exponent must be an integer.

DB2, Oracle, PostgreSQL, and SQL Server

These vendors all support the SQL2003 syntax.

MySql

MySQL supports the same functionality, but requires the use of the
keyword POW:

POW( base, exponent )

Example

Raising a positive number to an exponent is straightforward:

SELECT POWER(10,3) FROM dual;
POWER(10,3)
-----------
1000

Anything raised to the 0th power evaluates to 1:

SELECT POWER(0,0) FROM dual;
POWER(0,0)
----------
1

Negative exponents move the decimal point to the left:

SELECT POWER(10,-3) FROM dual;
POWER(10,-3)
------------
.001

The SQRT function returns the square root of a
number.

SQL2003 Syntax

All platforms support the SQL2003 syntax:

SQRT( expression )

Example

SELECT SQRT(100) FROM dual;
SQRT(100)
----------
10

The WIDTH_BUCKET function assigns values to
buckets in an equiwidth histogram.

SQL2003 Syntax

In the following syntax, expression
represents a value to be assigned to a bucket. You would typically
base expression on one or more columns
returned by a query.

WIDTH_BUCKET(expression, min, max, buckets)

The buckets argument specifies the number
of buckets to create over the range defined by
min through
max. min is
inclusive, whereas max is not. The value
from expression is assigned to one of
those buckets, and the function then returns the corresponding bucket
number. When expression falls outside the
range of buckets, the function returns either 0 or
max+1, depending on whether
expression is lower than
min or greater than or equal to
max.

Examples

The following example divides the integer values 1 through 10 into
two buckets:

SELECT x, WIDTH_BUCKET(x,1,10,2)
FROM pivot;
X WIDTH_BUCKET(X,1,10,2)
---------- ----------------------
1                      1
2                      1
3                      1
4                      1
5                      1
6                      2
7                      2
8                      2
9                      2
10                      3

The following example is more interesting. It divides 11 values from
1 through 10 into three buckets, and illustrates the distinction
between min being inclusive and
max being noninclusive:

SELECT x, WIDTH_BUCKET(x,1,10,3)
FROM pivot;
X WIDTH_BUCKET(X,1,10,3)
---------- ----------------------
1                      1
2                      1
3                      1
4                      2
5                      2
6                      2
7                      3
8                      3
9                      3
9.9                      3
10                      4

Pay particular attention to the results for X=1, 9.9, and 10. An
input value of min, 1 in this example,
falls into that first bucket, proving that the lower end of the range
for bucket #1 is defined as x >= min. An input
value of max, however, falls
outside the highest bucket. In this example, 10
falls into the overflow bucket numbered
max+1. The value 9.9 falls into bucket #3,
illustrating that the upper end of the range for the highest bucket
is defined as x < max.

4.4.4 String Functions and Operators

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

Function or operator

Usage

SQL2003 defines a concatenation operator (||),
which joins two distinct strings into one string value.

DB2

DB2 supports the SQL2003 concatenation operator and the
CONCAT function as a synonym.

MySQL

MySQL supports CONCAT( ) as a synonym for the
SQL2003 concatenation operator.

Oracle and PostgreSQL

PostgreSQL and Oracle support the SQL2003 double vertical bar
concatenation operator.

SQL Server

SQL Server uses the plus sign (+) as a synonym
for the SQL2003 concatenation operator. SQL Server has the system
setting CONCAT_NULL_YIELDS_NULL, which can be
set to alter the behavior when NULL values are used in the
concatenation of string values.

Examples

/* SQL2003 Syntax */
''''string1'''' || ''''string2'''' || ''''string3''''
''''string1string2string3''''
/* On MySQL */
CONCAT(''''string1'''', ''''string2'''')
''''string1string2''''

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

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 Cyrillic
(Russian) character set. The translation must already exist, either
by default or by having been created using the CREATE
TRANSLATION command.

SQL2003 Syntax

CONVERT (char_value USING conversion_char_name)
TRANSLATE(char_value USING translation_name)

CONVERT converts
char_value to the characterset with the
name supplied in conversion_char_name.
TRANSLATE converts
char_value to the character set provided
in translation_name.

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

MySQL''''s implementation of the
CONV function only translates numbers from one
base to another. In contrast, 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 SQL2003 CONVERT function.
PostgreSQL does not support CONVERT, and its
implementation of TRANSLATE morphs any
occurrence of a character string to any other character string.

DB2

DB2 does not support
CONVERT, and its support for the
TRANSLATE function is not ANSI standard.
TRANSLATE is used to translate substrings and
has historically acted as a synonym for UPPER,
since UPPER was only recently added to DB2. When
TRANSLATE is used with only a string expression
as an argument, the result will be an uppercase equivalent of that
argument. When used with more than one argument, as in
TRANSLATE(source,
replacements,
match),
the function translates all characters in
source that are also in
match. Each character in
source will be replaced with the character
in replacements that is found in the same
position as the matching character in
match. Following are some examples:

TRANSLATE(''''Hello, World!'''')
''''HELLO, WORLD!''''
TRANSLATE(''''Hello, World!'''', ''''wZ'''', ''''lW'''')
''''Hewwo, Zorwd!''''

MySQL

MySQL does not support
TRANSLATE or CONVERT.

Oracle

Oracle supports the following CONVERT and
TRANSLATE functions:

CONVERT(char_value, target_char_set, source_char_set)
TRANSLATE(char_value USING {CHAR_CS | NCHAR_CS})

Under Oracle''''s implementation, the
CONVERT function returns the text of
char_value in the target character set.
The char_value is the string to convert,
target_char_set is the name of the
character set into which the string is to be converted, and
source_char_set is the name of the
character set in which char_value was
originally stored.

Oracle''''s TRANSLATE function
follows the ANSI syntax, but you can choose between two character
sets only: the database character set (CHAR_CS)
and the national character set (NCHAR_CS).

Oracle supports a different function that is also named
TRANSLATE, which omits the SELECT CONVERT(''''Gro2'''', ''''US7ASCII'''', ''''WE8HP'''')FROM DUAL;Gross

PostgreSQL

PostgreSQL supports the ANSI standard CONVERT
and conversions can be defined by using CREATE
CONVERSION. PostgreSQL''''s implementation
of the TRANSLATE function offers a large
superset of functions that can convert any occurrence of one text
string to another within another specified string.

TRANSLATE (character_string, from_text, to_text)

Here are some examples:

SELECT TRANSLATE(''''12345abcde'''', ''''5a'''', ''''XX'''');''''1234XXbcde''''
SELECT TRANSLATE(title, ''''Computer'''', ''''PC'''')FROM  titlesWHERE type = ''''Personal_computer''''
SELECT CONVERT(''''PostgreSQL'''' USING iso_8859_1_to_utf_8)
''''PostgreSQL''''

SQL Server

SQL Server does not support
TRANSLATE. SQL Server''''s
implementation of the CONVERT function does not
follow the SQL2003 specification. Instead, it is functionally
equivalent to the CAST function.

CONVERT (data_type[(length) | (precision,scale)], expression[,style])

The style clause is used to define the
format of a date conversion. Refer to the SQL Server documentation
for more information. Following is an example:

SELECT title, CONVERT(char(7), ytd_sales)
FROM titles
ORDER BY title
GO

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. The different database
platforms also support a variety of other text formatting functions
that are specific to their implementation.

SQL2003 Syntax

LOWER(string)
UPPER(string)

LOWER converts string
into a lowercase string. UPPER is the uppercase
counterpart of LOWER.

DB2 and MySQL

These platforms support the SQL2003 UPPER and
LOWER scalar functions, as well as the synonyms
UCASE and LCASE.

Oracle, PostgreSQL, and SQL Server

These platforms support the SQL2003 UPPER and
LOWER scalar functions, as in the following
example:

Example

SELECT LOWER(''''You Talkin To ME?''''), UPPER(''''you talking to me?!'''');
you talkin to me?, YOU TALKING TO ME?!

The OVERYLAY function embeds one string into
another and returns the result.

SQL2003 Syntax

OVERLAY(string PLACING embedded_string FROM start[FOR length])

If any of the inputs are NULL, the OVERLAY
function returns a NULL. The
embedded_string replaces the
length characters in
string starting at character position
start. If the
length is not specified, then the
embedded_string will replace all
characters after start in
string.

DB2, MySQL, Oracle, and SQL Server

These platforms do not have support for the
OVERLAY function. You can simulate the
OVERLAY function on these platforms by using a
combination of SUBSTRING and the concatenation
operator.

PostgreSQL

PostgreSQL supports the ANSI standard for
OVERLAY.

Examples

This is an example of how to use the OVERLAY
function:

/* SQL2003 and PostgreSQL */
SELECT OVERLAY(''''DONALD DUCK'''' PLACING ''''TRUMP'''' FROM 8) FROM NAMES;''''DONALD TRUMP''''

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

SQL2003 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 the source where the
character value is to be extracted. 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 begin
performing the extract. The optional
length is an integer value that tells the
function how many characters to extract, starting at the
starting_position. If the optional
FOR is omitted, then the substring starting at
starting_position and continuing to the
end of the extraction_string is returned.

DB2

SUBSTR(extraction_string, starting_position [, length])

DB2''''s implementation, SUBSTR,
largely functions the same way as SQL2003''''s
SUBSTRING. It does not support the
COLLATE clause. If
length is omitted, the remainder of the
string (starting at starting_position) is
returned.

MySQL

SUBSTRING(extraction_string FROM starting_position)

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

Oracle

SUBSTR(extraction_string, starting_position [, length])

Oracle''''s implementation,
SUBSTR, largely functions the same way as
SQL2003''''s SUBSTRING. It
does not support the
COLLATE clause. When a
starting_position 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

SUBSTRING(extraction_string [FROM starting_position] [FOR length])

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

SQL Server

SUBSTRING(extraction_string [FROM starting_position] [FOR length])

SQL Server largely supports the ANSI standard, except that it does
not allow the COLLATE clause. SQL Server 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.

Examples

These examples generally work on any one of the five database
platforms 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'''' FROM 5);''''wy, vewy quiet''''
/* On PostgreSQL or SQL Server */
SELECT au_lname, SUBSTRING(au_fname, 1, 1)FROM authorsWHERE au_lname = ''''Carson''''
Carson     C

The TRIM function removes leading characters,
trailing characters, or both from a specified character string or
BLOB value. 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 character is specified,
TRIM removes spaces by default.

SQL2003 Syntax

TRIM( [ [{LEADING | TRAILING | BOTH}] [removal_char] FROM ]
target_string 
[COLLATE collation_name])

The removal_char is the character to be
stripped out. The target_string is the
character string from which characters are to be stripped. If a
removal_char is not specified, then
TRIM strips out spaces. The
COLLATE clause forces the result set of the
function into another pre-existing collation set.

DB2

DB2 provides the functions LTRIM and
RTRIM to trim off leading spaces or trailing
spaces, respectively.

MySQL, Oracle, and PostgreSQL

These platforms support the SQL2003 syntax of
TRIM.

Microsoft SQL Server

SQL Server provides the functions LTRIM and
RTRIM to trim off leading spaces or trailing
spaces, respectively. On SQL Server the LTRIM
and RTRIM cannot be used to trim other types of
characters.

Examples

SELECT TRIM(''''   wamalamadingdong  '''');''''wamalamadingdong''''
SELECT LTRIM( RTRIM(''''   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 ''''