Chapter 10: SQL Functions

Chapter 10: SQL Functions

Overview

SQL functions exist to make your life easier when you need to manipulate data retrieved from a table. While SQL query, which is composed of the statements, is busy retrieving some data for you, the functions used within that query are validating, converting, calculating, getting the system information, and much more.

Think of the SQL functions as tools designed to accomplish a single well-defined task, for example, calculating square root or converting lowercase letters into uppercase. You invoke a function within SQL query by name (usually a single keyword). Some functions accept arguments and some do not, but what differentiates a function from every other executable module in RDBMS is that it always returns value.

While SQL itself is not a procedural language — that is, it lacks procedural features such as flow control structures and loops — using functions allows you, to a certain extent, to alleviate problems stemming from this deficiency.

All functions could be divided into two broad categories: deterministic functions and nondeterministic functions. Deterministic functions always return the same result if you pass into the same arguments; nondeterministic functions might return different results, even if they are called with exactly the same arguments. For example function ABS, which returns the absolute value of a number passed to it as an argument, is a deterministic function — no matter how many times you call it with, say argument, -5, it will always return 5 as a result. For example, the Microsoft SQL Server function GETDATE() — when it accepts no arguments and returns only the current date and time on the RDBMS server — is an example of a nondeterministic function: each time you call it a new date and time is returned, even if the difference is one second.

Some RDBMS restrict use of the nondeterministic function in database objects such as INDEX or VIEW. For example, the MS SQL Server disallows use of such functions for indexed computed columns and indexed views; the IBM DB2 UDB does not allow nondeterministic functions in the join condition expression, and you cannot use these function in Oracle's function-based index.

Note?

Virtually every database vendor provides procedural extensions for use with their database products. Oracle has built-in PL/SQL, Microsoft uses its own dialect of Transact-SQL, and DB2 UDB uses IBM SQL (which is similar to Transact-SQL). Unlike SQL, these procedural extensions allow for creating full-fledged programs within their respective host environments. User-defined custom functions are usually created using one of their procedural languages.

The list of SQL functions available for use within a particular RDBMS implementation grows with every new release, and some vendors are allowing users to define their own custom functions to perform nonstandard tasks. In this chapter we provide only a short list of the most helpful functions and their uses. Differences between vendor-specific implementations are highlighted.

Note?

The portability problem with using functions in a query is the possibility that the query might not run properly with competitors' products. Some functions are identical in name and usage, some have only a different name, and some exist only within a particular RDBMS implementation.

Not all of these functions (some would say most of them are not) are part of the SQL standard — be it SQL89 (SQL1), SQL92 (SQL2), or even SQL99 (SQL3). In fact, all of these standards specify only a handful of functions as a requirement for conformance to a specific level (entry, intermediate, or full). The old saying that you cannot program a standard still keeps true. The list of the functions specified in the SQL2 standard is given in Table 10-1.

Table 10-1: Standard SQL2 Functions

SQL Function

Description

BIT_LENGTH (expression)

Returns the length of the expression, usually string, in bits.

CAST (value AS data type)

Converts supplied value from one data type into another compatible data type.

CHAR_LENGTH (expression)

Returns the length of the expression, usually string, in characters.

CONVERT (expression USING conversion)

Returns string converted according to the rules specified in the conversion parameter.

CURRENT_DATE

Returns current date of the system.

CURRENT_TIME (precision)

Returns current time of the system, of the specified precision.

CURRENT_TIMESTAMP (precision)

Returns current time and the current date of the system, of the specified precision.

EXTRACT (part FROM expression)

Extracts specified named part of the expression.

LOWER (expression)

Converts character string from uppercase (or mixed case) into lowercase letters.

OCTET_LENGTH (expression)

Returns the length of the expression in bytes (each byte containing 8 bits).

POSITION (char expression IN source)

Returns position of the char expression in the source.

SUBSTRING (string expression, start, length)

Returns the string part of a string expression, from the start position up to specified length.

TRANSLATE (string expression USING translation rule)

Returns string translated into another string according to specified rules.

TRIM(LEADING | TRAILING | BOTH char expression FROM string expression)

Returns string from a string expression where leading, trailing, or both char expression characters are removed.

UPPER (expression)

Converts character string from lowercase (or mixed case) into uppercase letters.

Cross-References?

For obvious reasons, it would be very beneficial to have a clear picture of what functions are available in the most popular RDBMS as well as mapping of those functions between different implementations. In Appendix D, we've attempted to provide list of all functions, their respective mapping to each other, and brief explanations for the "big three" — Oracle 9i, IBM DB2 UDB 8.1, and Microsoft SQL Server 2000.

