CEIL

By definition, function CEIL returns the least integer of the argument passed; that means that the function rounds the values up. The syntax and usage are identical for all three databases.

The argument could be a number or a column value; the output is shown underneath the query:

SELECT CEILING (prod_price_n)
			 ceil_price, prod_price_n FROM product; ceil_price prod_price_n ------------
			 ------------ 19 18.24 34 33.28 27 26.82

The FLOOR function acts in a very similar fashion, rounding down. The syntax is identical across all three databases:

SELECT FLOOR (prod_price_n)
			 floor_price, prod_price_n FROM product; floor_price prod_price_n ------------
			 ------------ 18 18.24 33 33.28 26 26.82

ROUND

One might wonder how that is different from the TRUNC function. This function rounds a number to a specific length or precision, and works almost identically in all three RDBMS implementations.

In the following example, all the values of the PROD_PRICE_N column from the table PRODUCT are rounded to 1 decimal digit of precision:

SELECT ROUND(prod_price_n,1)
			 round_price, prod_price_n FROM product; round_price prod_price_n ------------
			 ------------ 18.20 18.24 33.30 33.28 26.80 26.82

Since our query requested precision 2, the numbers were rounded up and down — depending on the number itself: 33.28 was rounded to 33.30, and 18.24 was rounded to 18.20.

The second integer argument could be negative to round numbers on the left of the decimal point, the integral part of the number:

SELECT ROUND (prod_price_n,1)
			 round_price_right, ROUND (prod_price_n,-1) round_price_left, prod_price_n FROM
			 product; round_price_right round_price_left prod_price_n -----------------
			 ---------------- ------------ 18.30 20.00 18.32 34.10 30.00 34.09 26.90 30.00
			 26.92 16.00 20.00 15.98

Here, specifying -1 as the second argument of the - function, we are getting the result of a rounded value for the digits on the left side of the decimal point.

TRUNC

Function TRUNC returns its argument truncated to the number of decimal places specified with the second argument. The example shown applies to Oracle and IBM DB2 UDB; the MS SQL Server uses the ROUND function to truncate:

SELECT TRUNC(prod_price_n, 1)
			 trunc_price, prod_price_n FROM product; trunc_price product_price_n
			 ------------ ------------ 18.2 18.24 33.2 33.28 26.8 26.82

There is a special case where function TRUNC is used to truncate dates in Oracle. It produces the midnight value for the date argument, that is, it truncates off all the hours, minutes, and seconds:

SELECT SYSDATE,
			 TRUNC(SYSDATE) truncated FROM dual; SYSDATE TRUNCATED ---------------------
			 ---------------------- 9/22/2003 10:53:36 AM 9/22/2003 00:00:00
			 AM

RAND

The RAND function is used to generate some random numbers at runtime. The syntax and usage are almost identical for DB2 UDB and the MS SQL Server 2000. (There is no analog function in Oracle, although it could be emulated through use of PL/SQL packages.) It accepts an optional seed argument (integer) and would produce a random float number in the range between 1 and 0 (inclusive).

The MS SQL Server 2000 syntax is:

SELECT RAND(1) random_number
			 random_number --------------------- 0.71359199321292355

The DB2 UDB Syntax produces analogous results in somewhat different format:

SELECT RAND(5) FROM
			 sysibm.sysdummy1 random_number ---------------------
			 +1.64799951170385E-03

There are some nuances to RAND function usage: called several times within a session with the same seed value, it will produce exactly the same output. To get different pseudo-random numbers you need to specify different seed values, or use different sessions.

Getting Random Numbers in a Different Range

What do you do when random numbers of a range different from 0 to 1 are required? In this case you could multiply the output of the - function by the range factor, and then TRUNCATE or ROUND the result. Here is an example of producing a set of pseudo-random values in the range of 0 to 10000 in MS SQL Server 2000 syntax:

SELECT ROUND((RAND(15)*
				10000),0) from_zero_to_10000 from_zero_to_10000 --------------------------
				7139.0

Encapsulating this functionality in a custom-made function would be the most rational solution. All three vendors provide the ability to create user-defined functions in their RDBMS software.

SIGN

The SIGN function works exactly the same way in all three implementations. It is used to determine the sign of the numeric expression argument: if the number is positive, then the function returns 1; if the number is negative (the result will be -1, if the argument is zero), then 0 is returned. In our example all 1s were returned since the price is expressed in positive numbers:

SELECT SIGN (prod_price_n)
			 sign_price, prod_price_n FROM product; sign_price prod_price_n ------------
			 ------------ 118.24 1 33.28 1 26.82

You could use just a literal number in place of the value from a table's column. Since all implementations use SELECT as the keyword to execute a function, you need something to select from.

CONCAT

The CONCAT function simply concatenates two strings. This function could be replaced with an operator — + for SQL Server, and || for Oracle and DB2 UDB.

Here is a concatenation example in Oracle 9i syntax:

