Oracle functions-advanced

Oracle functions-advanced

Below are some of the Oracle functions-advanced:

1. CATSTR
Example: select catstr (COLUMN_NAME) NAME_LIST FROM DBA_TAB_COLUMNS where trim (TABLE_NAME) = 't_rz_dkdata '-- the query result is in a column.
2. INSTR () string retrieval function: returns the index value (starting from 1) of the First retrieval location after matching. The value is greater than 0. Otherwise, the return value is 0.
Example: SELECT * FROM USER_TABLES where instr (TABLE_NAME, 't_rz _ ')> 0 -- query a table whose name contains the characters 't_rz _'
Example: select instr ('abcdefghjk ', 'H') FROM dual; -- return the index position where h appears for the first time. The returned value is 6.
3. replace (str, searthur _str, [replace_str]) string replacement function:
Str: The target string;
Sear_str: Specifies the string to be searched.
Replace_str: used to replace the search string. If this parameter is not set, all the searched sear_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 |. For example, select concat ('str1', 'str2') from dual -- the result is str1str2.
5. INITCAP (str) returns a string: the first letter in upper case and the other letters in lower case.
6. LPAD (string, padded_length, [pad_string]) -- fill in the character pad_string or space on the left side of the field or string. The length after filling 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 inversion 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 through Character Set conversion.
String: The target string;
From_string: the character set to be converted;
To_string: replaces the New Character Set of from_string. Each from_string character in the target string is replaced by the corresponding character in to_string. If the character of from_string does not have the corresponding character in to_string, delete the character from the target string.
Example:
SQL> SELECT TRANSLATE ('qwer. 100', '192. ', 'abcdefghj') FROM dual;
TRANSLATE ('qwer. 1233430004 ', '0
------------------------------
QWERbcddedaaae
9. sign (number) function: When number is negative, 0, or positive, the returned values are-, 1, and sum ().
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 parameter list for comparing numbers, strings, and dates.
Least (expr, [expr,…]) Function: returns the minimum value in the parameter list, which can be used for comparison of numbers, strings, and dates. It is opposite to greatest ().

Detailed examples of Oracle function GREATEST Functions

Single-row conversion functions of Oracle Functions

Oracle functions that generate dynamic prefixes and auto-increment numbers

Replace () for Oracle Functions ()

Oracle Functions

Differences in case and decode usage and Performance Comparison of Oracle Functions