Oracle Series: (8) Single-line function

single-line function : Only one parameter input, only one result output

Multiline function or grouping function : can have multiple parameter inputs, only one result output

Test lower/upper/initcap function, use dual dummy table

Select lower (' Www.BAIdu.COM ') from Dual;select Upper (' Www.BAIdu.COM ') from Dual;select initcap (' Www.BAIdu.COM ') from dual;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/86/F0/wKiom1fPHRLhHJyGAAAeysNLpt8939.png "title=" 001. PNG "alt=" Wkiom1fphrlhhjygaaaeysnlpt8939.png "/>

Test concat/substr function, starting with 1, representing characters, whether in English or Chinese

Select concat (' Hello ', ' hello ') from dual;

Select concat (' Hello ', ' hello ', ' world ') from dual; error

Select ' Hello ' | | ' Hello ' | | ' World ' from dual;

Select concat (' Hello ', concat (' Hello ', ' world ')) from dual;

Select substr (' Hello hello ', 5,3) from dual;

5 means starting from the beginning of the first character, 1, Chinese and English unified processing

3 means to take several characters consecutively

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/86/EF/wKioL1fPHmfBRxAXAAAKN6pXAxQ699.png "title=" 002. PNG "alt=" Wkiol1fphmfbrxaxaaakn6pxaxq699.png "/>

Test the LENGTH/LENGTHB function, encoded as UTF8/GBK, a Chinese account of 3/2 bytes in length, one byte in English

Select Length (' Hello Hello ') from dual; Select LENGTHB (' Hello hello ') from dual;

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/86/EF/wKioL1fPHxmiubfyAAAUvIAQrUs559.png "title=" 003. PNG "alt=" Wkiol1fphxmiubfyaaauviaqrus559.png "/>

Test the instr/lpad/rpad function to find the first occurrence from left to right, starting with 1

Select InStr (' HelloWorld ', ' O ') from dual;

Note: It is not found to return 0, case sensitive

