Previous Section Table of Contents Next Section

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.

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 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.

Table 4-3. ANSI SQL built-in scalar functions

Function

Usage

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.

Be aware that some conversions, such as DECIMAL values to INTEGER, will result in rounding operations. Also, some conversion operations may result in an error if the new datatype does not have sufficient space to display the converted value.


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.

Table 4-4. SQL2003 numeric functions

Function

Usage

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.

When extracting parts of a date value on DB2 into an integer, such as the year or minute, use the special functions provided by DB2 instead of the TO_CHAR function. Doing so will provide the highest performance, since a conversion from a result string into an integer won't be required.


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.

Table 4-5. MySQL dateparts

Type value

Meaning

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.

Table 4-6. Oracle dateparts

Type value

Meaning

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.

Table 4-7. PostgreSQL dateparts

Type value

Meaning

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.

Table 4-8. SQL Server dateparts

Type value

Meaning

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.

Table 4-9. SQL String functions and operators

Function or operator

Usage

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 USING keyword. That version of TRANSLATE has nothing to do with character set translation.


Both the target and source character set names can be passed either as literal strings, in variables, or in columns from a table. Note that 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('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 '

    Previous Section Table of Contents Next Section