PLSQL _ base Series 8 _ operator LPAD/TRUNC/DECODE/TRIM/INSTR, plsqllpad

PLSQL _ base Series 8 _ operator LPAD/TRUNC/DECODE/TRIM/INSTR, plsqllpad

BaoXinjian

I. Character Functions

1. LOWER (strexp)-returns a string and lowercase all characters.

select lower('ABCDE') from dual

 

2. UPPER (strexp) returns a string and uppercase all characters.

select upper('abcdf') from dual

 

3. INITCAP (strexp) converts the first letter of a string (each word) into upper case and lower case;

Select initcap ('source fore') from dualselect initcap ('source fore') from dual -- source fore (if both are uppercase letters, the first letter is automatically changed to uppercase letters and other lowercase letters)

 

4. CONCAT (strexp, strexp): connects two strings

select concat(first_name,last_name) from employees

 

5. substr (str, start_index, length): truncates a string of the specified length from the specified position.

select substr('abcdefg',2,3) from dual

 

6. LENGTH (strexp): returns the LENGTH of the string.

select length('abcdef') from dual

 

7. INSTR (C1, C2, I, J): searches for a specified character in a string and returns the location where the specified character is found;

C1: Search characters

C2: the character to be searched

I: Where to start searching

J: Find the number of occurrences

select instr('aborcdoryuklhorp','or',1,2) from dual  ---7select instr('aborcdoryuklhorp','or',6,2) from dual  ---14   

 

8. LPAD (string1, padded_length, [pad_string]) paste characters on the left of the column

select lpad('acd',8) from dual

 

9. RPAD (paste character) RPAD? Paste characters on the right of the column

select rpad('acd',8,'') from dualselect rpad('acdefghijklm',8,'') from dual

 

10. trim (str): truncates special characters at both ends of a string.

select   '  abd   '   from dualselect   trim('  abd   ')   from dual  

 

11. replace (str, search_str [, replace_str]): replace the search_str that appears in str with replace_str.

select replace('HELOVEYOU','HE','I') from dual

 

Ii. mathematical functions

1. round: returns a value that is rounded to the specified decimal place.

Select round (3000.926) from dual -- 3001 -- the fractional part is two select round (3000.926, 2) from dual -- 3000.93

 

2. The TRUNC function returns the processed value, which works very similar to the ROUND function,

-- This function does not round the portion before or after the specified decimal number, and all functions are truncated. Select trunc (3000.926) from dual -- 3000 select trunc (3000.926, 2) from dual -- 3000.92

 

3. mod (number1, number2) two values are mutually exclusive and return the remaining number. Operator for dividing number1 by number2

select mod(1600,300) from dual   --100

 

Iii. conversion functions

1. TO_CHAR (date, 'fmt'): a function of the character type. The conversion date is in the character format ('fmt ')

• Must be enclosed in single quotes and case sensitive
• Can contain any valid date format
• The fmt value can accommodate all valid numbers.

 

2. modify the current language environment to Chinese

alter session set nls_language='SIMPLIFIED CHINESE'

 

-- Enter the current system date in the format of yyyy-mm-ddselect sysdate from dualselect to_char (sysdate, 'yyyy-mm-dd') from dual -- enter the current system date, format: yyyy-mon-ddselect to_char (sysdate, 'yyyy-mon-dd') from dual -- enter the current system date in the format of yyyy-month-ddselect to_char (sysdate, 'yyyy-month-dd') from dual -- enter the current system date in the format of yyyy-mon-dd-dayselect to_char (sysdate, 'yyyy-mon-dd Day ') from dual -- enter the current system date in the format of yyyy-mon-dd-dyselect to_char (sysdate, 'yyyy-mon-dd-dy ') from dual -- enter the current system date in the format of yyyy-mon-dd-dy-dselect to_char (sysdate, 'yyyy/mon/dd/dy/D ') from dual -- english display of ddspth date -- enter the current system date in the format of yyyy-mon-ddspthselect to_char (sysdate, 'yyyy-mon-ddspth ') from dual -- 2010-4-seventeenth

 

3. Change it to an English environment (this statement is only valid for the current window, and reset when the window is closed)

