Oracle built-in functions

A String type

1 CONCAT (A, B) two field AB connections

2 a| | B field connection, you can connect multiple

3 Length (char \ varchar2) return string lengths

4 LOWER All Letters lowercase

5 UPPER All Letters capitalized

6 Initcap uppercase, the remaining lowercase, if there are spaces between the words, the first letter of each word is capitalized

7 TRIM (char1 from CHAR2) Delete char1 on both sides of CHAR2, note that CHAR1 can only have one character

8 LTRIM (CHAR1,CHAR2) starts from the left of Car1, and the characters contained in the CHAR2 are deleted until they encounter characters that do not exist in CHAR2.

9 RTRIM (CHAR1,CHAR2) starts from the right side of char1, deleting all occurrences of the character in the char2 until it encounters a character that has not been seen in CHAR2

Lpad (Str1,n, ' $ ') str1 is the string to manipulate, n is the target length, and $ is the replacement symbol. str1 insufficient length n, insufficient bit left to fill $, beyond n bits, intercept n bits from left to right

One rpad (str1,n, ' $ ') str1 is the string to manipulate, n is the target length, and $ is the replacement symbol. STR1 less than the length of n, the insufficient bit to the right of the $, beyond the N-bit, but also from left to right intercept n bits

SUBSTR (str,index,length) operation Str String, from the left index position, start interception, length, if index is negative, from the right number of the index bit start intercept, if not specify length, intercept to the last

INSTR (str1,str2,m,n) Check where str2 appears in Str1, M indicates where to start the check, n indicates the number of occurrences, MN defaults to 1

Two-value operation

1 ROUND (Num,index) ROUND (45.798,2) bit index is 0, move right two bits is 9, see next one is 8, enter one, get 45.80. Round (45.678,-1) left one, 4, next 5 into a 50

2TRUNC (m,n) Direct intercept trunc (45.678,2) from the single-digit right two bit 7, the rear cut off 45.67

3MOD (m,n) m%n

4 Ceil (A) rounding up

5FLOOR (b) rounding down

3rd issue

1 To_cahr (date, ' yyyy-mm-dd ') converts date to ' YYYY-MM-DD ' format, which can be defined by itself, but when defined, be aware that characters that are not recognized by the system are enclosed in double quotation marks.

2 Last_day (date) Returns the date of the last day of the month

3 add_months (date,i) returns DATE plus I month which day

4 Months_between (DATE1,DATE2) between two dates for a few months

5 Next_day (DATE,CHAR1) starting from the date of the next day to find, the first char1, CHAR1 can take the value of the days of the week, English days, 1-7, note 1 is Sunday, followed by push.

6 laest (A,b,c ...) Find the smallest

7 Greast (A,b,c ...) Find the maximum

8 EXTRACT (year from date) extract from date you want the element year month or day

9 NVL (ARG1,ARG2) If arg1 is not empty, return arg1, if empty, return arg2

NVL2 (ARG1,ARG2,ARG3) If arg1 is not empty, return arg2, if Arg1 is empty, return ARG3

Oracle built-in functions