Single-row conversion functions of ORACLE Functions

Required bytes 1 ASCIISTR

Format:ASCIISTR (C)

Note:Converts a string C to an ASCII string, meaning that the ASCII characters in C remain unchanged, but non-ASCII characters are returned in ASCII format.

Example:

SQL> select asciistr ('AB? CDE database ') a from dual;

A

---------------------

AB? CDE \ 6570 \ 636E \ 5E93

2 BIN_TO_NUM

Format:BIN_TO_NUM (n1, n2, n3 ...)

Note:Convert each binary consisting of n1, n2, n3 to decimal

Example:

SQL> SELECT BIN_TO_NUM (1, 1, 1) A FROM DUAL;

A

----------

15

3 CAST

Format:CAST (cas t)

Note:Convert expression C to data type T. T can be an internal data type or a programmer-defined data type. Only if the data can be converted

Example:

SQL> SELECT CAST ('000000' AS NUMBER) A FROM DUAL;

A

----------

12345

4 DECODE

Format:DECODE (x, if 1, then 1, if 2 then 2,..., else z)

Note:Compare x with the if value. if it is equal to if n, then n is returned. Otherwise, else z is returned.

Example:

SQL> select decode (1, 1, 0,-1) as a, DECODE (1, 0,-1) as B FROM DUAL;

A B

--------------------

0-1

5 NUMTODSINTERVAL

Format:NUMTODSINTERVAL (N, C)

Note:Converts the number n to the data of the intervalday to second type specified by C. The value of C is DAY, HOUR, MINUTE, and SECOND. C is case-insensitive.

Example:

SQL> SELECT SYSDATE + NUMTODSINTERVAL (10, 'day') A, SYSDATE + NUMTODSINTERVAL (10, 'hour') BFROM DUAL;

A B

-----------------------------------

2014/7/3:20:11

6 NUMTOYMINTERVAL

Format:NUMTODSINTERVAL (N, C)

Note:Converts the number n to the data of the intervalday to second type specified by C. The value of C is YEAR and MONTH. C is case-insensitive.

Example:

SQL> select sysdate + NUMTOYMINTERVAL (10, 'Year') A, SYSDATE + NUMTOYMINTERVAL (10, 'month') B FROM DUAL;

A B

----------------------------------------

2024/7/17:24:18

7 TO_CHAR

Format:TO_CHAR (X [, fmt])

Note:Convert X to a string in fmt Format. X can be a date, number, or string. fmt is a string that specifies the format used for conversion.

Example:

SQL> SELECT TO_CHAR (SYSDATE, 'yyyy-MM-DD ') A, TO_CHAR (111) B FROM DUAL;

A B

-------------

2014-07-02111

8 TO_CLOB

Format:TO_CLOB (x)

Note:Convert the NCLOB value or other string x in the LOB column to the CLOB value.

Example:

SQL> SELECT TO_CLOB ('aaa') A FROM DUAL;

A

--------------------------------------------------------------------------------

AAAA

9 TO_DATE

Format:TO_DATE (C [, fmt])

Note:Convert string C that meets the specific DATE format specified by fmt to data of the DATE type

Example:

SQL> SELECT TO_DATE ('2014-07-02 ', 'yyyy-MM-DD') A, TO_DATE ('2014-07 ', 'yyyy-mm') B FROMDUAL;

A B

----------------------

2014/7/2 2014/7/1

10 TO_DSINTERVAL

Format:TO_DSINTERVAL (C)

Note:Convert string C in a specific format TO data of the intervalday to second type

Example:

SQL> SELECT SYSDATE + TO_DSINTERVAL ('2014: 00: 00') A FROM DUAL;

A

-----------------

2014/7/

11 TO_LOB

Format:TO_LOB (X)

Note:Convert the value X in the LONG column to the value of the LOB column.

Example:

 

12 TO_NUMBER

Format:TO_NUMBER (C [, fmt])

Note:Convert string C that meets the specific numeric format specified by fmt into numeric data

Example:

SQL> SELECT TO_NUMBER ('123') A FROM DUAL;

A

----------

101

13 TO_TIMESTAMP

Format:TO_TIMESTAMP (C)

Note:Converts string C to a TIMESTAMP data type.

Example:

SQL> SELECT TO_TIMESTAMP ('2017-07-am. 1010', 'yyyy-MM-dd HH24: MI: SS. ff') A FROM DUAL;

A

--------------------------------------------------------------------------------

12-7-14 02.10.123200000 pm

14 TO_TIMESTAMP_TZ

Format:TO_TIMESTAMP_TZ (C [, fmt])

Note:Converts string C that meets the specific date format specified by fmt to timestampwith timezone data with the time zone

Example:

SQL> SELECT TO_TIMESTAMP_TZ ('1970-07-12. 1010', 'yyyy-MM-dd HH24: MI: SS. ff') A FROM DUAL;

A

--------------------------------------------------------------------------------

12-7-14 02.10.123200000 PM +

15 TO_YMINTERVAL

Format:TO_YMINTERVAL (C)

Note:Convert string C in a specific format TO data of the intervalyear to month type

Example:

SQL> SELECT SYSDATE + TO_YMINTERVAL ('01-03') AFROM DUAL;

A

-----------------

2015/10/29: 26: 45

16 TRANSLATE

Format:TRANSLATE (X, if y, then z)

Note:View each character in X and check whether the character exists in if y. If it exists, locate the character position in if y, replace the character with the character at the same position in then z, and finally return all replace X

Example:

SQL> SELECT TRANSLATE ('abcdefghj', 'abcdef', '000000') A FROM dual;

A

----------

123456 ghij

17 UNISTR

Format:UNISTR (c)

Note:Returns the UNICOOE character corresponding to string c.

Example:

SQL> SELECT UNISTR ('01-03') A FROM DUAL;

A

-----------------

01-03