Every vendor has its own classifications of the functions supported in its database product. IBM groups its DB2 UDB functions into column functions, scalar functions, row functions and table functions; whereas Oracle uses terms like single-row functions, aggregate functions, analytic functions, and object-reference functions; and Microsoft sports the most detailed classifications of configuration functions, cursor, date and time, mathematical functions, aggregate functions, metadata, security, string, system functions, and system statistical functions, as well as text and image functions, and rowset functions.

Note?

IBM makes a distinction between SYSIBM and SYSFUN schema functions; they differ in the way they handle data; for example, sometimes one is handling numeric input while the other handling character input. Consider it an IBM implementation of function overloading where a different task is expected of a function based on the argument data types passed into the function.

This chapter concentrates on the functions that could be used in any implementation context, leaving out many proprietary extensions. The XML-related functions are discussed in Chapter 17, security functions are in Chapter 12, and metadata functions are in Chapter 13.

All the examples, unless otherwise stated, use the ACME database. You could run them directly against this database installed in the RDBMS of your choice.

Cross-References?

Refer to Appendix F on instructions for installing ACME database, and Appendix E for how to start a command line or visual interface for the RDBMS of your choice.

Numeric functions

We grouped some functions into numeric functions because they are operating with numbers — both input and output parameters are usually numeric: INTEGER, DECIMAL, and so on. The list of most common numeric functions is given in Table 10-2.

Table 10-2: Numeric Functions

Oracle 9i

IBM DB2 UDB 8.1

MS SQL Server 2000

Description

ABS (n)

ABSs (n)

ABS (n)

Returns absolute value of a number n.

CEIL (n)

CEIL[ING] (n)

CEILING (n)

Returns smallest integer that is greater than or equal to n.

EXP (n)

EXP (n)

EXP (n)

Returns exponential value of n.

FLOOR (n)

FLOOR (n)

FLOOR (n)

Returns the largest integer less than or equal to n.

MOD.(n,m)

MOD.(n,m)

Operator %

Returns remainder of n divided by m.

POWER.(m,n)

POWER.(m,n)

POWER.(m,n)

Returns value of m raised into nth power.

N/A

RAND.(n)

RAND.(n)

Returns a random number between 0 and 1.

ROUND(n,[m])

ROUND (n,[m])

ROUND (n,m,[0])

Returns number n rounded to m decimal places. For MS SQL Server, the last argument — zero — is a default.

SIGN(n)

SIGN(n)

SIGN(n)

Returns -1, if n is a negative number, 1 if it is a positive number, and 0 if the number is zero.

TRUNC (n,[m])

TRUNC[ATE] (n,[m])

ROUND (n,m,<>0)

Returns n truncated to m decimal places. For MS SQL Server, when the last argument has a value other than zero, the result of the function is truncation.

Cross-References?

See Chapter 3 for more information on data types.

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
Tip?

The command line interface is different in all three implementations. Oracle uses SQL*Plus, Microsoft uses the OSQL utility, and IBM the command line processor. To execute an SQL command (after connection is established), type the command into the window of the utility (DOS window on the Windows platform, command prompt on UNIX/LINUX), and press Enter for DB2 UDB or type in a semicolon (;) and Enter for Oracle, or type GO and press Enter for the MS SQL Server. Each of these interfaces is customizable, and you could "teach," for instance, the MS SQL Server to accept a semicolon in lieu of GO statement.

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.

Note?

The Microsoft SQL Server's version of the ROUND function behaves somewhat differently than its equivalents in Oracle and DB2 UDB — it has a third optional argument (function) that by default is 0. When this argument is omitted or explicitly set to 0, the result is rounding — exactly as seen in the foregoing example; when the value is other than 0, the result will be truncated.

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.

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.

Note?

The DUAL table was introduced by Chuck Weiss of Oracle as an underlying object in the Oracle Data Dictionary. It was never meant to be seen by itself, but rather to be used in some complex JOIN operations (discussed in Chapter 9); it logically fits the SELECT notion as it implies that the FROM clause is to be used with each statement.

String functions

String functions are grouped together because they perform some operations specifically pertaining to strings characters — that is, manipulation of the strings: changing the letter case, changing alignment, finding ASCII codes, and so on. Usually, but not always the output of such functions is a string. Some of the most common string functions are listed in Table 10-3.

Table 10-3: String Functions

ORACLE 9i

IBM DB2 UDB 8.1

MS SQL Server 2000

Description

