Common simple functions in oracle (ONE)

Common simple functions in oracle (ONE)
In oracle, functions are divided into multiline functions and single-line functions. Single-line functions can be directly referenced in plsql blocks, but multiline functions can only be referenced in Embedded SQL statements in plsql blocks. Common single-row functions include character functions, numeric functions, conversion functions, date functions, and regular expression functions. 1) character function 1) ascii: converts a character to an ascii value. Eg: [SQL] SQL> select ascii ('A'), ascii ('B') from dual; ASCII ('A') ASCII ('B ') ---------- 65 98 SQL> </span> 2) length (x): This function is used to return the length of x string: eg: [SQL] SQL> select length ('rhys ') from dual; LENGTH ('rhys') -------------- 4 SQL> 3) concat (x, y): This function adds string y to string x: eg: [SQL] SQL> select concat ('rhys', 'learn Oracle! ') From dual; CONCAT ('rhys', 'le --------------- RhysLearn Oracle! SQL> </span> 4) lower (x) & upper (x): This function is used to convert string x from size to lower case, and upper (x ): the function is to display string x in uppercase from lowercase: eg: [SQL] SQL> select lower ('rhys') from dual; LOWE ---- rhys SQL> select upper ('rhys') from dual; UPPE ---- rhys SQL> 5) substr (x, y, z ): this function is used to start string x from left to right y, and then intercept z length: eg: [SQL] SQL> select substr ('Hello Rhys Learn Oracle ', 6, 4) from dual; SUBS ---- Rhy SQL> 6) instr (x, find_string [, start] [, occurrence]): This function is used to search for find _ in string x _ String character. You can specify the position in x to start searching, that is, the start position. You can also specify the location where the number of occurrences of find_string in occurrence is located. Eg: [SQL] SQL> select instr ('Hello rhys hello oracle ', 'O', 2, 3) from dual; INSTR ('hellorhyshellooracle', 'O', 2, 3) ----------------------------------- 18 SQL> 7) initcap (x): This function is used to uppercase the first letter of all words in string x: eg: [SQL] SQL> select initcap ('hello, oracle, hello, rhys ') from dual; INITCAP ('hello, ORACLE, H --------------------- HELLO, Oracle, Hello, Rhys SQL> 8) replace (x, y, z ): this function is used to search for y in the string, and then replace y with z: eg: [SQL] SQL> Select replace ('Hello oracle, hello rhys', 'rhys', 'xiaohai 2') from dual; REPLACE ('hellooracle, HELLOR ------------------------- hello oracle, hello xiaohai SQL> </span> 9) rpad (x, length, rpad_string): This function is used to right-complete x so that it reaches the length, the default filling mode is space. You can also use the rpad_string character to fill in: eg: [SQL] <span style = "font-size: 14px; "> SQL> select rpad ('rhys', 8) from dual; RPAD ('rh -------- Rhys SQL> select rpad ('rhys', 8, '*') from Dual; RPAD ('rh -------- Rhys *** SQL> </span> 2) number function: 1) abs (x); this function is used to return the absolute value of x: eg: [SQL] SQL> select abs (-20000) from dual; ABS (-20000) ----------- 20000 SQL> 2) mod (x, y ): returns the remainder of x divided by y: eg: [SQL] SQL> select mod () from dual; MOD () ---------- 1 SQL> 3) ceil (x ): returns the smallest integer greater than or equal to x: eg: [SQL] SQL> select ceil (22.4) from dual; CEIL (22.4) ---------- 23 SQL> 4) floor (x): this function is used for the maximum integer less than or equal to x: eg: [SQL] SQ L> select floor (22.4) from dual; FLOOR (22.4) ----------- 22 SQL> 5) power (x, y): This function is used to return the power value of y of x: eg: [SQL] SQL> select power (2, 3) from dual; POWER (2, 3) ---------- 8 SQL> 6) sqrt (x): This function calculates the square root of x, x must be greater than or equal to 0 eg: [SQL] SQL> select sqrt (4) from dual; SQRT (4) -------- 2 SQL> 7) round (x, [y ]): returns the integer of x. If y is specified, it indicates that the integer starts at y of the decimal point. If y is a negative value, the decimal point of x is rounded to y. Eg: [SQL] <span style = "font-size: 14px;"> SQL> select round (22.333, 2) from dual; ROUND (22.333, 2) --------------- 22.33 SQL> select round (22.333,-2) from dual; ROUND (22.333,-2) ---------------- 0 SQL> select round (222.333,-2) from dual; ROUND (222.333,-2) ----------------- 200 SQL> select round (22.263, 2) from dual; ROUND (22.263, 2) ------------- 22.26 SQL> select round (22.263, 1) from dual; ROUND (22. 263,1) --------------- 22.3 SQL> </span> 8) sign (x): This function is used to detect positive and negative values. If the value is zero, it is zero. If the value is negative, the value is-1. If the value is positive, the result is 1: eg: [SQL] <span style = "font-size: 14px; "> SQL> select sign (-2), sign (0), sign (2) from dual; SIGN (-2) SIGN (0) SIGN (2) -----------1 0 1 SQL> </span> 9) cos (x) returns the arc cosine of a number (3) conversion Function: 1) to_char (x): converts a number to a string: eg: [SQL] <span style = "font-size: 14px;"> SQL> select enam E, 'this sal is '| to_char (sal) from emp; ENAME 'thissalis' | TO_CHAR (SAL) ---------- -------------------------------------------------- WARD this sal is 1250 JONES this sal is 2975 MARTIN this sal is 1250 BLAKE this sal is 2850 SCOTT this sal is 3000 KING this sal is 5000 TURNER this sal is 1500 ADAMS this sal is 1100 JAMES this sal is 950 FORD this sal is 3000 MILLER this sal is 2222 11 rows sel Ected. SQL> </span> 2) to_number (x) converts a string to a number: eg: [SQL] <span style = "font-size: 14px; "> SQL> select to_number ('-$21,222.33', '$99,999.99') from dual; TO_NUMBER ('-$21,222.33', '$99,999.99 ') ------------------------------------21222.33 SQL> </span> 3) cast (x as type); this function is used to specify the type of type compatibility for x; eg: [SQL] <span style = "font-size: 14px;"> SQL> select cast ('000000' AS VARCHAR2 (20) | 'hello' from dual; CAST ('20140901' VARCHAR2 (20) ----------------------- 1234 hello SQL> </span> 4) asciistr (x): converts any string to an ASCII string of the database character set 5) bin_to_number (x ): convert the binary number x to the number type.