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.
|
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 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 supports USER and SYSDATE as a synonym of CURRENT_TIMESTAMP.
PostgreSQL supports all the SQL2003 built-in scalar functions except SESSION_USER.
SQL Server supports all the built-in scalar functions.
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
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.
-- 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.
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.
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.
|
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
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.
ABS( expression )
ABS returns the absolute value of the number in expression.
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.
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 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 supports CHAR_LENGTH and the SQL2003 synonym CHARACTER_LENGTH( ).
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 supports CHAR_LENGTH and the SQL2003 synonym CHARACTER_LENGTH( ).
SQL Server provides the LEN function.
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 supports the following two forms of the function:
CEIL( expression ) CEILING ( expression )
DB2 supports both variations of the SQL2003 syntax.
MySQL supports only CEILING.
Oracle supports only CEIL.
PostgreSQL has no support for either CEIL or CEILING.
SQL Server supports only CEILING.
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.
All platforms support the SQL2003 syntax:
EXP( expression )
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.
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 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''''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 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 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 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.
|
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.
All platforms support the SQL2003 syntax:
FLOOR( expression )
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.
LN( expression )
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 call their natural logarithm function LOG:
LOG( expression )
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.
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.
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.
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''''s equivalent function is POSSTR.
MySQL supports POSITION as defined in SQL2003.
Oracle''''s equivalent function is called INSTR.
PostgreSQL supports POSITION as defined in SQL2003.
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.
/* 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.
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.
These vendors all support the SQL2003 syntax.
MySQL supports the same functionality, but requires the use of the keyword POW:
POW( base, exponent )
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.
All platforms support the SQL2003 syntax:
SQRT( expression )
SELECT SQRT(100) FROM dual;
SQRT(100)
----------
10
WIDTH_BUCKET |
The WIDTH_BUCKET function assigns values to buckets in an equiwidth histogram.
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.
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.
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 supports the SQL2003 concatenation operator and the CONCAT function as a synonym.
MySQL supports CONCAT( ) as a synonym for the SQL2003 concatenation operator.
PostgreSQL and Oracle support the SQL2003 double vertical bar concatenation operator.
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.
/* 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.
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 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 does not support TRANSLATE or CONVERT.
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).
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.
LOWER(string) UPPER(string)
LOWER converts string into a lowercase string. UPPER is the uppercase counterpart of LOWER.
These platforms support the SQL2003 UPPER and LOWER scalar functions, as well as the synonyms UCASE and LCASE.
These platforms support the SQL2003 UPPER and LOWER scalar functions, as in the following 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.
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.
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 supports the ANSI standard for OVERLAY.
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.
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.
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.
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.
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.
SUBSTRING(extraction_string [FROM starting_position] [FOR length])
PostgreSQL largely supports the ANSI standard, except that it does not accept the COLLATE clause.
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.
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.
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 provides the functions LTRIM and RTRIM to trim off leading spaces or trailing spaces, respectively.
These platforms support the SQL2003 syntax of TRIM.
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.
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 ''''