Select Lpad (' Hello ', ten, ' # ') from Dual;select rpad (' Hello ', ' Ten, ' # ') from dual;

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/86/F0/wKiom1fPIEXhSQCAAAAeQopPTkY387.png "title=" 004. PNG "alt=" Wkiom1fpiexhsqcaaaaeqopptky387.png "/>

Testing the trim/replace function

Select Trim ("from" he ll ') from Dual;select replace (' Hello ', ' l ', ' l ') from dual;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/86/F0/wKiom1fPIP-S9IXyAAARTLpUnK0556.png "title=" 005. PNG "alt=" Wkiom1fpip-s9ixyaaartlpunk0556.png "/>

Testing the round/trunc/mod function on a numerical type

Select Round (3.1415,3) from Dual;select trunc (3.1415,3) from Dual;select mods (10,3) from dual;

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M02/86/EF/wKioL1fPIZahSSGWAAAXhydNrAM118.png "title=" 006. PNG "alt=" Wkiol1fpizahssgwaaaxhydnram118.png "/>

Current Date:

Select Sysdate from dual;

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/86/EF/wKioL1fPIiTCOO0VAAAHT1HbNPY405.png "title=" 007. PNG "alt=" Wkiol1fpiitcoo0vaaaht1hbnpy405.png "/>

Test round action on date type (month)

Select round (sysdate, ' month ') from dual;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/86/F0/wKiom1fPInbBlF5KAAAIOlR6SEM294.png "title=" 008. PNG "alt=" Wkiom1fpinbblf5kaaaiolr6sem294.png "/>

Test round action on date type (year)

Select round (Sysdate, ' year ') from dual;

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/86/F0/wKiom1fPIsKRAhy8AAAHEYU1AWw877.png "title=" 009. PNG "alt=" Wkiom1fpiskrahy8aaaheyu1aww877.png "/>

Test trunc Action on date type (month)

Select Trunc (sysdate, ' month ') from dual;

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/86/EF/wKioL1fPIwGja145AAAIMQh_QiU778.png "title=" 010. PNG "alt=" Wkiol1fpiwgja145aaaimqh_qiu778.png "/>

Test trunc Action on date type (year)

Select Trunc (Sysdate, ' year ') from dual;

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/86/F0/wKiom1fPI0Xho_6pAAAIMKO1k08355.png "title=" 011. PNG "alt=" Wkiom1fpi0xho_6paaaimko1k08355.png "/>

Show yesterday, today, tomorrow's date, date type +-numeric = Date type

Select Sysdate-1 "Yesterday", sysdate "Today", sysdate+1 "tomorrow" from dual;

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/86/EF/wKioL1fPI_ziwFdQAAAMMCLr7wA872.png "title=" 012. PNG "alt=" Wkiol1fpi_ziwfdqaaammclr7wa872.png "/>

Show employee approximate seniority in year and month, date-date = value, assuming: One year is calculated in 365 days, January in 30 days

Select Ename "Name", round (sysdate-hiredate,0)/365 "seniority" from EMP;

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/86/F0/wKiom1fPJMnghMXzAAAgF7OgbO8016.png "title=" 013. PNG "alt=" Wkiom1fpjmnghmxzaaagf7ogbo8016.png "/>

Use the Months_between function to calculate exactly how many months until the end of the year

Select Months_between (' 3 January-December -16 ', sysdate) from dual;

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/86/EF/wKioL1fPJTai5POEAAAKNEQZ2VQ333.png "title=" 014. PNG "alt=" Wkiol1fpjtai5poeaaakneqz2vq333.png "/>

Use the Months_between function to display employee seniority in a precise monthly format

Select Ename "Name", Months_between (sysdate,hiredate) "accurate monthly seniority" from EMP;

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/86/EF/wKioL1fPJXiwZHRsAAAinogF5_w664.png "title=" 015. PNG "alt=" Wkiol1fpjxiwzhrsaaainogf5_w664.png "/>

Test the Add_months function, what's the number for the next month today?

Select Add_months (sysdate,1) from dual;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/86/F0/wKiom1fPJbfCXwHLAAAIWWW2JjQ613.png "title=" 016. PNG "alt=" Wkiom1fpjbfcxwhlaaaiwww2jjq613.png "/>

Test the Add_months function, what's the number today?

Select Add_months (sysdate,-1) from dual;

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/86/EF/wKioL1fPJgnT0_fuAAAHHOMF-WE744.png "title=" 017. PNG "alt=" Wkiol1fpjgnt0_fuaaahhomf-we744.png "/>

Test the Next_day function, starting today, the next one weeks three is the number of "Chinese platform"

Select Next_day (sysdate, ' Wednesday ') from dual;

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/86/F0/wKiom1fPJpWg25M9AAAH4SsM0A8684.png "title=" 018. PNG "alt=" Wkiom1fpjpwg25m9aaah4ssm0a8684.png "/>

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M02/86/EF/wKioL1fPJ4zBIgcnAAB73ws_sP8813.png "Title=" 020. PNG "alt=" Wkiol1fpj4zbigcnaab73ws_sp8813.png "/>

Test the Next_day function, starting today, the next one weeks three is the number of "Chinese platform"

Select Next_day (Next_day (sysdate, ' Wednesday '), ' Wednesday ') from dual;

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/86/F0/wKiom1fPJvDRbEiFAAAJjj9jRsI650.png "title=" 019. PNG "alt=" Wkiom1fpjvdrbeifaaajjj9jrsi650.png "/>

Test the Next_day function, starting today, the next Sunday in the next Wednesday is the number of "Chinese platform"

Select Next_day (Next_day (sysdate, ' Wednesday '), ' Sunday ') from dual;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/86/EF/wKioL1fPJ8mDWaogAAAJ0KKZ-lc092.png "title=" 021. PNG "alt=" Wkiol1fpj8mdwaogaaaj0kkz-lc092.png "/>

Test the Last_day function, what number is the last day of the month

Select Last_day (sysdate) from dual;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/86/F0/wKiom1fPKAeAg4y5AAAH4EV3IwU532.png "title=" 022. PNG "alt=" Wkiom1fpkaeag4y5aaah4ev3iwu532.png "/>

Test the Last_day function, what number is the second day of the month?

Select Last_day (sysdate)-1 from dual;

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/86/EF/wKioL1fPKDPy1x-3AAAGx6ABI5E113.png "title=" 023. PNG "alt=" Wkiol1fpkdpy1x-3aaagx6abi5e113.png "/>

Test the Last_day function, what number is the last day of the next one months?

Select Last_day (add_months (sysdate,1)) from dual;

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M02/86/F0/wKiom1fPKHjS65efAAAIAJwovqI861.png "title=" 024. PNG "alt=" Wkiom1fpkhjs65efaaaiajwovqi861.png "/>

Test the Last_day function, what number is the last day of one months

Select Last_day (add_months (sysdate,-1)) from dual;

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/86/EF/wKioL1fPKKrCNaIKAAAH2Ioh1ow806.png "title=" 025. PNG "alt=" Wkiol1fpkkrcnaikaaah2ioh1ow806.png "/>

Attention:

1) Date-date = number of days

2) Date +-days = Date

Oracle Series: (8) Single-line function