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.
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 separate usage from the "built-in" functions described here. |
CASE and CAST | While these two functions operate on scalar input values, they are in a category all their own. CASE supplies IF-THEN logic to SQL statements and CAST can convert values from one datatype to another. |
Date and Time | Performs operations on temporal datatypes and returns values in a temporal datatype format. There is no SQL2003 function that operates on a temporal datatype and returns a temporal result. The closest function is EXTRACT (covered in Section 4.4.3 later in this chapter), which operates on temporal values and returns numeric values. Functions returning temporal values but operating on no arguments are covered in the later Section 4.4.1. |
Numeric | Performs operations on numeric values and returns numeric values. |
String | Performs operations on character values (e.g., CHAR, VARCHAR, NCHAR, NVARCHAR, and CLOB) and returns a string or numeric value. |
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.
CURRENT_DATE | Returns the current date. |
CURRENT_TIME | Returns the current time. |
CURRENT_TIMESTAMP | Returns the current date and time. |
CURRENT_USER or USER | Returns the currently active user within the database server. |
SESSION_USER | Returns the currently active Authorization ID, if it differs from the user. |
SYSTEM_USER | Returns the currently active user within the host operating system. |
DB2 |
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.
CASE |
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.
CAST |
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.
ABS | Returns the absolute value of a number. |
MOD | Returns the remainder of one number divided into another. |
BIT_LENGTH | Returns an integer value representing the number of bits in another value. |
CEIL or CEILING | Rounds a noninteger value upwards to the next greatest integer. Returns an integer value unchanged. |
CHAR_LENGTH | Returns an integer value representing the number of characters in a string expression. |
EXP | Raises a value to the power of the mathematical constant known as e. |
EXTRACT | Allows the datepart to be extracted (YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, or TIMEZONE_MINUTE) from a temporal expression. |
FLOOR | Rounds a noninteger value downwards to the next least integer. Returns an integer value unchanged. |
LN | Returns the natural logarithm of a number. |
OCTET_LENGTH | Returns an integer value representing the number of octets in another value. This value is the same as BIT_LENGTH/8. |
POSITION | Returns an integer value representing the starting position of a string within the search string. |
POWER | Raises a number to a specified power. |
SQRT | Computes the square root of a number. |
WIDTH_BUCKET | Deposits a value into the appropriate bucket from a set of buckets covering a given range. |
ABS |
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
BIT_LENGTH, CHAR_LENGTH, and OCTET_LENGTH |
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;
CEIL |
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.
EXP |
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.
EXTRACT |
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.
SECOND | Seconds |
MINUTE | Minutes |
HOUR | Hours |
DAY | Days |
MONTH | Months |
YEAR | Years |
MINUTE_SECOND | Minutes and seconds |
HOUR_MINUTE | Hours and minutes |
DAY_HOUR | Days and hours |
YEAR_MONTH | Years and months |
HOUR_SECOND | Hours, minutes, and seconds |
DAY_MINUTE | Days, hours, and minutes |
DAY_SECOND | Days, hours, minutes, and seconds |
Oracle
Oracle supports the SQL2003 syntax with the dateparts listed in Table 4-6.
DAY | The day of the month field (1-31) |
HOUR | Returns the hour field (0-23) |
MINUTE | The minutes field (0-59) |
MONTH | The month field (1-12) |
SECOND | The seconds field (0-59) |
TIMEZONE_HOUR | The hour component of the time zone offset |
TIMEZONE_MINUTE | The minute component of the time zone offset |
TIMEZONE_REGION | The current time zone name |
TIMEZONE_ABBR | The abbreviation of the current time zone |
YEAR | The year field |
PostgreSQL
PostgreSQL supports the SQL2003 syntax with a few extra dateparts.
PostgreSQL supports the dateparts listed in Table 4-7.
CENTURY | The year field divided by 100. |
DAY | The day of the month field (1-31). |
DECADE | The year field divided by 10. |
DOW | Day of week (0-6, where Sunday is 0). This type only works for TIMESTAMP values. |
DOY | Day of year (1-366.) The maximum returned value is only 365 for years that are not leap years. This type can only be used on TIMESTAMP values. |
EPOCH | Returns the number of seconds between the epoch (1970-01-01 00:00:00-00) and the value. The result can be negative for values before the epoch. |
HOUR | Returns the hour field (0-23). |
MICROSECONDS | The seconds field (including fractional parts) multiplied by 1,000,000. |
MILLENNIUM | The year field divided by 1,000. |
MILLISECONDS | The seconds field (including fractional parts) multiplied by 1,000. |
MINUTE | The minutes field (0-59). |
MONTH | The month field (1-12). |
QUARTER | The quarter of the year (1-4) that the value is in. This type can only be used with TIMESTAMP values. |
SECOND | The seconds field (0-59). |
TIMEZONE_HOUR | The hour component of the time zone offset. |
TIMEZONE_MINUTE | The minute component of the time zone offset. |
WEEK | The week number that the value falls on within the year. |
YEAR | The year field. |
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.
year | The year field of the datetime expression. The abbreviations yy and yyyy can also be used for two-digit and four-digit years, respectively. |
quarter | The quarter that the datetime expression falls on. The abbreviations q and qq can also be used. |
dayofyear | The day of the year for the date time expression. The abbreviations y and dy can also be used. |
day | The day of the month for the date time expression. The abbreviations d and dd can also be used. |
week | The week of the year for the date time expression. The abbreviations wk and ww can also be used. |
weekday | The day of the week for the date time expression. The abbreviation dw can also be used. |
hour | The hour of the day for the date time expression. The abbreviation hh can also be used. |
minute | The minute of the hour for the date time expression. The abbreviations n and mi can also be used. |
second | The second of the minute for the date time expression. The abbreviations s and ss can also be used. |
millisecond | The millisecond for the date time expression. The abbreviation ms can also be used. |
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
FLOOR |
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.
LN |
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.
MOD |
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
POSITION |
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
POWER |
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
SQRT |
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
WIDTH_BUCKET |
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.
Concatenation operator | Appends two or more literal string 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. |
OVERLAY | Returns the result of replacing a substring of one string with another. |
SUBSTRING | Returns 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. |
Concatenation Operator |
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
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 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
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. 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?!
OVERLAY |
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''''
SUBSTRING |
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
TRIM |
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 ''''