ORACLE single-row function and multi-row function 5: Conversion Function example

Source: Internet
Author: User
1. implicit conversion rules for data types: strings can be converted to numbers and dates. The number must be legal and the date must be matched in a regular fashion. The following two statements have the same execution result: BYS @ bys1selectename, empnofromempwhereempno7788; BYS @ bys1selectename, empnofromempwhereempno7788; ENAMEEMPNO ----------

1. implicit conversion rules for data types: strings can be converted to numbers and dates. The number must be legal and the date must be matched in a regular fashion. The execution results of the following two statements are the same: BYS @ bys1select ename, empno from emp where empno = '000000'; BYS @ bys1select ename, empno from emp where empno = 7788; ename empno ----------

1. implicit conversion rules for data types:A string can be converted into numbers and dates. The number must be legal, and the date must be in the same format.
The execution results of the following two statements are the same:
BYS @ bys1> select ename, empno from emp where empno = '20140901 ';
BYS @ bys1> select ename, empno from emp where empno = 7788;
ENAME EMPNO
--------------------
SCOTT 1, 7788
Numbers and dates can also be converted into strings when being assigned values, but cannot be converted during expressions. As follows:
BYS @ bys1> select ename, empno from emp where ename = '201312 ';
No rows selected
BYS @ bys1> select ename, empno from emp where ename = 123;
Select ename, empno from emp where ename = 123
*
ERROR at line 1:
ORA-01722: invalid number.
Examples of implicit conversion of TO_DATE: http://blog.csdn.net/q947817003/article/details/13015993
######################################## ######################################## ######
2. The most common conversion functions are TO_CHAR, TO_DATE, and TO_NUMBERTO_CHAR, which convert the given date and number into characters. Convert the date type to the character type: Add fm to remove the leading zero or space, and use double quotation marks to add characters.
Select to_char (sysdate, 'yyyy/mm/dd hh24: mi: ss'), to_char (sysdate, 'fmyyyy "year"/mm/dd hh24: mi: ss ') from dual;
TO_CHAR (SYSDATE, 'yyyy/MM/DDHH2 TO_CHAR (SYSDATE, 'fmyyyy "year"/MM
------------------------------------------------------------
2013 20:10:07 11/2
To_char convert numeric type
9 indicates the number of widths. If the width is insufficient, it is displayed as #####; 0 indicates that 0 is forcibly displayed. It can be placed at the end or at the top, but it will not change your result; G is a kilobytes of characters, and D is a decimal point. You can add the currency symbol $ before the number, which is forcibly displayed.
Select to_char (456789) as a1, to_char (456789, '000000') as a2, to_char (00999,999.0000, '$456789') as a3, to_char (999,999.00, '$999G999D00 ') as a4, to_char (456789, '$99,999.00') as a5 from dual;
A1 A2 A3 A4 A5
--------------------------------------------------------
456789 00456,789.0000 $456,789.00 $456,789.00 ###########

Use the to_char function to convert a decimal number to a hexadecimal number.

The hexadecimal expression in the database is described according to the string, so to_char function is used to convert the decimal number to the hexadecimal number.

BYS @ bys1> select to_char (10, 'xxx'), to_char (42, 'xxx') from dual;
TO_C
--------

A 2a

TO_NUMBER converts the given characters to numbers.

SQL> select to_number ('20140901') year from dual;

YEAR
---------
1999
Use the to_number function to convert the hexadecimal number to the decimal number.
As shown in the following figure, the hexadecimal value of A is 10.2A, which is 2*16 + A = 42. Note xxx. If the number of conversions is large, write A few more to avoid errors due to insufficient digits.
BYS @ bys1> select to_number ('A', 'xxx'), to_number ('2a ', 'xxx') from dual;
TO_NUMBER ('A', 'xxx') TO_NUMBER ('2a ', 'xxx ')
-----------------------------------------
10 42

TO_DATE (string, 'format') converts a string to a date in ORACLE. Note that the date format and language are sensitive.

More in http://blog.csdn.net/q947817003/article/details/13015993

BYS @ bys1> select to_date ('2017/25 19:40:31 ', 'yyyy-mm-dd hh24: mi: ss') from dual;
TO_DATE ('2014/1/25
-------------------

19:40:31

The following example references the lecture notes:

Yy represents the year. The Century is always the same as the current century of the speaker.
RR is more flexible. It divides the century into the first and second centuries. If you are in the first half century and describe 0-49, then it is the same as the current century. 50-99 is described as the last century. If you are in the second half of the century and describe 0-49, it is the next century. 50-99 is the current century. It can be seen that the RR design was completely designed for our thinking habits between 1990 and 2010. When our time is around 2050, it is very awkward to use.

Select to_char (sysdate, 'yyyy') "current", to_char (to_date ('98 ', 'yy'), 'yyyy') "yy98 ", to_char (to_date ('08', 'yy'), 'yyyy') "yy08", to_char (to_date ('98 ', 'rr'), 'yyyy ') "rr98", to_char (to_date ('08 ', 'rr'), 'yyyy') "rr08" from dual;
Current yy98 yy08 rr98 rr08
--------------------
2013 2098 2008 1998 2008

######################################## ######################################## ######################################## ##################

Iii. Other conversion functions-some do not understand the experiment yet. 1. GREATEST returns the maximum value in a group of expressions, that is, compare the character encoding size.

. LEAST returns the minimum value in a group of expressions.
SQL> select greatest ('A', 'AB', 'ac') from dual;

GR
--
AC
SQL> select greatest ('white', 'lil', 'zhang') from dual;
GREATEST ('white', 'lil', 'zhang ')
------------------------
White
SQL> select least ('white', 'lil', 'zhang') from dual;
LEAST ('white', 'lil', 'zhang ')
---------------------
Zhang

2. CHARTOROWID converts the character data type to the ROWID type

ROWIDTOCHAR converts the ROWID data type to the character type
BYS @ bys1> select rowid, rowidtochar (rowid), chartorowid (rowidtochar (rowid), ename from emp where rownum <2;
Rowid rowidtochar (ROWID) CHARTOROWID (ROWIDT ENAME
----------------------------------------------------------------
AAAST0AAEAAAALDAAA AAAST0AAEAAAALDAAA AAAST0AAEAAAALDAAA SMITH

3. CONVERT (c, dset, sset)

Convert the source string sset from one language character set to another destination dset Character Set
SQL> select convert ('strutz', 'we8hp ', 'f7dec') "conversion" from dual;
Conver
------
Strutz

4. DUMP (s, fmt, start, length)

The DUMP function returns a VARCHAR2 value in the internal numeric format specified by fmt.
SQL> select global_name, dump (global_name, 1017,8, 5) dump_string from global_name;
GLOBAL_NAME DUMP_STRING
--------------------------------------------------------------------------------
ORACLE. WORLD Typ = 1 Len = 12 CharacterSet = ZHS16GBK: W, O, R, L, D

5. BFILENAME (dir, file)
Specify an external binary file
SQL> insert into file_tb1 values(bfilename('lob_dir1', 'image1.gif '));

6. CONVERT ('x', 'desc', 'source ')
Convert the source of the x field or variable to desc

7. EMPTY_BLOB () and EMPTY_CLOB ()
Both functions are used to initialize fields of the big data type.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.