Alter session set nls_language = AMERICAN;
-- Enter the current system date in the format of yyyy-mm-dd.
Select to_char (sysdate, 'yyyy-mm-dd') from dual
-- Enter the current system date in the format of yyyy-mon-ddselect to_char (sysdate, 'yyyy-mon-dd') from dual -- 2010-apr-17 -- enter the current system date, format: YYYY-MON-DDselect to_char (sysdate, 'yyyy-MON-dd') from dual -- 2010-APR-17 -- enter the current system date, format: YYYY-MONTH-DDselect to_char (sysdate, 'yyyy-MONTH-DD ') from dual
-- Enter the current system date in the format of yyyy-mon-dd-dayselect to_char (sysdate, 'yyyy-mon-dd-day') from dualselect to_char (sysdate, 'yyyy-mon-dd-day') from dual -- 2010-apr-17-SATURDAY -- enter the current system date in the format of yyyy-mon-dd-dyselect to_char (sysdate, 'yyyy-mon-dd-dy ') from dual -- 2010-apr-17-satselect to_char (sysdate, 'yyyy-mon-dd-DY') from dual -- 2010-apr-17-SAT

 

4. english display of ddspth regardless of Chinese and English environment dates

-- Enter the current system date in the format of yyyy-mon-ddspthselect to_char (sysdate, 'yyyy-mon-ddspth ') from dual -- 2010-apr-seventeenth -- hour minute second -- enter the current system date in the format of yyyy-mon-dd HH24: MI: SSselect to_char (sysdate, 'yyyy-mon-dd HH24: MI: ss') from dual -- enter the current system date in the format of dd-mon-yyyy SS: HH24: MI (the format is not sequential) -- Note: use the to_char function to convert the date ---> the character format has no sequence select to_char (sysdate, 'dd-mon-yyyy SS: HH24: MI ') from dual -- enter the current system date, format: yyyy-mon-dd HH24: MI: ss am (PM) -- add am pm -- in this format, there is no difference between am pm, the expression means the same select to_char (sysdate, 'yyyy-mon-dd HH24: MI: SS am') from dual select to_char (sysdate, 'yyyy-mon-dd HH24: MI: ss pm ') from dual select to_char (sysdate, 'yyyy-mon-dd HH24: MI: SS pm') from dual
-- Enter the current system date in the format of dd of month (19 of January 1, January) -- you can add a string in the format, and use "" to select to_char (sysdate, 'dd of month') from dual

 

5. Convert to_date (char, fmt) to date

The first parameter: The second parameter that meets the Date Format: Format (same as the format of the to_char function conversion date) alter session set nls_language = 'simplified CHINESE 'alter session set nls_language = AMERICAN;
-- Convert April month-18 to select to_date ('1970-August-18', 'yyyy-mon-dd') from dual; select to_date ('2014-18-2008 ', 'mon-dd-yyyy') from dual;

 

6. TO_CHAR (number, 'fmt'): a character-type function that converts a number to a character.

 

Iv. Date Functions

1. Calculate the number of months from month 1 to month 4 to month 10. Use the months_between (date1, date2) function.

Select months_between (to_date ('1970-January 1, 2008-01 ', 'yyyy-mon-dd'), to_date ('1970-January 10, 2008-10 ', 'yyyy-mon-dd') from dual
Select months_between (to_date ('1970-February 10 ', 'yyyy-mon-dd'), to_date ('1970-February 1-01 ', 'yyyy-mon-dd') from dual

 

2. How many months of difference are rounded to an integer?

Select round (months_between (to_date ('1970-August 10-10', 'yyyy-mon-dd'), to_date ('1970-August 1-01 ', 'yyyy-mon-dd') from dual -- returns the date calculated from month 4 to month 01 after six months -- use Add_months (date1, number) select add_months (to_date ('1970-January 1, 2008-01 ', 'yyyy-mon-dd'), 6) from dual

 

3. Calculate the date of the next week after the given date and week x -- use NEXT_DAY (date, 'day ')

Select next_day (to_date ('1970-February 1-01 ', 'yyyy-mon-dd'), 'weday') from dual

 

4. Calculate the date of the last day of the month of the specified date -- use last_day (date)

Select last_day (to_date ('1970-February 1-01 ', 'yyyy-mon-dd') from dual

 

Thanks and Regards

Reference: http://blog.163.com/jd_wangchunhe/blog/static/89945993201031915124106/