Oracle function-higher order
Here are some of the oracle functions-higher-order articles:
1 、 CATSTR
For example: SELECT CATSTR (COLUMN_NAME) NAME_LIST FROM DBA_TAB_COLUMNS WHERE TRIM (TABLE_NAME) = 'Tunable RZED DKDATA'-the result of the query is in a column
2. INSTR () retrieves the string function: if it matches, it returns the index value of the location retrieved for the first time (starting from 1), the value is > 0, otherwise the return value = 0
For example: SELECT * FROM USER_TABLES WHERE INSTR (TABLE_NAME,'T_RZ_') > 0-query a table whose name contains the character 'Tunable RZ'.
For example: SELECT INSTR ('abcdehfghjk','h') FROM dual;-returns the location of the index where h first appeared, with a value of 6
3. Replace (str,searth_str, [replace_str]) string replacement function:
Str: target string
Searth_str: specifies the string to find
Replace_str: used instead of finding matching strings. Without this parameter, all found 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 concatenation, similar to | |, for example: SELECT CONCAT ('str1','str2') FROM DUAL-- get the result: str1str2
5. INITCAP (str) returns a string: the first letter is uppercase and the other letters are lowercase.
6. LPAD (string, padded_length, [pad_string])-fill in the character pad_string or space to the left of the field or character string, and the length after filling is padded_length
For example: SQL > SELECT LPAD ('Amalagem 2 Zhe B'), RPAD (' Achilles dagger 2) FROM dual
LPAD ('Achilles pommel 2 recollection B') RPAD (' Achilles dagger 2 cowboy 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: target string
From_string: character set to be converted
To_string: a new character set that replaces from_string. Each from_string character in the target string string is replaced by a corresponding character in the to_string character. If the from_string character does not have a corresponding character in the to_string, the character is deleted in the target string.
For example:
SQL > SELECT TRANSLATE ('QWER.1233430004','0123456789.','abcdefghij') FROM dual
TRANSLATE ('QWER.1233430004','0
-
QWERbcddedaaae
9. Sign (number) function: when number is negative, 0, and positive, the corresponding return values are-1, respectively, which are often used with 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, which can be used to compare numbers, strings, and dates
Least (expr, [expr, …]) Function: returns the minimum value in the parameter list, which can be used to compare numbers, strings, and dates, as opposed to greatest ()
