Functions in Oracle

A: Preface

Recently decided to summarize the knowledge points every day, and then each week to publish the knowledge points in the classification of the journal.

II: The Question

(1): In Oracle for age calculation, know that the date of birth after the calculation of interception, it is a very simple function, but I think for these things really use more than I remember, so I still record down, or I have to search every time, or is very troublesome.

function to calculate Age:

Trunc ((SYSDATE-CSRQ)/365,0) as NL
The form of this function: Trunc (number,decimals); The function returns the processed value. Similar to round, but the function does not do rounding, but rather the entire decimal point is truncated.

Number: The value to be intercepted for processing;

Descimals: Indicates the number of digits after the decimal point is reserved. (You can choose to ignore, then intercept all the decimal parts);

eg

TRUNC (22.22,0) = 22;

TRUNC (22.22) = 22;

TRUNC (22.3356456,2) = 22.33;

TRUNC (22.54464,-1) = 20;

Trunc can also represent the interception of time:

eg

Trunc (sysdate, ' yyyy ')--return to the first day of the year.

Trunc (sysdate, ' mm ')--Returns the first day of the month.

Trunc (sysdate, ' d ')--Returns the first day of the current week.

The following cases have just been tested: Select Trunc (to_date (sysdate, ' dd-mon-yyyy ')) from dual;

The results are as follows: 0014/9/23;

I think for a while or do not know why. In the solution.

(2): Replace function in Oracle

Eg:replace (Get_name (Z.HJQH, ' xzqy '), ",") | | Z.HJMX) as HJMX; This represents the space in the Out field

Here is the situation in JS to remove the space var aa= "Fdksja Fckdjah fcsdajfha", Aa=aa.raplace (/\s+/, "");

(3): Oracle function Wm_concat
Today, when querying some questions, I saw an Oracle function Wm_concat, just at the beginning I do not know what this function means, but saw a quick look, a little bit,

Oneself in the database casually test the following this function, probably know the function of the basic ability bar

Select Wm_concat (ID) from t_xx whereid between 1 and 10;
The result is: 1,2,3,4,5,6,7,8,9,10

The plain is to connect the ID together with a comma separated. Online says it's best to write a method if you want to achieve this effect. Because the Oracle database does not call this method at the same time, there may be a difference in the return value type.

Functions in Oracle