Common SQL functions need to be aware of the details

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

This is a cow people let us think of the question, to tell the truth at that time, although the function is clear, but refinement to this degree, really is called not Ah, the following is a few more typical problems, and I do the experiment, not necessarily accurate, and the method of testing not only one, please forgive me, learn together, common progress!!!

1. How to fill 0 before the number

Sql> Select Lpad (' 123 ', ten, ' 0 ') from dual;

Lpad (' 123 '

----------

0000000123

Sql> Select Lpad (123,10,0) from dual;

Lpad (123,1

----------

0000000123

Sql> Select Lpad (' 123 ', ten, ' 0 ') from dual;

Lpad (' 123 '

----------

0000000123

2. If you remove a space with trim, the return or line break at the end of the string will be deleted .

Method: The CHR function is used in this area . Chr function: Returns a character encoded as a numeric expression value. Description: The function return value type is string and its numeric expression has a value range of 0~255.

CHR (10) stands for carriage return

CHR (13) stands for line break

CHR (32) stands for space

Sql> Select Length (' Hello ' | | Chr (Ten)) from dual;

LENGTH (' HELLO ' | | CHR (10))

------------------------

6

Sql> Select Length (Trim (' Hello ' | | Chr (Ten))) from dual;

LENGTH (TRIM (' HELLO ' | | CHR (10)))

------------------------------

6

Sql> Select Length (' Hello ' | | Chr (+) from dual;

LENGTH (' HELLO ' | | CHR (32))

------------------------

6

Sql> Select Length (Trim (' Hello ' | | CHR)) from dual;

LENGTH (TRIM (' HELLO ' | | CHR (32)))

------------------------------

5

Sql> Select Length (' Hello ' | | CHR) from dual;

LENGTH (' HELLO ' | | CHR (13))

------------------------

6

Sql> Select Length (Trim (' Hello ' | | CHR)) from dual;

LENGTH (TRIM (' HELLO ' | | CHR (13)))

------------------------------

6

Conclusion: From the above experiment, it can be concluded that the blanks cannot remove the carriage return and newline characters.

3. what is the result of the two values inside the date function Months_between if the left value is greater than the right value?

Check out the official documentation below, which is described below:

Syntax

Purpose

Months_betweenReturns number of months between datesDate1andDate2. The month and the last day of the month is defined by the parameterNls_calendar.IfDate1is later thanDate2 date1 is earlier than and then the result is negative. date1 and date2 is either the same days of the month or both last days of months, and then the result was always An integer. Otherwise Oracle Database calculates the fractional portion of the result based on a 31-day month and considers the differ ence in time components and date2

With the yellow Department text description, you know that ifthe right (Date2) is greater than the Left (date1), the number returned 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.03225806

Sql> Select Months_between

2 (to_date (' 2012-01-01 ', ' yyyy-mm-dd '),

3 to_date (' 2012-02-02 ', ' Yyyy-mm-dd ')) "Months"

4 from dual;

Months

----------

-1.0322581

Conclusion: If date1 is less than date2 value is negative

4.case...when function if the column value taken out is null, how to judge?

Sql> Select a,

2 case NVL (b,4) while 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

Summary: I can think of the method is to use the NVL function to convert to a visible value or character, I also default does not handle null results are also empty, such as I do not specify when 4, so that thefirst 4 The bar is also null .

5. There is also a null value for the result of the operation.

Sql> select 5*null from dual;

5*null

Elvis

2012.12.23

Knowledge sharing ~ Common progress

Reprint Please specify:

http://blog.csdn.net/elvis_dataguru/article/details/8393947

Common SQL functions need to be aware of the details