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