Conversion Functions

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.

Note

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:

  1. Arithmetic expressions and functions can be used on numeric values.

  2. Numeric values are right-justified, whereas character string data types are left- justified in 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, while some have conversion functions such as this. In either case, consult your implementation documentation for specific syntax and rules for conversions.

Note

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 Hour 11 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.

Tip

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.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset