196Part II: SQL and SQL*PlusFunction Name TO_YMINTERVAL TRANSLATE UNISTR TABLE 10-1.Definition pps

The use of two of these functions, TO_CHAR and TO_DATE, has already been demonstrated

in Chapter 9. TO_CHAR transforms a date into a character string (in the format you request).

TO_CHAR can convert not just DATEs but also NUMBERs into character strings. TO_DATE is

also a transformation function. It takes either a character string or a number and converts it into

the DATE datatype. It then can be used in date arithmetic to calculate MONTHS_BETWEEN,

NEXT_DAY, and other date functions.

Elementary Conversion Functions

Although there are many conversion functions listed in Table 10-1, the most commonly used are

three whose purpose is to convert one datatype into another:

■ TO_CHAR transforms a DATE or NUMBER into a character string.

■ TO_DATE transforms a NUMBER, CHAR, or VARCHAR2 into a DATE.

■ TO_NUMBER transforms a CHAR or VARCHAR2 into a NUMBER.

Why are these transformations important? TO_DATE is obviously necessary to accomplish

date arithmetic. TO_CHAR allows you to manipulate a number as if it were a string, using string

functions. TO_NUMBER allows you to use a string that happens to contain only numbers as if

it were a number; by using it, you can add, subtract, multiply, divide, and so on.

This means that if you stored a nine-digit ZIP code as a number, you could transform it into a

string, and then use SUBSTR and concatenation to add a dash (such as when printing addresses

on envelopes):

select SUBSTR(TO_CHAR(948033515),1,5)||'-'||

SUBSTR(TO_CHAR(948033515),6) AS Zip

from DUAL;

ZIP

-----------------------------------------

94803-3515

Here, the TO_CHAR function transforms the pure number 948033515 (notice that it has no

single quotation marks around it, as a CHAR or VARCHAR2 string must) into a character string.

196 Part II: SQL and SQL*Plus

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 10

Blind Folio 10:196

Function Name Definition

TO_YMINTERVAL Converts a character string of CHAR, VARCHAR2, NCHAR, or

NVARCHAR2 datatype to an INTERVAL YEAR TO MONTH type.

TRANSLATE TRANSLATEs characters in a string into different characters.

UNISTR Converts a string into Unicode in the database Unicode character set.

TABLE 10-1. Transformation Functions (continued)

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:11:44 PM

Color profile: Generic CMYK printer profile

Composite Default screen

SUBSTR then clips out positions 1 to 5 of this “string,” producing 94803. A dash is concatenated

on the right end of this string, and then another TO_CHAR creates another “string,” which

another SUBSTR clips out from position 6 to the end. The second string, 3515, is concatenated

after the dash. The whole rebuilt string is relabeled Zip, and Oracle displays it: 94803-3515. This

TO_CHAR function lets you use string manipulation functions on numbers (and dates) as if they

were actually strings. Handy? Yes. But watch this:

select SUBSTR(948033515,1,5)||'-'||

SUBSTR(948033515,6) AS Zip

from DUAL;

ZIP

-----------------------------------------

94803-3515

This shouldn’t work, because 948033515 is a NUMBER, not a character string. Yet the string

function SUBSTR clearly worked anyway. Would it work with an actual NUMBER database

column? Here’s a table with Zip as a NUMBER:

describe ADDRESS

Name Null? Type

------------------------------- -------- ----

LASTNAME VARCHAR2(25)

FIRSTNAME VARCHAR2(25)

STREET VARCHAR2(50)

CITY VARCHAR2(25)

STATE CHAR(2)

ZIP NUMBER

PHONE VARCHAR2(12)

EXT VARCHAR2(5)

Select just the ZIP code for all the Marys in the table:

select SUBSTR(Zip,1,5)||'-'||

SUBSTR(Zip,6) AS Zip

from ADDRESS

where FirstName = 'MARY';

ZIP

-----------------------------------------

94941-4302

60126-2460

SUBSTR works here just as well as it does with strings, even though Zip is a NUMBER column

from the ADDRESS table. Will other string functions also work?

select Zip, RTRIM(Zip,20)

from ADDRESS

Chapter 10: Conversion and Transformation Functions 197

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 10

Blind Folio 10:197

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:11:45 PM

Color profile: Generic CMYK printer profile

Composite Default screen

where FirstName = 'MARY';

ZIP RTRIM(ZIP,20)

---------- ----------------------------------------

949414302 9494143

601262460 60126246

The column on the left demonstrates that Zip is a NUMBER; it is even right-justified, as

numbers are by default. But the RTRIM column is left-justified, just as strings are, and it has

removed zeros and twos from the right side of the ZIP codes. Something else is peculiar here.

