Oracle Database Function collation

1. Character classes

Lpad(STR1,LENGTH,STR2)

The Lpad function fills the string with the specified character from the left. From its literal meaning can also be understood, L is the left shorthand, pad is the meaning of padding, so the lpad is the meaning of the padding.

Example:Select Lpad (1,5, ' X ') from dual; output: xxxx1

Rpad(STR1,LENGTH,STR2)

The Lpad function fills the string with the specified character from the right. From its literal meaning can also be understood, R is the abbreviation of right, pad is the meaning of padding, so lpad is to fill the meaning from the left.

Example: Select Rpad (1,5, ' X ') from dual; Output Result: 1xxxx

Upper(str)

Converts the current string all to uppercase

Lower(str)

Converts the current string all to lowercase

Initcap (str)

Capitalize the first character and other characters into lowercase

Example: Select Initcap (' HEllo World ') from dual; Output Result: Hello World

Replace (string 1, String 2, String 3)

Replace all string 2 in string 1 with string 3

Example: Select replace (' HEllo world ', ' he ', ' he ') from dual; Output Result: Hello World

Translate (char, from, to)

Converts the character in the from into the character corresponding to the position in to, and if the corresponding character is not found in to, the character in the return value is deleted

Example: Select Translate (' 1ello 2orldc ', ' 12c ', ' HW ') from dual; output result: Hello World

Bitand (EX1,EX2)

not commonly used, returns the result of two numeric numeric values after the bitwise AND operation, if both the EX1 and ex2 bits are 1, the corresponding result bit is 1; otherwise the corresponding result bit is 0 concat (CHAR1,CHAR2)stitching Char1 and CHAR2 strings together Wmsys.wm_concatThe columns of all query results are stitched together with "," 2. Date Class To_char (Date,char) Date turn characterExample: Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') from dual; output result: 2017-07-31 17:16:14 to_date (char,date) character turn dateExample: Select To_date (' 2017-07-31 17:16:14 ', ' yyyy-mm-dd hh24:mi:ss ') from dual; output result: 2017/7/31 17:16:14

Select To_char (sysdate, ' yyyy ') as nowyear from dual; --year of acquisition of time
Select To_char (sysdate, ' mm ') as nowmonth from dual; --Get the month of the time
Select To_char (sysdate, ' DD ') as nowday from dual; --Day of acquisition of time
Select To_char (sysdate, ' hh24 ') as nowhour from dual; -When the time is acquired
Select To_char (sysdate, ' mi ') as Nowminute from dual; --access to time points
Select To_char (sysdate, ' SS ') as Nowsecond from dual; --Get the seconds of time
Select To_char (to_date (' 2017-07-31 ', ' yyyy-mm-dd '), ' Day ') from dual; --Get the day of the week

3. Aggregation Functions avg () Average max () max value min () min value sum () sum count () statistics 4. Conditional Judgment NVL (A, b)if the value of the preceding a is NULL then the value returned is the following B nvl2 (a,b,c)If A is null, the result is B, otherwise the result is C decode (field, condition 1, value 1, Condition 2, value 2,... Value N)If the field satisfies the condition 1, then the value 1, and so on, the last return value n 

Oracle Database Function collation