Oracle inversion inversion function and single-line conversion function

Oracle inversion inversion function

Oracle provides a reverse inversion function reverse, but this function cannot be grouped upside down, this article provides a function that can be grouped upside down, as follows:

CREATE OR REPLACE FUNCTION reverse_f (p_str VARCHAR2, P_delimiter varchar2:= ")
Return VARCHAR2 is
V_return VARCHAR2 (4000);
VP_STR VARCHAR2 (4000): = P_STR;
V_IDX number;
Vp_len Number: = Length (P_delimiter);
BEGIN
if (P_delimiter is null) then
Select reverse (P_STR) into the v_return from dual;
Else
Loop
V_idx: = InStr (Vp_str, P_delimiter);
if (v_idx = 0) Then
V_return: = Vp_str | | V_return;
Exit
elsif (v_idx = 1) Then
V_return: = substr (Vp_str, 1, vp_len) | | V_return;
VP_STR: = substr (Vp_str, Vp_len + 1);
Else
V_return: = substr (Vp_str, 1, v_idx-1) | | V_return;
VP_STR: = substr (Vp_str, V_IDX);
End If;
End Loop;
End If;
return v_return;
End Reverse_f;


Single-line conversion functions of Oracle Functions

1 Asciistr

Format: Asciistr (C)

Description: Converts string C to an ASCII string, leaving the ASCII characters in C unchanged, but non-ASCII characters are returned in ASCII notation

Example:

Sql>select asciistr (' AB? CDE database ') A from DUAL;

A

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

Ab? Cde6570636e5e93

2 Bin_to_num

Format: Bin_to_num (N1,n2,n3 ...)

Description: Each digit is converted to decimal by the binary system composed of N1,N2,N3, etc.

Example:

Sql>select bin_to_num (1,1,1,1) A from DUAL;

A

----------

15

3 CAST

Format: CAST (CAS T)

Description: Converts an expression C to a data type T. T can be an internal data type, or it can be a programmer's custom data type. The premise is to be able to convert before you can convert

Example:

Sql>select CAST (' 12345 ' 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 to If value and return then n if equal to if N. Otherwise, return else Z.

Example:

Sql>select DECODE (1,1,0,-1) as A,decode (1,0,0,-1) as B from DUAL;

Ab

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

0-1

5 Numtodsinterval

Format: Numtodsinterval (N,C)

Note: Converts the number n to the Intervalday to second type of data specified by C. The value of C is day,hour,minute,second. C is not case-insensitive

Example:

Sql>select Sysdate+numtodsinterval (Ten, ' Day ') A,sysdate+numtodsinterval (+, ' HOUR ') bfrom DUAL;

Ab

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

2014/7/1117:20:11 2014/7/2 3:20:11

6 Numtoyminterval

Format: Numtodsinterval (N,C)

Note: Converts the number n to the Intervalday to second type of data specified by C. The value of C is year, MONTH. C is not case-insensitive

Example:

Sql>select Sysdate+numtoyminterval ("year") A,sysdate+numtoyminterval (A, ' MONTH ') B from DUAL;

Ab

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

2024/7/117:24:182015/5/1 17:24:18

7 To_char

Format: To_char (X[,FMT])

Description: Converts x into a string in FMT format. X can be a date or a number, or it can be a string, FMT is a format string that sets the format in which X is converted

Example:

Sql>select to_char (sysdate, ' Yyyy-mm-dd ') A,to_char (a) B from DUAL;

Ab

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

2014-07-02111

8 To_clob

Format: To_clob (x)

Description: Converts the NCLOB value or other string x in the LOB column to the CLOB value

Example:

Sql>select to_clob (' AAAA ') A from DUAL;

A

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

Aaaa

9 To_date

Format: To_date (C[,FMT])

Description: Converts string C conforming to the specified date format of FMT to date-type data

Example:

Sql>select to_date (' 2014-07-02 ', ' Yyyy-mm-dd ') a,to_date (' 2014-07 ', ' yyyy-mm ') B fromdual;

Ab

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

2014/7/22014/7/1

Ten To_dsinterval

Format: To_dsinterval (C)

Description: Converts string C conforming to a specific format to data of type Intervalday to second

Example:

Sql>selectsysdate+to_dsinterval (' 1000:00:00 ') A from DUAL;

A

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

2014/7/128:49:39

One To_lob

Format: To_lob (X)

Description: Converts the value x in a long column to the value of the LOB column

Example:

To_number

Format: To_number (C[,FMT])

Description: Converts a string C that conforms to the specified number format of FMT to a numeric type of data

Example:

Sql>select to_number (' 0101 ') A from DUAL;

A

----------

101

To_timestamp

Format: To_timestamp (C)

Description: Converts the string C to a timestamp data type

Example:

Sql>selectto_timestamp (' 2014-07-1214:10:10.12320 ', ' Yyyy-mm-dd HH24:MI:SS. FF ') A from DUAL;

A

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

December-July-14 02.10.10.123200000 pm

To_timestamp_tz

Format: To_timestamp_tz (c[, FMT])

Description: Converts string C conforming to the specified date format of FMT to Timestampwith timezone type of data, with time zone

Example:

Sql>selectto_timestamp_tz (' 2014-07-1214:10:10.12320 ', ' Yyyy-mm-dd HH24:MI:SS. FF ') A from DUAL;

A

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

December-July-14 02.10.10.123200000 pm +08:00

To_yminterval

Format: To_yminterval (C)

Description: Converts string C conforming to a specific format to data of type intervalyear to month

Example:

Sql>selectsysdate+to_yminterval (' 01-03 ') Afrom DUAL;

A

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

2015/10/29:26:45

TRANSLATE

Format: TRANSLATE (x,if y, then Z)

Description: View each character in X, and then see if the character exists in if Y. If present, the position of the character is found in if Y, and then the character is replaced with the same position in then Z, and then the X after all the substitution is returned.

Example:

Sql>select TRANSLATE (' Abcdefghij ', ' abcdef ', ' 123456 ') A from dual;

A

----------

123456ghij

Unistr

Format: Unistr (c)

Description: Returns the Unicooe character corresponding to the string C

Example:


Sql>selectunistr (' 01-03 ') A from DUAL;

A

01-03