Oracle Learning (iv) _SQL function

--First part: SQL Basic--ch1 Simple Query--CH2 query basic concept--CH3 data filtering--Part Two: Multi-table Operation--CH4 set theory--ch5 inner Connection--CH6 outer connection--ch7 subquery--Part three: Data grouping  --ch8 Simple Statistics--CH9 data packet--ch10 packet data filtering-Part IV: SQL function--CH11 built-in function--ch12 CASE expression-Part V: DML statement--ch13 Insert data--ch14 Modify data --CH15 Delete Data----------------------------------------------------------------------------Part IV: SQL Functions "ch11 built-in functions"-- 11.1 Use the single-line function/* Character function number function Convert function Date function */--character function--ascii (x) returns the ASCII code of character x select ASCII (' a '), ASCII (' a '), ASCII (' Z '), ASCII (' Z '), ASCII (0), ASCII (9) from DUAL;--CHR (x) returns the ASCII code x character of select Chr, Chr (+), Chr (122), Chr (All-in-Dual;--concat (x, y) Append y to X and return the appended string select First_name,last_name,concat (first_name, last_name) Allname from Customers;--initcap (x) Converts the first letter of each word in X to uppercase select Product_id,description, Initcap (description) ad from Products WHERE product_id < 4;--instr (x , Find_str,star,occur)--Find the string find_str in X and return to the location where Find_str is located. --start specifies where to start the lookup, occur specifies the number of occurrences of FIND_STR select Name, InStr (Name, ' Science ') from the products WHERE product_id = 1; SELECT NAME, InStr (NAME, ' e ', 1, 2) from the products where product_id = 1;--available for date select customer_id, Dob,instr (DOB, ' ') from customers where CU stomer_id = 1;--can specify occur as a negative number, which means to look left from the end of the string for select NAME, InStr (Name, ' n ',-2, 2) from products WHERE product_id = 1;--lengt H (x) returns the number of characters in x select NAME, Length (NAME) from Products;--lower (x) converts letters in x to lowercase--upper (x) converts letters in X to uppercase select First_Name, Upper (First_Name), Last_name,lower (last_name) from Customers;--lpad (X,WIDTH,PAD_STR) on the left side of X to complement Pad_ The STR character makes its length width--rpad (X,WIDTH,PAD_STR) on the right side of the X Pad_str character so that its length is Widthselect name,rpad (name, A, '. '), Price, Lpad ( Price, 8, ' *+ ') from the products WHERE product_id < 4;--ltrim (X,TRIM_STR) truncate some characters from the left side of X until a character in X does not appear in Trim_str (x, TRIM_STR) truncates some characters from the right side of X until a character in X does not appear in Trim_str until--trim (Leading|trailing|both y from X) truncates a single character from X Yselect LTrim (' Hello Gail seymour! '), RTrim (' Hi Doreen Oakley!abcabc ', ' akb!c '), TRIM (TRAILING ' 0 ' from ' 000Hey Steve button!00000 ' ) from DUAL;--NVL (X,value) If x is empty, return value, otherwise return X--NVL2 (X,V1,V2) If x is empty, return v2, otherwise return v1select customer_id, phone, NVL (phone, ' unkown phone number '), DOB, NVL (DOB, sysdate), phone, NVL2 (phone, ' known ', ' Unknown ') from Customers;--replace (X,SEARCH_STR,REPLACE_STR) find search_str in X and replace it with Replace_ Strselect name,replace (Name, ' Science ', ' Physics ') from the products WHERE product_id = 1;--substr (x,start,length)  Returns a substring in X, starting at start with a substring length of lengthselect name, substr (name, 2, 7), substr (name, -5, 2) from the products WHERE product_id < 4;--11.2 function combination select NAME, upper (substr (NAME, 2, 8)) from the products WHERE product_id < 4;--11.3 numeric function--abs (x) returns the absolute value of x SE Lect ABS ( -1.222) from dual; Select product_id, Price, price-30, ABS (PRICE-30) from Products WHERE product_id < 4;--ceil (x) returns the smallest integer greater than or equal to x select Ceil (5.8), ceil ( -5.2) from Dual;--floor (x) returns the largest integer less than or equal to X select Floor (5.8), floor ( -5.2) from Dual;--mod (x, y) Returns the remainder of x divided by y Select mod (8, 3), mod (8, 4) from Dual;--power (x, y) returns x power (2,3) from Dual;--round (x, y) Returns the result of rounding x: If y is not specified, the first digit after the decimal point is rounded, if y>0, to the y+1 decimal fourFive in; if y<0, the Y-bit to the left of the decimal point is rounded up select round (5.75), round (5.75, 1), round (15.75,-1) from dual;--sign (x) returns the sign of X. If x<0, return 1; if x=0, return 0; if x>0, return 1SELECT sign ( -5), sign (0) and sign (5) from Dual;--trunc (x, y) to calculate the result of truncation of x: If y is not specified, Truncate the first digit after the decimal point and, if y>0, truncate the y+1 decimal place; if y<0, the Y-bit to the left of the decimal point is truncated select Trunc (5.75), Trunc (5.76, 1), Trunc (15.75,-1) from dual;-- The 11.4 conversion function--to_char (X,format) is used to convert X to a string. The format can be specified in form select To_char (123.456) from dual; SELECT to_char (12345.67, ' 99,999.99 ') from Dual;--to_number (X,format) is used to convert X to a number. You can use format to specify the form select To_number (' 9455.335 ') from dual; SELECT to_number ('-$12,345.67 ', ' $99,999.99 ') from dual;--using To_char () and to_date () Convert time value--to_char (X,format) Converts a number or time value x to a string. --to_date (X,format) converts the string x to the date type--using To_char () to convert the time value to the string select Sysdate,to_char (sysdate, ' Yyyy-mm-dd ') from dual; SELECT customer_id, To_char (DOB, ' MONTH dd,yyyy ') from customers; SELECT to_char (sysdate, ' MONTH dd,yyyy,hh24:mi:ss ') from dual;/* common format parameter YYYY four-bit year mm two-bit months month of full spell, The first 3 letters of all uppercase Mon months WW The first week of the year W this month DD the first few days of this month the day of the week d the first day of a weekWrite HH24 24 hour format hours HH 12 hour format hours mi minutes ss seconds ff[1..9] seconds separator with decimal:-/,.;: "Text" */select to_char (sysdate, ' D ') from dual;--using to_ Date () Converts the string to a time value of select To_date (' May 2,2006 ', ' month dd,yyyy ') from dual; SELECT to_date (' 20120808 ', ' yyyy-mm-dd ') from dual;--11.5 uses the time value function--add_months (x, y) to return the result of x plus y months, if y is a negative value, Subtract y months from X Select Add_months (sysdate, 3), Add_months (Trunc (Sysdate), 3) from Dual;--last_day (x) returns the last day containing the X-month select Last_ Day (DATE ' 2012-12-1 ') returns from Dual;--months_between (x, y) for several months between x and Y, and returns a positive number if x is later than Y, otherwise negative select Months_between (Sysdate + Sysdate) from Dual;--next_day (X,day) returns the time value of the next day starting with X, select Next_day (Date ' 2012-5-23 ', ' Monday ') from dual; SELECT next_day (DATE ' 2012-5-23 ', 2) is rounded to X from Dual;--round (x,unit). SELECT round (sysdate, ' YYYY ') from dual; Select round (sysdate, ' MM ') from Dual;--sysdate () returns the time value currently set in the operating system on which the database is located select Sysdate from Dual;--trunc (x,unit) truncates x. /*YYYY four-bit year mm two-bit month WW this year the week of the week DD this month the day of the week in the first days of the day HH24 24 hours format hours HH 12 hours format hours mi minutes */select trunc (sysdate, ' Y ') from Dual SELECT trunc (sysdate, ' MM ') from DuAl SELECT Sysdate+7,trunc (sysdate+7, ' W ') from dual; SELECT trunc (sysdate, ' DD ') from dual; SELECT trunc (sysdate+7, ' D ') from dual; SELECT trunc (sysdate, ' HH ') from dual; SELECT trunc (sysdate, ' MI ') from dual;--11.6 uses the translate () function--translate (X,FROM_STR,TO_STR) function to find characters in From_str in X, and convert it to the corresponding character in To_str select product_id, name, translate (name, ' ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGH Ijklmnopqrstuvwxyz ', ' EFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZABCD ') from the products; Select Translate (' I am Chinese, I love Chinese ', ' China ', ' PRC ') ' translate example ' from dual; Select Translate (' I am Chinese, I love China ', ' Chinese ', ' China ') ' translate example ' from dual;--11.7 use decode () function--decode (value,search_ Value,result,default_value) compares value to Search_value. If the two values are equal--returns result, otherwise default_value--is equivalent to executing an IF statement in SQL Select DECODE (1,1,2,3) from dual; SELECT DECODE (1,2,1,3) from dual;    SELECT prd_id, available, decode (available, ' Y ', ' Product is available ',          ' Product is not available ') the from more_products;--can pass multiple parameters to decode select product_id, product_type_id,              Decode (product_type_id, 1, ' book ', 2, ' Video ', 3, ' DVD ', 4, ' CD ', ' Magazine ') from the products; "Ch12 Case expression"--similar to decode, equivalent to the SQL The IF statement in the case expression has two forms: the case simple expression, which determines the result by comparing the expression to a simple set of expressions. A case search expression that determines the result by calculating a set of logical expressions. Both of these formats support optional ELSE parameters. Case can be used to allow any statement or clause that uses a valid expression. For example, a case can be used in statements such as SELECT, UPDATE, DELETE, SET, and select_list, in, WHERE, ORDER by, and having. --case Simple expression: Case input_expression if When_expression1 then result_expression1 when When_expression2 then Resul      T_expression2 ... [ELSE else_result_expression]  Endselect product_id, product_type_id, Case product_type_id if 1 then "book" When 2  Then ' Video ' if 3 Then ' DVD ' while 4 Then ' CD ' ELSE        ' Magazine ' END Casen from products;--using case search expression case if Logical_expression1 then Result_expression1     When Logical_expression2 then Result_expression2 ... [ELSE else_result_expression]  Endselect product_id, product_type_id, case if product_type_id = 1 Then ' book ' When product_type_id = 2 Then ' Video ' when product_type_id = 3 Then ' DVDs ' when product_type_ id = 4 Then ' the CD ' ELSE ' Magazine ' END casen from the products;