Notes for common SQL Functions

Common SQL functions need to pay attention to the details. This is a question that a cool man asked us to think about. To tell the truth, I was so blind at the time. Although the function was clear, but it was not accurate to this level, the following are some typical questions. My experiments are not necessarily accurate, and there are more than one test method. Please forgive me for learning and making progress together !!! 1. how to add the following parameters before the number: www.2cto.com SQL> select lpad ('000000', 10, '0') from dual; LPAD ('000000' -------- 0000000123SQL> select lpad (123, 10, 0) from dual; LPAD (123 ---------- 0000000123SQL> select lpad ('123', 10, '0') from dual; LPAD ('123' -------- 123. if you use trim to delete spaces, will the carriage return or line break at the end of the string be deleted? the chr function is used here. Chr function: returns a character encoded with a numeric expression value. Description: The Return Value Type of the function is string, and the value range of the numeric expression is 0 ~ 255. chr (10) stands for carriage return Chr (13) stands for line feed Chr (32) stands for space SQL> select length ('hello' | chr (10) from dual; LENGTH ('hello' | CHR (10) ---------------------- 6SQL> select length (trim ('hello' | chr (10) from dual; LENGTH (TRIM ('hello' | CHR (10) ---------------------------- 6SQL> select length ('hello' | chr (32) from dual; LENGTH ('hello' | CHR (32) ---------------------- 6SQL> select length (trim ('hello' | chr (32) from dual; LE NGTH (TRIM ('hello' | CHR (32) ---------------------------- 5SQL> select length ('hello' | chr (13) from dual; LENGTH ('hello' | CHR (13) ---------------------- 6SQL> select length (trim ('hello' | chr (13) from dual; LENGTH (TRIM ('hello' | CHR (13) ------------------------------ 6 conclusion: From the experiment above, we can conclude that the space cannot remove the carriage return and line break. 3. What are the results of two values in the date function months_between if the left value is greater than the right value? I have consulted The following official documents: Invalid returns number of months between datesdate1 anddate2. the month and the last day of the month are defined by The parameterNLS_CALENDAR.If date1 is later thandate2, then the result is positive. ifdate1 is earlier thandate2, then the result is negative. ifdate1 anddate2 are either the same days of the month or both last days of months, then the result is alwa Ys an integer. otherwise Oracle Database calculates the fractional portion of the result based on a 31-day month and considers the difference in time componentsdate1 anddate2. if the right side (date2) the value greater than the number returned by date1 on the left is a negative value. Experiment: SQL> select months_between 2 (to_date ('02-02-2012 ', 'Mm-dd-yyyy'), 3 to_date ('01-01-2012 ', 'Mm-dd-yyyy') "Months" 4 from dual; Months ---------- 1.03225806SQL> select months_between 2 (to_date ('2017-01-01 ', 'yyyy-mm-dd'), 3 to_date ('1970-02-02 ', 'yyyy-mm-dd') "Months" 4 from dual; Months ----------- 2012 conclusion: if date1 is less than the value of date2, It is negative. 4. case... How can I judge if the column value retrieved by the when function is Null? SQL> select a, 2 case nvl (B, 4) when 1 then 123 3 when 2 then 123 4 when 3 then 123 5 when 4 then 888 6 when 5 then 123 7 end as B 8 from t; a B ---------- 1 123 2 123 3 123 4 888 5 123 conclusion: the method I can think of now is to convert the nvl function to A visible value or character, by default, the result obtained when I do not process null is also null. For example, if I do not specify when 4, the 4th results will be null. 5. Another reason is that the result obtained from the operation with Null is also Null. SQL> select 5 * null from dual; 5 * NULL Source http://blog.csdn.net/elvis_dataguru/article/details/8393947