Recall from Chapter 7 the format for RTRIM, shown here:

RTRIM(string [,'set'])

The set to be removed from the string is enclosed within single quotation marks, yet in this

example,

RTRIM(Zip,20)

there are no quotation marks. So what is going on?

Automatic Conversion of Datatypes

Oracle is automatically converting these numbers, both the Zip and the 20, into strings, almost as

if they both had TO_CHAR functions in front of them. In fact, with a few clear exceptions, Oracle

will automatically transform any datatype into any other datatype, based on the function that is

going to affect it. If it’s a string function, Oracle will convert a NUMBER or a DATE instantly into

a string, and the string function will work. If it’s a DATE function and the column or literal is a

string in the format DD-MON-YY, Oracle will convert it into a DATE. If the function is arithmetic

and the column or literal is a character string, Oracle will convert it into a NUMBER and do the

calculation.

Will this always work? No. To have Oracle automatically convert one datatype into another,

the first datatype must already “look” like the datatype it is being converted to. The basic

guidelines are as follows:

■ Any NUMBER or DATE can be converted to a character string. Any string function can

be used on a NUMBER or DATE column. Literal NUMBERs do not have to be enclosed

in single quotation marks when used in a string function; literal DATEs do.

■ A CHAR or VARCHAR2 value will be converted to a NUMBER if it contains only

NUMBERs, a decimal point, or a minus sign on the left.

■ A CHAR or VARCHAR2 value will be converted to a DATE only if it is in the default

date format (usually DD-MON-YY). This is true for all functions except GREATEST and

LEAST, which will treat the value as a string, and is true for BETWEEN only if the column

to the left after the word BETWEEN is a DATE. Otherwise, TO_DATE must be used, with

proper format.

198 Part II: SQL and SQL*Plus

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 10

Blind Folio 10:198

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:11:45 PM

Color profile: Generic CMYK printer profile

Composite Default screen

These guidelines may be confusing, so favor the use of TO_DATE and other conversion

functions to make sure the values are treated properly. The following examples should help to

clarify the guidelines. The following are the effects of several randomly chosen string functions

on NUMBERs and DATEs:

select INITCAP(LOWER(SysDate)) from DUAL;

INITCAP(LOWER(SYSDATE))

-----------------------

26-Mar-02

Note that the INITCAP function put the first letter of “mar” into uppercase even though “mar”

was buried in the middle of the string “26-mar-02.” This is a feature of INITCAP that is not confined

to dates, although it is illustrated here for the first time. It works because the following works:

select INITCAP('this-is_a.test,of:punctuation;for+initcap')

from DUAL;

