Using conversion functions

Introduction to Oracle SQL

Hadrien Lacroix

Content Developer

Data types

Data types define what type of data a column can contain.

	Numeric data	Character data	Date data
`AVG`	x
`SUM`	x
`MIN`	x	x	x
`COUNT`	x	x	x

Conversion functions convert a column from one data type to another

Conversion functions

Data type conversion
- Implicit data type conversion
- Explicit data type conversion

Implicit data type conversion

SQL automatically converts data types

SELECT 'Track length: ' || Milliseconds
FROM Track

| 'Track length: ' || Milliseconds     |
|--------------------------------------|
| Track length: 343719                 |
| Track length: 342562                 |
| ...                                  |

Explicit data type conversion

Summary diagram of conversion functions and data types

Converting to character data

Convert a number string to a character format using the TO_CHAR function:

SELECT UnitPrice, TO_CHAR(UnitPrice, '$999.99') 
FROM InvoiceLine

| UnitPrice   | TO_CHAR(UnitPrice, '$9.99') |
|-------------|-----------------------------|
| 0.99        | $0.99                       |
| 1.99        | $1.99                       |
| ...         | ...                         |

$ : Floating dollar sign
. : Decimal position
9 : Specifies numeric position. The number of 9's determine the display width
0 : Specifies leading zeros
, : Comma position in the number

Converting to character data

Convert a date string to a character format using the TO_CHAR function:

SELECT TO_CHAR(BirthDate, 'DD-MON-YYYY') 
FROM Employee

| TO_CHAR(BirthDate, 'DD-MON-YYYY') |
|-----------------------------------|
| 19-SEP-1947                       |
| ...                               |

YYYY: Four digit representation of year
YEAR: Year spelled out
MM: Two digit value of month
MONTH: Full name of month
MON: 3-letter representation of month
DY: 3-letter representation of day of week
DAY: Full name of the day
DD: Numeric day of the month

Converting to numeric data

Convert a character string to a number format using the TO_NUMBER function:

SELECT TO_NUMBER('$15,000.75', '$999,999.99')
FROM DUAL

| TO_NUMBER('$15,000.75', '$999,999.99') |
|----------------------------------------|
| 15000.75                               |

$ : Floating dollar sign
. : Decimal position
9 : Specifies numeric position. The number of 9's determine the display width
0 : Specifies leading zeros
, : Comma position in the number

Converting to date data

Convert a character string to a date format using the TO_DATE function:

SELECT TO_DATE('2016-01-31','YYYY-MM-DD')
FROM DUAL

| TO_DATE('2016-01-31','YYYY-MM-DD')     |
|----------------------------------------|
| 31-JAN-16                              |

YYYY: Four digit representation of year
YEAR: Year spelled out
MM: Two digit value of month
MONTH: Full name of month
MON: 3-letter representation of month
DY: 3-letter representation of day of week
DAY: Full name of the day
DD: Numeric day of the month

Which data type conversion should you use?

Caution sign

Always use explicit conversion
- Easier to read and maintain
- Code will continue to work

Let's practice!

Introduction to Oracle SQL