Oracle Common conversion Functions ____ static functions

Author: The source of Xiaoxiang: http://www.cnblogs.com/kerrycode/

------------------------------------------type conversion function----------------------------------------------
--Automatic type conversion, ORACLE can automatically according to the specific situation of the following conversion:
* String to Numeric
* String to date
* Value to String
* Date to String

EX:

--* string to numeric value
SELECT ' 3.14159 ' + from DUAL;

SELECT ' 3.1T ' + from DUAL; --Error: Invalid number, that is, the string must be converted to a numeric type to operate

--* Values to Strings
SELECT ' 100 ' | | 124 from DUAL;

--1:to_char (DATE, ' FORMAT ')
Converts the corresponding data to a string type

SELECT to_char (sysdate, ' Yyyy-mm-dd HH24:MI:SS ') from DUAL;

SELECT To_char (SAL) from SCOTT. EMP;

SELECT to_char (122323.45, ' $99999999.99 ') from DUAL;

Y or yy or yyy year's last, two-bit or three-bit
SELECT to_char (sysdate, ' YYY ') from DUAL;
SELECT to_char (sysdate, ' YY ') from DUAL;

Syear or year Syear adds a minus sign to the B.C.
SELECT to_char (sysdate, ' syear ') from DUAL; --twenty Eleven

Q quarter, January-March for the first quarter
SELECT to_char (sysdate, ' Q ') from DUAL; --2 indicates the second quarter

MM number of months
SELECT to_char (sysdate, ' MM ') from DUAL; --04 says April

Roman representation of the RM month
SELECT to_char (sysdate, ' RM ') from DUAL; --iv says April

Month month names in 9 character lengths
SELECT to_char (sysdate, ' MONTH ') from DUAL; --April

WW year of the first few weeks
SELECT to_char (sysdate, ' WW ') from DUAL; ---24 for week 24th June 13, 2002

W Week of this month
SELECT to_char (sysdate, ' W ') from DUAL; --Week 4th of April 26, 2011

DDD The first days of the year. January 1 is 001, February 1 is 032
SELECT to_char (sysdate, ' DDD ') from DUAL;

DD Day of the month
SELECT to_char (sysdate, ' DD ') from DUAL;

D Days in the week
SELECT to_char (sysdate, ' D ') from DUAL;

DY The first days of the week abbreviation
SELECT to_char (sysdate, ' DY ') from DUAL;

HH or HH12 12 number of hours in the system
SELECT to_char (sysdate, ' HH ') from DUAL;

HH24 24-hour system
SELECT to_char (sysdate, ' HH24 ') from DUAL;

MI minutes (0~59)
SELECT to_char (sysdate, ' MI ') from DUAL;
Note the MM format should not be used for minutes (MI should be used in minutes). MM is the format for the month, and it works for minutes, but the result is wrong.

SS seconds (0~59)
SELECT to_char (sysdate, ' SS ') from DUAL;

--2:to_date (STRING, ' FORMAT ')
Converts a string to a date in Oracle

SELECT to_date (' 2011-03-24 ', ' yyyy/mm/dd ') from DUAL; --After the format is still 2011-3-24, it is very puzzling to find out their own data to solve this problem

SELECT to_date (' 2011/03/24 ', ' yyyy-mm-dd ') from DUAL;

--3:to_number
Converts the given character to a number

SELECT to_number (' 2008 ') as year from DUAL;

--4:runc (for number)
Intercepts a number according to the specified precision
The TRUNC function returns the processed value, which works very much like the round function, except that the function does not round the specified decimal number before or after the appropriate rounding selection.

Its specific syntax format is as follows

TRUNC (Number[,decimals])

which

Number to be intercepted

Decimals indicates the number of digits that need to be retained after the decimal point. Optional, ignore it to intercept all the decimal parts

SELECT TRUNC (314.14159234,-2) as Firstnumber, TRUNC (314.14159234,2) from DUAL;

--5:chartorowid
Converts a rowid char or VARCHAR2 value containing an external format to an internal binary format. The parameter string must be a string of 18 characters that contains the outer format of the ROWID.
The external formats in Oracle7 and Oracle8 are different. Chartorowid is the inverse function of Rowidtochar.

SELECT ROWID, Chartorowid (' Adcddfadgedgjgfryj '), ename from SCOTT. EMP;

--6:rowidtochar
Converting a ROWID data type to a character type
Converting the numeric rowid of the ROWID type to its outer 18-character string indicates that there are some different places between Oracle7 and Oracle8. Rowidtochar and Chartorowid are two opposite functions.

SELECT ROWID, Rowidtochar (ROWID), ename from SCOTT. Emp

--7:convert (C,dset,sset)
Converts the source string sset from one language character set to another dset character set

SELECT CONVERT ('? Êíó? A B C D E ', ' us7ascii ', ' we8iso8859p1 ') from DUAL;

--8:hextoraw
Converts a hexadecimal-composed string to Raw

SELECT Hextoraw (' 324 ') from DUAL;

--9:rawtohex
Converts the raw class numeric rawvalue to a corresponding hexadecimal-represented string. Each byte in the RawValue is converted to a two-byte string. Rawtohex and Hextoraw are two opposite functions.

SELECT Rawtohex (' one ') from DUAL;

--10:to_multi_byte
Converts a single-byte character in a string to a multibyte character
Converts the specified character to full angle and returns a char type string
SELECT to_multi_byte (' abc abc China ') from DUAL;

SELECT to_multi_byte (' AFDB ') from DUAL;

--11:dump (S,fmt,start,length)
The DUMP function returns the value of a VARCHAR2 type in the internal number format specified by FMT
Dump is a very powerful function that is quite useful for people who have a deep understanding of Oracle storage. So for those of us who are just applications, we don't know where to apply them. Only use here, do not do in-depth analysis of its functions.

As shown above, dump has a lot of parameters. The essence is to return the VARCHAR2 value of the internal representation of exp for the specified length in the specified form. FMT contains 4 kinds of formats: 8| | 10| | 16| | 17, respectively, 8, 10, 16 and given, the default is 10-in-system.
The start parameter represents the starting position, length as the number of strings separated.
For example: SELECT DUMP (' ABCDEFG ', 17,2,4) from DUAL;

--12:empty_blob ()
Both functions are functions that are used to initialize large data type fields

--13:empty_clob ()
-----------------------------------------------------------------------------------------------------------