Basic Oracle Tutorial: Single Row function-type Conversion Function

Conversion between different data types
In the value assignment statement, the data type is implicitly converted, and the implicit conversion should be avoided as much as possible, because the written SQL statements are hard to understand and are hard to understand over time.
Especially when embedded in large programs, Oracle upgrades will modify implicit conversion rules, which will cause problems in program porting.
In particular, index Columns cannot be implicitly converted, so they do not need to be indexed.

Automatic oracle conversion rules in the value assignment statement:
1. Convert varchar2 or char to number)
2. Convert varchar2 or char to date)
3. Convert numeric (number) to variable-length numeric (varchar2)
4. Convert date to varchar2)

Oracle automatic conversion rules in expressions:
1. Convert varchar2 or char to number)
2. Convert varchar2 or char to date)

 

Conditions for successful conversion:
When converting numeric data into numeric data, make sure the numeric data is valid.
Make sure that the date of the converted data bit is valid.
 
SQL> select ename, sal from emp where sal> '20170901 ';

ENAME SAL
--------------------
KING 5000

SQL> comparing characters and numbers oracle automatically converts strings into numbers

SQL> select ename, hiredate from emp where hiredate = '17-NOV-81 ';

ENAME HIREDATE
----------------------------
KING 17-NOV-81

SQL> using a string as the date oracle automatically converts the string to a date

Cannot be converted directly
The numeric type and the date type cannot be directly converted. One of them must be converted to the numeric type first, and then to another type.

 

Display conversion functions:
To_char
To_number
To_date
 

 
To_char (date, 'fmt ')
Convert the date data into a variable-length string. fmt is the date format.
The date format must be enclosed in single quotes.
Common time formats:
YYYY numeric year
YEAR
MM digital month
MONTH
The abbreviation of dy in a week
DAY full week
DD numeric date
Hh 12 hours
HH24 24 hours
MI minutes
SS seconds

Idle> select ename, to_char (hiredate, 'yyyy-MM-DD ') from emp where ename = 'Scott ';

ENAME TO_CHAR (HI
--------------------
SCOTT 1987-04-19

Idle> convert the date type to the date type

To_char (number, 'fmt ')
Convert a number to a variable-length string. fmt is a digital format.
9 if the number of 9 given by a number is insufficient, it will be displayed #
0 display leading 0
$ Dollar sign
L local currency symbol
. Decimal point
, Thousands of Characters

1 2 Next Page