Conversion Functions - Oracle to Aurora MySQL Migration Playbook
Oracle UsageMySQL Usage
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Conversion Functions

Feature compatibility Amazon SCT / Amazon DMS automation level Amazon SCT action code index Key differences

Four star feature compatibility

Four star automation level

N/A

MySQL doesn’t support all functions. These unsupported functions require manual creation.

Oracle Usage

All databases have their own conversion methods for transforming data between types and performing data manipulation. This section addresses the conversion functions TO_CHAR and TO_NUMBER.

TO_CHAR

TO_CHAR can convert many types of data (mostly number, date, and string) to string. There are many format combinations. Some examples include:

TO_CHAR calls with strings Results

to_char('0972')

0972

to_char('0972','9999')

972

to_char('0972','$9999.99')

$972.00

to_char('0972','$0009999.99')

$972.00

to_char('0972.48','$9999.999')

$972.480
TO_CHAR calls with numbers Results

to_char(0972)

972

to_char(0972,'9999')

972

to_char(0972,'$9999.99')

$972.00

to_char(0972,'$0009999.99')

$0000972.00

to_char(0972.48,'$9999.999')

$972.480
TO_CHAR calls with date Results Description

to_char(sysdate,'YYYY')

2013

Year

to_char(sysdate,'YY')

13

Last two digits of the year

to_char(sysdate,'YEAR')

TWENTY THIRTEEN

Year in words

to_char(sysdate,'SYYYY')

2017

S prefixed (-) sign for BC

to_char(sysdate,'Y,YYY')

2017

Year with comma

to_char(sysdate,'MONTH')

SEPTEMBER

Complete month

to_char(sysdate,'MON')

SEP

Three-letter month format

to_char(sysdate,'MM')

9

Month of the year

to_char(sysdate,'W')

4

Week of the current month

to_char(sysdate,'WW')

36

Week of the year (1 - 53)

to_char(sysdate,'DAY')

SATURDAY

Name of the day

to_char(sysdate,'DD')

30

Day in number format

to_char(sysdate,'D')

7

Day of the week (1 - 7)

to_char(sysdate,'DDD')

273

Day of the year (1 - 366)

to_char(sysdate,'DY')

SAT

Short form of the day

to_char(sysdate,'HH')

9

Hour (1 - 12)

to_char(sysdate,'HH12')

9

Hour in 12 hours format

to_char(sysdate,'HH24')

21

Hour in 24 hours format

to_char(sysdate,'MI')

15

Minute (0 - 59)

to_char(sysdate,'SS')

24

Second (0 - 59)

to_char(sysdate,'SSSSS')

79100

Seconds after midnight (0 - 86399)

to_char(sysdate,'PM')

PM

AM or PM

to_char(sysdate,'AM')

PM

AM or PM

to_char(sysdate,'DL')

Saturday, February 23, 2017

Date in long format

to_char(sysdate,'Q')

3

Quarter of the Year (1 - 4)

TO_NUMBER

TO_NUMBER converts one of the following to number data types: CHAR, VARCHAR2, NCHAR, NVARCHAR2, BINARY_FLOAT, or BINARY_DOUBLE. When converting one of the first four types, you can use the format parameter for the returned number.

The format parameter specifies one of the following options:

Example data to convert Format parameter Results

-1234567890

9999999999S

'1234567890-'

0

99.99

' .00'

0.1

99.99

' .10'

-0.2

99.99

' -.20'

0

9999

' 0'

1

9999

' 1'

0

B9999

' '

1

B9999

' 1'

123.456

999.999

' 123.456'

123.456

FM999.009

'123.456'

123.456

9.9EEEE

' 1.2E+02'

1.00E+123

9.9EEEE

' 1.0E+123'

123.456

FM9.9EEEE

'1.2E+02'

123.45

FM999.009

'123.45'

123

FM999.009

'123.00'

123.45

L999.99

' $123.45'

123.45

FML999.99

'$123.45'

1234567890

9999999999S

'1234567890+'

Examples

The following example converts a string to a number.

select to_number('99999') from dual;

TO_NUMBER('99999')
99999

For more information, see Functions in the Oracle documentation.

MySQL Usage

For more information, see Single-Row and Aggregate Functions.