Conversion Functions
Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences |
---|---|---|---|
|
|
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 |
---|---|
|
0972 |
|
972 |
|
$972.00 |
|
$972.00 |
|
$972.480 |
TO_CHAR calls with numbers |
Results |
---|---|
|
972 |
|
972 |
|
$972.00 |
|
$0000972.00 |
|
$972.480 |
TO_CHAR calls with date |
Results | Description |
---|---|---|
|
2013 |
Year |
|
13 |
Last two digits of the year |
|
TWENTY THIRTEEN |
Year in words |
|
2017 |
S prefixed (-) sign for BC |
|
2017 |
Year with comma |
|
SEPTEMBER |
Complete month |
|
SEP |
Three-letter month format |
|
9 |
Month of the year |
|
4 |
Week of the current month |
|
36 |
Week of the year (1 - 53) |
|
SATURDAY |
Name of the day |
|
30 |
Day in number format |
|
7 |
Day of the week (1 - 7) |
|
273 |
Day of the year (1 - 366) |
|
SAT |
Short form of the day |
|
9 |
Hour (1 - 12) |
|
9 |
Hour in 12 hours format |
|
21 |
Hour in 24 hours format |
|
15 |
Minute (0 - 59) |
|
24 |
Second (0 - 59) |
|
79100 |
Seconds after midnight (0 - 86399) |
|
PM |
AM or PM |
|
PM |
AM or PM |
|
Saturday, February 23, 2017 |
Date in long format |
|
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
MySQL Usage
For more information, see Single-Row and Aggregate Functions.