|
|
Table of Contents |
|
4.4 ANSI SQL Scalar FunctionsThe 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.
4.4.1 Built-in Scalar FunctionsSQL2003 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.
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. MySQLMySQL 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. OracleOracle supports USER and SYSDATE as a synonym of CURRENT_TIMESTAMP. PostgreSQLPostgreSQL supports all the SQL2003 built-in scalar functions except SESSION_USER. SQL ServerSQL Server supports all the built-in scalar functions. ExamplesThe 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 FunctionsANSI SQL2003 provides a function named CASE that can be used to create IF-THEN flow-control logic within queries or update statements. The CAST function is for converting between datatypes and is also in the ANSI standard. All of the databases covered by this book provide ANSI standard support for both the CASE and CAST functions.
The CASE function provides IF-THEN-ELSE functionality within a SELECT or UPDATE statement. It evaluates a list of conditions and returns one value out of several possible values. CASE has two usages: simple and searched. Simple CASE expressions compares one value, the input_value, with a list of other values and return a result associated with the first matching value. Searched CASE expressions allow the analysis of several logical conditions and return a result associated with the first one that is true. All vendors provide the ANSI SQL2003 syntax for CASE. SQL2003 Syntax and Description-- Simple comparison operation CASE input_valueWHEN when_condition THEN resulting_value[...n] [ELSE else_result_value] END -- Boolean searched operation CASE WHEN Boolean_condition THEN resulting_value[...n] [ELSE else_result_expression] END In the simple CASE function, the input_value is evaluated against each WHEN clause. The resulting_value is returned for the first TRUE instance of input_value = when_condition. If no when_condition evaluates as TRUE, the else_result_value is returned. If no else_result_value is specified, then NULL is returned. In the more elaborate Boolean searched operation, the structure is essentially the same as the simple comparison operation except that each WHEN clause has its own Boolean comparison operation. In either usage, multiple WHEN clauses are used, though only one ELSE clause is necessary. ExamplesHere 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 NULLThe update has now completed three separate UPDATE operations in a single statement.
The CAST command explicitly converts an expression of one datatype to another. All vendors provide the ANSI SQL2003 syntax for CAST. SQL2003 Syntax and DescriptionCAST(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.
ExamplesThis 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 FunctionsThe 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.
All platforms have standard SQL2003 support for the ABS function. SQL2003 SyntaxABS( expression ) ABS returns the absolute value of the number in expression. ExampleThe following shows how to use the ABS function: /* SQL2003 */ SELECT ABS(-1) FROM NUMBERS1
All platforms stray from the ANSI standard in their support for the scalar functions for determining the length of expressions. While the platform support is nonstandard, the equivalent functionality exists under different names. SQL2003 SyntaxThe 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 ) DB2DB2 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. MySQLMySQL supports CHAR_LENGTH and the SQL2003 synonym CHARACTER_LENGTH( ). OracleThe 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. PostgreSQLPostgreSQL supports CHAR_LENGTH and the SQL2003 synonym CHARACTER_LENGTH( ). SQL ServerSQL Server provides the LEN function. ExampleThe following example, shown for different databases, determines the length of a string and a value retrieved from a column: /* On MySQL and PostgreSQL */
SELECT CHAR_LENGTH('hello');SELECT OCTET_LENGTH(book_title) FROM titles;
/* On Microsoft SQL Server */
SELECT DATALENGTH(title) FROM titlesWHERE type = 'popular_comp'GO
/* On Oracle or DB2 */
SELECT LENGTH('HORATIO') "Length of characters"FROM dual;
The CEIL function returns the smallest integer greater than an input value that you specify. SQL2003 SyntaxSQL2003 supports the following two forms of the function: CEIL( expression ) CEILING ( expression ) DB2DB2 supports both variations of the SQL2003 syntax. MySQLMySQL supports only CEILING. OracleOracle supports only CEIL. PostgreSQLPostgreSQL has no support for either CEIL or CEILING. SQL ServerSQL Server supports only CEILING. ExamplesWhen 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)
------------
101Remember, though, that with negative numbers, rounding "up" results in a lower absolute value: SELECT CEIL(-100.1) FROM dual;
FLOOR(-100.1)
-------------
-100Use FLOOR to get behavior opposite to that of CEIL.
The EXP function returns the value of the mathematical constant e (approximately 2.718281) raised to the power of a specified number. SQL2003 SyntaxAll platforms support the SQL2003 syntax: EXP( expression ) ExampleThe following example uses EXP to return an approximation of e: SELECT EXP(1) FROM dual;
EXP(1)
----------
2.71828183
Use LN to go in the opposite direction.
The EXTRACT function is supported by Oracle, PostgreSQL, and MySQL. The other platforms support a separate command to accomplish the same functionality. SQL2003 SyntaxEXTRACT( 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. DB2DB2 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.
MySQLMySQL'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.
OracleOracle supports the SQL2003 syntax with the dateparts listed in Table 4-6.
PostgreSQLPostgreSQL supports the SQL2003 syntax with a few extra dateparts. PostgreSQL supports the dateparts listed in Table 4-7.
SQL ServerSQL 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.
ExampleThis example extracts dateparts from several date time values: /* On MySQL */ SELECT EXTRACT(YEAR FROM "2013-07-02");2013 SELECT EXTRACT(YEAR_MONTH FROM "2013-07-02 01:02:03");201307 SELECT EXTRACT(DAY_MINUTE FROM "2013-07-02 01:02:03");20102 /* On PostgreSQL */ SELECT EXTRACT(HOUR FROM TIMESTAMP '2001-02-16 20:38:40'); 20
The FLOOR function returns the largest integer less than an input value that you specify. SQL2003 SyntaxAll platforms support the SQL2003 syntax: FLOOR( expression ) ExamplesWhen 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)
------------
100Remember, 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)
-------------
-101Use CEIL to get behavior opposite to FLOOR.
The LN function returns the natural logarithm of a number, which is the power to which you would need to raise the mathematical constant e (approximately 2.718281) in order to get the number in question as the result. SQL2003 SyntaxLN( expression ) DB2, Oracle, PostgreSQLDB2, 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 ServerMySQL and SQL Server call their natural logarithm function LOG: LOG( expression ) ExampleThe following, Oracle-based example shows the natural logarithm of a number closely approximating the mathematical constant known as e: SELECT LN(2.718281) FROM dual; LN(2.718281) ------------ .999999695 Use the EXP function to go in the other direction.
The MOD function returns the remainder of a dividend divided by a divider. All platforms support the SQL2003 syntax for the MOD function. SQL2003 SyntaxMOD( 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. ExampleThe following shows how to use the MOD function from within a SELECT statement: SELECT MOD(12, 5) FROM NUMBERS 2
The POSITION function returns an integer that indicates the starting position of a string within the search string. SQL2003 SyntaxPOSITION( 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. DB2DB2's equivalent function is POSSTR. MySQLMySQL supports POSITION as defined in SQL2003. OracleOracle's equivalent function is called INSTR. PostgreSQLPostgreSQL supports POSITION as defined in SQL2003. SQL ServerSQL Server has both CHARINDEX and PATINDEX. CHARINDEX and PATINDEX are very similar, except that PATINDEX allows the use of wildcard characters in the search criteria. Examples/* DB2 */
SELECT POSSTR('bar', 'foobar');
4
/* On MySQL */
SELECT LOCATE('bar', 'foobar');4
/* On MySQL and PostgreSQL */
SELECT POSITION('fu' IN 'snafhu');0
/* On Microsoft SQL Server */
SELECT CHARINDEX( 'de', 'abcdefg' )GO4
SELECT PATINDEX( '%fg', 'abcdefg' )GO6
Use POWER to raise a number to a specific value. SQL2003 SyntaxPOWER( 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 ServerThese vendors all support the SQL2003 syntax. MySqlMySQL supports the same functionality, but requires the use of the keyword POW: POW( base, exponent ) ExampleRaising a positive number to an exponent is straightforward: SELECT POWER(10,3) FROM dual;
POWER(10,3)
-----------
1000Anything raised to the 0th power evaluates to 1: SELECT POWER(0,0) FROM dual;
POWER(0,0)
----------
1Negative exponents move the decimal point to the left: SELECT POWER(10,-3) FROM dual;
POWER(10,-3)
------------
.001
The SQRT function returns the square root of a number. SQL2003 SyntaxAll platforms support the SQL2003 syntax: SQRT( expression ) ExampleSELECT SQRT(100) FROM dual;
SQRT(100)
----------
10
The WIDTH_BUCKET function assigns values to buckets in an equiwidth histogram. SQL2003 SyntaxIn 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. ExamplesThe 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 3The 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 4Pay 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 OperatorsBasic 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.
SQL2003 defines a concatenation operator (||), which joins two distinct strings into one string value. DB2DB2 supports the SQL2003 concatenation operator and the CONCAT function as a synonym. MySQLMySQL supports CONCAT( ) as a synonym for the SQL2003 concatenation operator. Oracle and PostgreSQLPostgreSQL and Oracle support the SQL2003 double vertical bar concatenation operator. SQL ServerSQL Server uses the plus sign (+) as a synonym for the SQL2003 concatenation operator. SQL Server has the system setting CONCAT_NULL_YIELDS_NULL, which can be set to alter the behavior when NULL values are used in the concatenation of string values. Examples/* SQL2003 Syntax */
'string1' || 'string2' || 'string3'
'string1string2string3'
/* On MySQL */
CONCAT('string1', 'string2')
'string1string2'If any of the concatenation values are NULL, the entire returned string is NULL. Also, if a numeric value is concatenated, it is implicitly converted to a character string. SELECT CONCAT('My ', 'bologna ', 'has ', 'a ', 'first ', 'name...');
'My bologna has a first name...'
SELECT CONCAT('My ', NULL, 'has ', 'first ', 'name...');NULL
The CONVERT function alters the representation of a character string within its character set and collation. For example, CONVERT might be used to alter the number of bits per character. TRANSLATE alters the character set of a string value from one base-character set to another. Thus, TRANSLATE might be used to translate a value from the English character set to a Kanji (Japanese) or Cyrillic (Russian) character set. The translation must already exist, either by default or by having been created using the CREATE TRANSLATION command. SQL2003 SyntaxCONVERT (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. DB2DB2 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!'MySQLMySQL does not support TRANSLATE or CONVERT. OracleOracle 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).
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;GrossPostgreSQLPostgreSQL 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 ServerSQL Server does not support TRANSLATE. SQL Server's implementation of the CONVERT function does not follow the SQL2003 specification. Instead, it is functionally equivalent to the CAST function. CONVERT (data_type[(length) | (precision,scale)], expression[,style]) The style clause is used to define the format of a date conversion. Refer to the SQL Server documentation for more information. Following is an example: SELECT title, CONVERT(char(7), ytd_sales) FROM titles ORDER BY title GO
The functions LOWER and UPPER allow the case of a string to be altered quickly and easily, so that all the characters are lower- or uppercase, respectively. These functions are supported in all the database implementations covered in this book. The different database platforms also support a variety of other text formatting functions that are specific to their implementation. SQL2003 SyntaxLOWER(string) UPPER(string) LOWER converts string into a lowercase string. UPPER is the uppercase counterpart of LOWER. DB2 and MySQLThese platforms support the SQL2003 UPPER and LOWER scalar functions, as well as the synonyms UCASE and LCASE. Oracle, PostgreSQL, and SQL ServerThese platforms support the SQL2003 UPPER and LOWER scalar functions, as in the following example: ExampleSELECT LOWER('You Talkin To ME?'), UPPER('you talking to me?!');
you talkin to me?, YOU TALKING TO ME?!
The OVERYLAY function embeds one string into another and returns the result. SQL2003 SyntaxOVERLAY(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 ServerThese 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. PostgreSQLPostgreSQL supports the ANSI standard for OVERLAY. ExamplesThis is an example of how to use the OVERLAY function: /* SQL2003 and PostgreSQL */
SELECT OVERLAY('DONALD DUCK' PLACING 'TRUMP' FROM 8) FROM NAMES;'DONALD TRUMP'
The SUBSTRING function allows one character string to be returned from another. SQL2003 SyntaxSUBSTRING(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. DB2SUBSTR(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. MySQLSUBSTRING(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. OracleSUBSTR(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. PostgreSQLSUBSTRING(extraction_string [FROM starting_position] [FOR length]) PostgreSQL largely supports the ANSI standard, except that it does not accept the COLLATE clause. SQL ServerSUBSTRING(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. ExamplesThese examples generally work on any one of the five database platforms profiled in this book. Only the second Oracle example, with a negative starting position, fails on the others (assuming, of course, that Oracle's SUBSTR is translated into SUBSTRING): /* On Oracle, counting from the left */
SELECT SUBSTR('ABCDEFG',3,4) FROM DUAL;'CDEF'
/* On Oracle, counting from the right */
SELECT SUBSTR('ABCDEFG',-5,4) FROM DUAL;'CDEF'
/* On MySQL */
SELECT SUBSTRING('Be vewy, vewy quiet' FROM 5);'wy, vewy quiet'
/* On PostgreSQL or SQL Server */
SELECT au_lname, SUBSTRING(au_fname, 1, 1)FROM authorsWHERE au_lname = 'Carson'
Carson C
The TRIM function removes leading characters, trailing characters, or both from a specified character string or BLOB value. This function also removes other types of characters from a specified character string. The default function is to trim the specified character from both sides of the character string. If no removal character is specified, TRIM removes spaces by default. SQL2003 SyntaxTRIM( [ [{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. DB2DB2 provides the functions LTRIM and RTRIM to trim off leading spaces or trailing spaces, respectively. MySQL, Oracle, and PostgreSQLThese platforms support the SQL2003 syntax of TRIM. Microsoft SQL ServerSQL 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. ExamplesSELECT 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 ' |
|
|
Table of Contents |
|
