196 Part II: SQL and SQL*Plus Function Name TO_YMINTERVAL TRANSLATE UNISTR TABLE 10-1. Definition Converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to an INTERVAL YEAR TO MONTH type. TRANSLATEs characters in a string into different characters. Converts a string into Unicode in the database Unicode character set. Transformation Functions (continued) 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. | 196 Part II SQL and SQL Plus 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 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 t 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
