Shulou(Shulou.com)06/01 Report--

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 ()

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.