Conversion Functions | Sams Teach Yourself SQL in 24 Hours (5th Edition) (Sams Teach Yourself -- Hours)


Team-Fly

	Sams Teach Yourself SQL in 24 Hours, Third Edition By Ronald R. Plew, Ryan K. Stephens
	Table of Contents

	Hour 11. Restructuring the Appearance of Data

Conversion functions are used to convert a data type into another data type. For example, there may be times when you want to convert character data into numeric data. You may have data that is normally stored in character format, but occasionally you want to convert the character format to numeric for the purpose of making calculations. Mathematical functions and computations are not allowed on data that is represented in character format.

The following are general types of data conversions:

Character to numeric
Numeric to character
Character to date
Date to character

The first two types of conversions are discussed in this hour. The remaining conversion types are discussed during Hour 12, "Understanding Dates and Times," after date and time storage is discussed in more detail.

Some implementations may implicitly convert data types when necessary.

Converting Character Strings to Numbers

There are two things you should notice regarding the differences between numeric data types and character string data types:

Arithmetic expressions and functions can be used on numeric values.
Numeric values are right-justified, whereas character string data types are left-justified in the output results.

When a character string is converted to a numeric value, the value takes on the two attributes just mentioned.

Some implementations may not have functions to convert character strings to numbers, whereas some will have such conversion functions. In either case, consult your implementation documentation for specific syntax and rules for conversions.

Characters in a character string being converted to a number must typically be 0 through 9. The addition symbol, minus symbol, and period can also be used to represent positive numbers, negative numbers, and decimals. For example, the string STEVE cannot be converted to a number, whereas an individual's Social Security number could be stored as a character string, but could easily be converted to a numeric value via use of a conversion function.

The following is an example of a numeric conversion using an Oracle conversion function:

    SELECT EMP_ID, TO_NUMBER(EMP_ID)   FROM EMPLOYEE_TBL;   EMP_ID            TO_NUMBER(EMP_ID) ---------         ----------------- 311549902                 311549902 442346889                 442346889 213764555                 213764555 313782439                 313782439 220984332                 220984332 443679012                 443679012 6 rows selected.

The employee identification is right-justified following the conversion.

The justification of data is the simplest way to identify a column's data type.

Converting Numbers to Strings

The conversion of numeric values to character strings is precisely the opposite of converting characters to numbers.

The following is an example of converting a numeric value to a character string using a Transact-SQL conversion function for Microsoft SQL Server:

     SELECT PAY = PAY_RATE, NEW_PAY = STR(PAY_RATE)   FROM EMPLOYEE_PAY_TBL   WHERE PAY_RATE IS NOT NULL;   PAY NEW_PAY ---------- -------       17.5 17.5      14.75 14.75      18.25 18.25       12.8 12.8         11 11         15 15 6 rows affected.

The following is the same example using an Oracle conversion function:

    SELECT PAY_RATE, TO_CHAR(PAY_RATE)   FROM EMPLOYEE_PAY_TBL   WHERE PAY_RATE IS NOT NULL;   PAY_RATE TO_CHAR(PAY_RATE) ---------- -----------------       17.5 17.5      14.75 14.75      18.25 18.25       12.8 12.8         11 11         15 15 6 rows selected.


Team-Fly

Top