SELECT CONCAT( '$',
			 TO_CHAR(prod_price_n)) display_price FROM product;
			 display_price ------------------------------- $18.24 $33.28
			 $26.82

For DB2 UDB the syntax will be:

SELECT '$' ||
			 CHAR(prod_price_n) display_price FROM product; display_price -------------
			 $00000018.24 $00000033.28 $00000026.82

Here is an equivalent MS SQL Server 2000 syntax:

SELECT '$' +
			 CONVERT(varchar, prod_price_n) display_price FROM product;

CHARINDEX, INSTR, LOCATE, and POSSTR

SQL is a language specifically designed to handle information. As such it has a rich toolset for manipulating strings and characters. The three functions INSTR, LOCATE, and CHARINDEX are used to determine the position of a specific character (or combination of characters) within a string; based on this information, you can slice and dice text information in a number of ways.

For example, to locate the position of the end of the first word, use a blank space to separate the words in the description (assuming that every value in the column PROD_DESCRIPTION_S would have at least one blank space).

In MS SQL Server 2000 syntax, the blank space is indicated as ' '. You can use ASCII code 32 to specify blank space — CHAR (32). The following two statements are equivalent and produce identical results:

SELECT CHARINDEX(' ',
			 prod_description_s, 1) FROM product; SELECT CHARINDEX(CHAR(32),
			 prod_description_s, 1) char_position FROM product; char_position -------------
			 7 6 6 6 5 8 4 4 6 8 (10 row(s) affected)

Oracle's INSTR function syntax is slightly different — Oracle allows you to specify occurrence of the string within a string — first, second, and so on. Most of the arguments are optional.

This query, executed in Oracle 9i SQL*Plus, looks for a second occurrence of the blank space within the string:

SELECT
			 INSTR(PROD_DESCRIPTION_S, CHAR(32),1,2) char_position FROM product;

The following query executed in IBM DB2 UDB produces a result identical to that shown for MS SQL Server: it finds the first occurrence of a blank space in the string (both starting char and occurrence are optional arguments, if omitted defaults to 1):

SELECT LOCATE('
			 ',PROD_DESCRIPTION_S) char_position FROM product;

To use IBM DB2 UDB function POSSTR you would need to change order of arguments:

SELECT
			 POSSTR(PROD_DESCRIPTION_S,' ') char_position FROM product;

The results of the both queries will be identical, and match that produced for MS SQL Server.

SUBSTR and SUBSTRING

The SUBSTR (SUBSTRING on MS SQL Server) function returns part of an argument string, which is designated by starting position and required character length. Here is a query example using the function to return only the first three characters of the column prod_description_s value:

SELECT
			 SUBSTR(prod_description_s,1,3) FROM product;

The third argument, specifying the required length, is optional for Oracle and DB2 UDB, and is mandatory for MS SQL Server's SUBSTRING function. If the third argument is omitted, the function would return all characters after the starting position in Oracle and DB2 UDB; for SQL Server to simulate this behavior, use an LEN / LENGTH function (see later in the chapter) to determine the total length of the string, or a number large enough to exceed any possible length of the string (no greater than 8000). For example, in the SQL Server this query would return all characters in the column prod_description_s, beginning from the second character:

SELECT
			 SUBSTRING(prod_description_s, 2, LEN(prod_description_s)) FROM
			 product;

Let's make our output slightly more complex. Say a user wants results to be mangled in a special way to produce an output that combines product number, product price, and product description in the format <first word of product description> <pound sign><product_number><pound sign><dollar sign>< product_price> for some company application. This could be done in a number of ways, one of which is the following query below (in MS SQL Server syntax):

SELECT
			 LEFT(prod_description_s, CHARINDEX(CHAR(32), prod_description_s, 1)-1) + '#'+
			 prod_num_s + '#' + '$' + CONVERT(VARCHAR,prod_price_n) display FROM product;
			 display ------------------------- SPRUCE#990#$18.24 STEEL#1880#$33.28
			 STOOL#2871#$26.82 STOOL#3045#$15.92 HAND#4000#$11.80

In Oracle this result can be produced with this query:

SELECT
			 SUBSTR(prod_description_s,1 INSTR(CHAR(32), prod_description_s, 1,1)-1) || '#'
			 || prod_num_s || '#' || '$' || TO_CHAR(prod_price_n)display FROM product;
			 display ------------------------- SPRUCE#990#$18.24 STEEL#1880#$33.28
			 STOOL#2871#$26.82 STOOL#3045#$15.92 HAND#4000#$11.80

IBM DB2 UDB uses the POSSTR (or LOCATE) function in place of Oracle's INSTR function, and function CHR to produce a blank character from ASCII code 32 and converts number to string with the CHAR function (instead of Oracle's TO_CHAR):

