Oracle functions-Higher-order articles

Some of the Oracle functions are organized below-higher-order articles:

1, Catstr
Example: SELECT catstr (column_name) name_list from Dba_tab_columns where TRIM (table_name) = ' t_rz_dkdata '--the result of the query is in a column
2. INSTR () retrieves the string function: Match returns the index value (starting at 1) of the location that was first retrieved, or the value >0, otherwise the return value =0
Example: SELECT * from User_tables WHERE INSTR (table_name, ' t_rz_ ') >0--table with the ' t_rz_ ' character in the query table name
Example: SELECT INSTR (' ABCDEHFGHJK ', ' h ') from dual; --Returns the index position of the first occurrence of H, return value =6
3. Replace (Str,searth_str,[replace_str]) string substitution function:
STR: target string;
SEARTH_STR: Specifies the string to find
REPLACE_STR: Used instead of finding a matching string, when no such argument is found, the SEARTH_STR will be deleted
For example: sql> SELECT REPLACE (' YADDSFSFSA ', ' d ') from dual;
REPLACE (' Yaddsfsfsa ', ' D ')
-------------------------
Yasfsfsa
sql> SELECT REPLACE (' YADDSFSFSA ', ' d ') from dual;
REPLACE (' Yaddsfsfsa ', ' D ', ' 1 ')
-------------------------
Ya11sfsfsa
4, CONCAT (STR1,STR2) string connection, similar to | | , Example: SELECT CONCAT (' str1 ', ' str2 ') from DUAL--Results: STR1STR2
5, Initcap (str) returns a string: The first letter is capitalized and the other letter is lowercase.
6, Lpad (String, Padded_length, [pad_string])--padding the character pad_string or spaces to the left of a field or character string, the length of the fill is padded_length
Example:sql> SELECT lpad (' A ', 2, ' B '), Rpad (' A ', 2, ' B ') from dual;
Lpad (' A ', 2, ' B ') rpad (' A ', 2, ' B ')
--------------- ---------------
BA AB
7, Reverse (str) string reversal function
Example: SELECT REVERSE (' ABCDEFGHJK ') from dual; -The result is: KJHGFEDCBA
8. Translate (string,from_string,to_string) function: A function that modifies a string by character set conversion
String: target string;
From_string: The character set that you want to convert;
To_string: Replaces the new character set of from_string, and each from_string character in the target string string is replaced by the corresponding character in the to_string character, if the from_string character is in To_ There is no corresponding character in the string, the character is deleted in the target string.
Example:
sql> SELECT TRANSLATE (' qwer.1233430004 ', ' 0123456789. ', ' Abcdefghij ') from dual;
TRANSLATE (' qwer.1233430004 ', ' 0
------------------------------
Qwerbcddedaaae
9, sign (number) function: Numbers are negative, 0, positive values corresponding to the return value of -1,0,1, often and sum () used together
10. Floor (value) function: Returns the largest integer less than or equal to value, SELECT floor ( -9.612727) from dual; ---return value is-10
11. Greatest (expr,[expr,......]) Function: Returns the maximum value in the argument list, which can be used for numeric, string, and date comparisons
Least (expr,[expr,......]) Function: Returns the minimum value in the argument list, which can be used for numeric, string, and date comparisons, as opposed to greatest ()

<------------------------continuous Update--------------------------->

This article is from the "Qytag (upspringing)" blog, so be sure to keep this source http://qytag.blog.51cto.com/6125308/1437868