Single-row Oracle learning functions

1. Single-Row Functions (Single-Row function) Single-Row function returns a separate result Row for the table or view queried for each Row, single-row functions can appear in SELECT columns, WHERE clauses, start with and connect by clauses, and HAVING clauses. Single-row Functions can operate on multiple data types: 1. Numeric Functions (Numeric Functions): commonly used Functions used to operate Numeric Functions are as follows: 1.1, ABS (n ): this function returns the absolute value of n. SQL code SQL> SELECT ABS (-10) FROM dual; ABS (-10) ---------- 10 1.2, FLOOR (n ): this function returns a maximum value equal to or less than n SQL code SQL> select floor (12.8) from dual; FLOOR (12.8) ----------- 12 SQL> SELECT FLOOR (-12.8) from dual; FLOOR (-12.8) -------------13 1.3, MOD (n2, n1): This function returns the remainder of n2 divided by n1, if n1 is 0, return n2Sql code SQL> select mod (11,4) from dual; MOD (11,4) -------- 3 SQL> SELECT MOD (11,0) FROM DUAL; MOD (11,0) ---------- 11 1.4, ROUND (n, integer): This function rounds the value n and returns the SQL code SQL> SELECT ROUND (13.265, 1) FROM DUAL; ROUND (13.265, 1) --------------- 13.3 SQL> select round (13.265,-1) from dual; ROUND (13.265,-1) -------------- 10 SQL> SELECT ROUND (13.265, 0) FROM DUAL; ROUND (13.265, 0) --------------- 13 SQL> SELECT ROUND (3.6) FROM DUAL; ROUND (3.6) ---------- 4 1.5, TRUNC (n1, n2 ): SQL code> select trunc (13.68, 1) from dual; TRUNC (13.68, 1) ------------ 13.6 SQL> SELECT TRUNC (13.68,-1) from dual; TRUNC (13.68,-1) --------------- 10 SQL> select trunc (13.68) from dual; TRUNC (13.68) ------------ 13 SQL> SELECT TRUNC (13.68, 0) FROM DUAL; TRUNC (13.68, 0) -------------- 13 1.6, LOG (n2, n1): This function returns the logarithm of n1 with n2 as the base, where n1 must be a positive number greater than 0, n2 must be a positive number greater than 1 SQL code SQL> select log (10,100) from dual; LOG (10,100) ----------- 2 SQL> SELECT LOG () FROM DUAL; LOG (1.7) ---------- 0, POWER (n2, n1): This function is used to evaluate the n1 POWER of n2 SQL code SQL> SELECT POWER () FROM DUAL; POWER) ---------- 27 SQL> SELECT POWER (-3, 3) FROM DUAL; POWER (-3, 3) ------------27 2. Character Functions Returning Character Values (letter number ): common functions used to operate functions of the character type and return character data are as follows: 2.1, CONCAT (char1, char2): This function is used to connect two strings: char1 and char2, SQL> select concat ('hello', 'World') "message" from dual; message --------- Hello World SQL> SELECT CONCAT (ename, 'job is'), job) "job" 2 FROM emp 3 WHERE empno = '000000'; job --------------------------- SMITH's job is CLERK 7369, POWER (char ): this function converts the first character of a string to an uppercase SQL code SQL> SELECT INITCAP ('Hello World') FROM DUAL; INITCAP ('He ----------- HELLO WorLD 2.3, LOWER (char ): this function converts all uppercase letters in the string to lowercase letters SQL code SQL> SELECT LOWER ('Hello World') FROM DUAL; LOWER ('hell ----------- HELLO WORLD 2.4, UPPER (char): converts all lowercase letters in the string to uppercase letters. SQL code: SQL> SELECT LOWER ('Hello World') FROM DUAL; LOWER ('hell ----------- hello world 2.5, SUBSTR (char, position, substring_length): This function captures a char string, specifying the start position and length of the truncation, if no truncation length is specified, the string is intercepted from the start position to the end of the string. If the specified start position is a positive number, the first one from the left is 1, if the specified start position is negative, the first FROM the right to the left is-1Sql code SQL> SELECT SUBSTR ('Hello world', 7) FROM DUAL; SUBST ----- world SQL> select substr ('helloworld', 1, 3) from dual; SUB --- El SQL> SELECT SUBSTR ('helloworld',-3, 3) FROM DUAL; SUB --- RLD 2.6, TRIM (trim_character FROM trim_source ): this function removes the specified trim_character string FROM the trim_source string, but can only remove the specified character at the beginning and end of the SQL code SQL> SELECT TRIM ('H' FROM 'Hello World') FROM DUAL; TRIM ('H' FR ---------- ello world SQL> SELECT TRIM ('D' FROM 'Hello World') FROM DUAL; TRIM ('D' FR ---------- hello worl 2.7, LPAD (exp1, n, exp2): This function uses the value of the exp2 expression to fill the exp1 expression from the left, the total length after filling is nSql code SQL> SELECT LPAD ('World', 10, '*') FROM DUAL; LPAD ('worl ---------- ***** WORLD 2.8, RPAD (exp1, n, exp2): This function uses the value of the exp2 expression to fill the exp1 expression from the right, the total length after filling is n SQL code SQL> SELECT RPAD ('World', 10, '*') FROM DUAL; RPAD ('worl -------- WORLD ***** prepared by dong_dong