SELECT
			 SUBSTR(prod_description_s,1 POSSTR(prod_description_s, CHR(32))-1) || '#' ||
			 prod_num_s || '#' || '$' || CHAR(prod_price_n) display FROM product; display
			 ------------------------------------- SPRUCE#990#$18.24 STEEL#1880#$33.28
			 STOOL#2871#$26.82 STOOL#3045#$15.92 HAND#4000#$11.80

While this query might look a bit scary, there is nothing mysterious about it. The CHARINDEX / INSTR / POSSTR functions find the position of blank space — CHAR(32) — and subtract 1 from that number so a blank space is not included in the final result. We use the position value as input for the function LEFT, specifying from which position it should return the characters (column PROD_ DESCRIPTION_S) to the left (Oracle and DB2 UDB use the SUBSTR function; MS SQL Server uses SUBSTRING — which, in a sense, is a more generic version of its own LEFT/RIGHT functions); the rest is a simple concatenation of characters, discussed previously.

LENGTH

The function LENGTH (LEN for MS SQL Server) returns a number of characters (not a number of bytes! — see Chapter 3 for more details) in the argument. If an argument is not of a character type, it will be implicitly converted into string, and its length will be returned. Oracle also provides a number of variations of the function:

SELECT
			 LENGTH(prod_description_s) length_of_string FROM product;

To return a number of bytes in the expression, use LENGTHB and DATALENGTH for Oracle and SQL Server, respectively. IBM and Oracle also overload their LENGTH functions — the same function could return length in characters or bytes, depending upon the data type of the argument. These functions do not convert argument into string, but rather give the internal storage size for the data type as it is defined in the RDBMS. For example, the query

SELECT LENGTHB(SYSDATE) FROM
			 dual;

returns 9 for Oracle (internal storage for current system date). The MS SQL Server equivalent expression

SELECT
			 DATALENGTH(GETDATE())

reports that 8 bytes are used to store system date. IBM UDB2, for example, uses 4 bytes for date storage and 10 bytes for timestamp:

SELECT LENGTH(CURRENT DATE)
			 date_length, LENGTH(CURENT TIMESTAMP) timestamp_length FROM sysibm.sysdummy1
			 date_length timestamp_length ------------ ----------------- 4
			 10

LOWER and UPPER

The functions LOWER and UPPER are the rare examples of functions mandated by the SQL92/99 standard and implemented across all three RDBMS without modifications. These functions are simple and intuitive to use. They convert string expressions into lowercase or uppercase, respectively:

SELECT
			 UPPER(prod_description_s) upper_case, LOWER(prod_description_s) lower_case FROM
			 product; upper_case lower_case ------------------------ -----------------------
			 SPRUCE LUMBER 30X40X50 spruce lumber 30x40x50 STEEL NAILS
			 6'' steel nails 6'' STOOL CAPS 5'' stool caps 5''

IBM DB2 UDB also contains additional versions of the functions LCASE and UCASE, most probably due to being in business for a long time — you certainly accumulate some baggage after being on the market for over 30 years.

TO_CHAR, CHAR, and STR

These functions fall into the broader range of Conversion Functions, which are discussed later in this chapter. They are used to convert one data type into character data type, for example, a number into a string or date/time data into a string (this might be needed to produce a report that accepts character data only). In addition, these functions allow you to format output when used to convert, for instance, date and time data types.

The usage examples are shown in the CONCAT function above. Since this function accepts only strings, it is necessary to convert all the numbers into strings to avoid an error in IBM DB2 UDB; Oracle 9i implicitly converts all concatenated values into strings.

Microsoft function STR differs from Oracle's TO_CHAR and IBM's CHAR in that it accepts only numeric input — no date/time or even string. It has optional arguments that specify total length of the result (including decimal point) as well as number decimal places. For example, the following query converts a float number 123.35 (two decimal places) into a string (MS SQL Server 2000 syntax):

SELECT STR(123.35) result
			 result ---------- 123

Since both optional arguments — total length (default 10) and precision (default 0) — were omitted, the result is truncation. The following query takes into account that the expected result should be 7 characters long and have 3 decimal places (specifying 2 decimal places — less than is present — would result in rounding the final output):

SELECT STR(123.235,7,3)
			 result result ------- 123.235

REPLACE

The REPLACE function found in IBM DB2 UDB, Oracle, and MS SQL Server returns a string (CHAR data type); every occurrence of an expression2 is replaced with expression3, for example:

SELECT
			 REPLACE('aabbaabbaa','aa','bb') FROM dual; result ------------
			 bbbbbbbbbb

This query returns a 'bbbbbbbbbb' string since every occurrence of 'aa' is replaced with 'bb'. To run this query against SQL Server, just remove the FROM clause.

REPLICATE and REPEAT

To replicate a character or sequence of characters you may use the REPLICATE and REPEAT functions. These functions pertain to IBM DB2 UDB and the Microsoft SQL Server; in Oracle similar functionality is achieved with a combination of functions LPAD/RP