ASCII (string)

ASCII (string)

ASCII (string)

Returns ASCII code of the first character of a string.

CHR (number)

CHR (number)

CHAR (number) NCHAR (number)

Returns character for the ASCII code.

CONCAT (string1, string2)

CONCAT (string1, string2)

operator '+'

Returns result of concatenation of two strings.

INSTR (string, substring, start position, occurrence)

LOCATE (string1, string2, n) POSSTR (string1, string2, n)

CHARINDEX (string1,string2, n)
PATINDEX
(<pattern>, <string>)

Returns position of an occurrence of a substring within the string.
The POSSTR test is case sensitive.

SUBSTR (1,n)

LEFT (string, n)

LEFT (string, n)

Returns n number of characters starting from the left.

LENGTH (string)

LENGTH (string)

LEN (string)

Returns number of characters in a string.

LENGTHB (expression)

LENGTH (expression)

DATALENGTH (expression)

Returns number of bytes in the expression, which could be any data type.

LOWER (string)

LOWER (string)

LOWER (string)
LCASE (string)

Converts all characters in a string to lowercase.

LPAD (string1,n, string 2)

REPEAT (char expression, integer)
SPACE (integer)

REPLICATE (char expression, integer)
SPACE(integer)

Returns string1 padded from the left with string2 n times.

LTRIM (string, set)

LTRIM (string)

LTRIM (string)

Returns string with leading blank characters removed.

REPLACE (string1, string2, string3)

REPLACE (string1, string2, string3)

REPLACE (string1, string2, string3)

Replaces all occurrences of string1 within string2 with string3.

RPAD (string1,n, string2)

SPACE (integer)

SPACE (integer)

Returns string1 padded from the left with string2 n times.

LPAD/RPAD combination

REPEAT (string, n)

REPLICATE (string, n)

Returns string consisting of the argument repeated n times.

RTRIM (string, set)

RTRIM (string)

RTRIM (string)

Returns string with trailing blank characters removed.

TO_CHAR (expression)

CHAR (expression)

STR (expression)

Converts argument expression into a character string.

SUBSTR (string, n, m)

SUBSTR (string, n, m)

SUBSTRING (string, n, m)

Returns a part of a string starting from nth character for the length of m characters.

TRANSLATE (string1, string2, string3)

TRANSLATE (string1, string2, string3)
INSERT (works similar to STUFF)

STUFF (<char_ expression1>,<start>, <length>,<char_expression1>)

Replaces all occurrences of string1 within string2 translated into string3. Functions STUFF and INSERT add/replace strings within strings.

TRIM (string)

LTRIM (RTRIM (string))

LTRIM (RTRIM (string))

Trims leading or trailing spaces off the string, or both.

UPPER (string)

UPPER (string)
UCASE (string)

UPPER (string)

Converts all characters
of a string into uppercase.

Cross-References?

Refer to Appendix G for a comprehensive list of vendor-specific functions.

String functions are arguably the most widely used and the most confusing of the SQL functions. Here we are giving the examples of some we are using daily.

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.

Note?

You could use output of one function to be an input for another — this is not RDBMS- or even SQL-specific; it is a general programming concept.

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
Note?

That CHAR function converts numeric data into a fixed-length string, and the final result will be padded with zeroes from the left. The number of zeroes depends on the numeric data type converted. You could use additional formatting/conversion functions to produce results identical to these produced by Oracle or the MS SQL Server.

Here is an equivalent MS SQL Server 2000 syntax:

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

We used conversion functions CHAR, TO_CHAR, and CONVERT to convert a numeric expression into a string data type to combine two different types. Some implementations would implicitly convert compatible data types; some require explicit conversion to take place. It is usually a good idea not to rely on implicit conversions but rather explicitly convert the values. There is more on conversion later in this chapter.

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.

Note?

Optional arguments are the arguments that have some predefined default value that is assumed if the argument is missing from the list. Since the order of arguments is fixed, you must enter all the arguments prior to the one that you decided to specify; in the foregoing example, once you've specified occurrence value (fourth argument) you no longer can omit starting position (third argument).

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.

Note?

You may have noticed that we did not use the available function LEFT in DB2 UDB; this is because this function works differently from the similarly named function in the MS SQL Server. In the IBM DB2 UDB database, function LEFT returns a string consisting of the leftmost expression2 bytes in expression1.

Also, function LOCATE has a third optional argument with which to specify what character the search should start from. (POSSTR always starts at the first character.)

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
Cross-References?

For more information on data types and their internal storage, see Chapter 3.

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/