INITCAP('THIS-IS_A.TEST,OF:PUNCTUATION;FO

-----------------------------------------

This-Is_A.Test,Of:Punctuation;For+Initcap

INITCAP puts the first letter of every word into uppercase. It determines the beginning of

a word based on its being preceded by any character other than a letter. You can also cut and

paste dates using string functions, just as if they were strings:

select SUBSTR(SysDate,4,3) from DUAL;

SUB

---

MAR

Here, a DATE is left-padded with 9s for a total length of 20:

select LPAD(SysDate,20,'9') from DUAL;

LPAD(SYSDATE,20,'9')

--------------------

9999999999926-MAR-02

LPAD, or any other string function, also can be used on NUMBERs, whether literal (as shown

here) or as a column:

select LPAD(9,20,0) from DUAL;

LPAD(9,20,0)

--------------------

00000000000000000009

Chapter 10: Conversion and Transformation Functions 199

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 10

Blind Folio 10:199

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:11:45 PM

Color profile: Generic CMYK printer profile

Composite Default screen

These examples show how string functions treat both NUMBERs and DATEs as if they were

character strings. The result of the function (what you see displayed) is itself a character string.

In this next example, a string (note the single quotation marks) is treated as a NUMBER by the

number function FLOOR:

select FLOOR('-323.78') from DUAL;

FLOOR('-323.78')

----------------

-324

Here, two literal character strings are converted to DATEs for the DATE function

MONTHS_BETWEEN. This works only because the literal strings are in the default date format

DD-MON-YY:

select MONTHS_BETWEEN('16-MAY-02','01-NOV-02') from DUAL;

MONTHS_BETWEEN('16-MAY-02','01-NOV-02')

---------------------------------------

-5.516129

One of the guidelines says that a DATE will not be converted to a NUMBER. Yet, here is an

example of addition and subtraction with a DATE. Does this violate the guideline?

select SysDate, SysDate + 1, SysDate - 1 from DUAL;

SYSDATE SYSDATE+1 SYSDATE-1

--------- --------- ---------

26-MAR-02 27-MAR-02 25-MAR-02

It does not, because the addition and subtraction is date arithmetic, not regular arithmetic.

Date arithmetic (covered in Chapter 9) works only with addition and subtraction, and only with

DATEs. Most functions will automatically convert a character string in default date format into

a DATE. An exception is this attempt at date addition with a literal:

select '26-MAR-02' + 1 from DUAL;

ERROR: ORA-01722: invalid number

Date arithmetic, even with actual DATE datatypes, works only with addition and subtraction.

Any other arithmetic function attempted with a date will fail. Dates are not converted to numbers,

as this attempt to divide a date by 2 illustrates:

select SysDate / 2 from DUAL;

*

ERROR at line 1: ORA-00932: inconsistent data types

Finally, a NUMBER will never be automatically converted to a DATE, because a pure number

cannot be in the default format for a DATE, which is DD-MON-YY:

200 Part II: SQL and SQL*Plus

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 10

Blind Folio 10:200

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:11:46 PM

Color profile: Generic CMYK printer profile

Composite Default screen

select NEXT_DAY(032602,'FRIDAY') from DUAL;

*

ERROR at line 1: ORA-00932: inconsistent data types

To use a NUMBER in a date function, TO_DATE is required.

A Warning About Automatic Conversion

The issue of whether it is a good practice to allow SQL to do automatic conversion of datatypes

has arguments on either side. On one hand, this practice considerably simplifies and reduces

the functions necessary to make a select statement work. On the other hand, if your assumption

about what will be in the column is wrong (for example, you assume a particular character

column will always have a number in it, meaning you can use it in a calculation), then, at some

point, a query will stop working, Oracle will produce an error, and time will have to be spent

trying to find the problem. Further, another person reading your select statement may be

confused by what appear to be inappropriate functions at work on characters or numbers. Using

TO_NUMBER makes it clear that a numeric value is always expected even if the column uses

the VARCHAR2 datatype.

A simple rule of thumb might be that it is best to use functions where the risk is low, such as

string manipulation functions on numbers, rather than arithmetic functions on strings. For your

benefit and that of others using your work, always put a note near the select statement signaling

the use of automatic type conversion.

Specialized Conversion Functions

As shown in Table 10-1, Oracle includes several specialized conversion functions. If you expect

to use SQLPLUS and Oracle simply to produce reports, you probably won’t ever need any of

these functions. On the other hand, if you plan to use SQLPLUS to update the database, expect

to build Oracle applications; or if you are using National Language Support, this information

will eventually prove valuable. The functions can be found, by name, in the Alphabetical

Reference section of this book.

NOTE

The CAST function is used with nested tables and varying arrays; see

Chapter 31 for details. The DECODE function is covered in Chapter 17.

The conversion functions generally take a single value as input and return a single converted

value as output. For example, the BIN_TO_NUM function converts binary values to decimal

numeric values. Its input value is a list of the digits of a binary value, separated by commas and

treated as a single input string:

select BIN_TO_NUM(1,1,0,1) from DUAL;

BIN_TO_NUM(1,1,0,1)

-------------------

13

Chapter 10: Conversion and Transformation Functions 201

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 10

Blind Folio 10:201

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:11:46 PM

Color profile: Generic CMYK printer profile

Composite Default screen

202 Part II: SQL and SQL*Plus

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 10

Blind Folio 10:202

select BIN_TO_NUM(1,1,1,0) from DUAL;

BIN_TO_NUM(1,1,1,0)

-------------------

14

Transformation Functions

Although in one sense any function that changes its object could be called a transformation

function, there are two unusual functions that you can use in many interesting ways to control

your output based on your input, instead of simply transforming it. These functions are

TRANSLATE and DECODE.

TRANSLATE

TRANSLATE is a simple function that does an orderly character-by-character substitution in a

string. This is the format for TRANSLATE:

TRANSLATE(string,if,then)

TRANSLATE looks at each character in string, and then checks if to see whether that character

is there. If it is, it notes the position in if where it found the character, and then looks at the same

position in then. TRANSLATE substitutes whichever character it finds there for the character in

string. Normally, the function is written on a single line, like this:

select TRANSLATE(7671234,234567890,'BCDEFGHIJ')

from DUAL;

TRANSLA

-------

GFG1BCD

But it might be easier to understand if simply broken onto two lines (SQLPLUS doesn’t care,

of course):

select TRANSLATE(7671234,234567890,

'BCDEFGHIJ')

from DUAL;

TRANSLA

-------

GFG1BCD

When TRANSLATE sees a 7 in the string, it looks for a 7 in the if, and translates it to the

character in the same position in the then (an uppercase G). If the character is not in the if, it

is not translated (observe what TRANSLATE did with the 1).

TRANSLATE is technically a string function, but, as you can see, it will do automatic data

conversion and work with a mix of strings and numbers. The following is an example of a very

simple code cipher, where every letter in the alphabet is shifted one position. Many years ago,

spies used such character-substitution methods to encode messages before sending them. The

recipient simply reversed the process. Do you remember the smooth-talking computer, HAL, in

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:11:47 PM

Color profile: Generic CMYK printer profile

Composite Default screen

Chapter 10: Conversion and Transformation Functions 203

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 10

Blind Folio 10:203

the movie 2001: A Space Odyssey? If you TRANSLATE HAL’s name with a one-character shift in the

alphabet, you get this:

select TRANSLATE('HAL','ABCDEFGHIJKLMNOPQRSTUVWXYZ',

'BCDEFGHIJKLMNOPQRSTUVWXYZA') AS Who

from DUAL;

WHO

---

IBM

DECODE

If TRANSLATE is a character-by-character substitution, DECODE can be considered a

value-by-value substitution. For every value it sees in a field, DECODE checks for a match in

a series of if/then tests. DECODE is an incredibly powerful function, with a broad range of

areas where it can be useful. Chapter 17 is devoted entirely to advanced use of DECODE.

This is the format for DECODE:

DECODE(value,if1,then1,if2,then2,if3,then3,. . . ,else)

Only three if/then combinations are illustrated here, but there is no practical limit. To see

how this function works, recall the NEWSPAPER table you saw in earlier chapters:

select * from NEWSPAPER;

FEATURE S PAGE

--------------- - -----

National News A 1

Sports D 1

Editorials A 12

Business E 1

Weather C 2

Television B 7

Births F 7

Classified F 8

Modern Life B 1

Comics C 4

Movies B 4

Bridge B 2

Obituaries F 6

Doctor Is In F 6

Suppose you want to change the name of a couple of the regular features. DECODE will

check each Feature value, row by row. If the value it finds is ‘Sports’, then it will substitute

‘Games People Play’; if it finds ‘Movies’, then it will substitute ‘Entertainment’; if it finds anything

else in the value, then it will use the value of Feature.

In the next example, the page number is decoded. If the page number is 1, then the words

‘Front Page’ are substituted. If the page number is anything else, the words ‘Turn to’ are

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:11:47 PM

Color profile: Generic CMYK printer profile

Composite Default screen

concatenated with the page number. This illustrates that else can be a function, a literal, or

another column.

select Feature, Section,

DECODE(Page,'1','Front Page','Turn to '||Page)

from NEWSPAPER;

FEATURE S DECODE(PAGE,'1','FRONTPAGE','TURNTO'||PAGE)

--------------- - ------------------------------------------------

National News A Front Page

Sports D Front Page

Editorials A Turn to 12

Business E Front Page

Weather C Turn to 2

Television B Turn to 7

Births F Turn to 7

Classified F Turn to 8

Modern Life B Front Page

Comics C Turn to 4

Movies B Turn to 4

Bridge B Turn to 2

Obituaries F Turn to 6

Doctor Is In F Turn to 6

There are some restrictions on datatypes in the list of ifs and thens, which will be covered in

Chapter 17.

Review

Most functions in Oracle, although they are intended for a specific datatype, such as CHAR,

VARCHAR2, NUMBER, and DATE, will actually work with other datatypes as well. They do

this by performing an automatic type conversion. With a few logical exceptions, and the hope

of future compatibility, they will do this as long as the data to be converted “looks” like the

datatype required by the function.

Character functions will convert any NUMBER or DATE. NUMBER functions will convert a

CHAR or VARCHAR2 if it contains the digits 0 through 9, a decimal point, or a minus sign on the

left. NUMBER functions will not convert DATEs. DATE functions will convert character strings if

they are in the format DD-MON-YY. They will not convert NUMBERs.

Two functions, TRANSLATE and DECODE, will fundamentally change the data they act on.

TRANSLATE will do a character substitution according to any pattern you specify, and DECODE

will do a value substitution for any pattern you specify.

204 Part II: SQL and SQL*Plus

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 10

Blind Folio 10:204

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:11:47 PM

Color profile: Generic CMYK printer profile

Composite Default screen

ORACLE Series TIGHT / Oracle9i: The Complete Reference / Loney, Koch / 222521-1 / Chapter 11

Blind Folio 11:205

CHAPTER

11

Grouping Things

Together

P:\010Comp\Oracle8\521-1\CD\Ventura\book.vp

Friday, July 19, 2002 4:11:48 PM

Color profile: Generic CMYK printer profile

Composite Default screen