Oracle Single-line function

Source: Internet
Author: User
Tags chr


Syntax for single-line functions

Function_name (Column|expression, [Arg1, Arg2, ...])


Parameter description:

Function_name Function Name

Column Name

Expression Expressions

Arg1,arg2,... Parameters


Single-line function classification:

    • Character functions: Receive character input and return characters or values

    • Numeric function: Accepts data entry and returns a value

    • Date functions: Manipulating date-based data

    • Conversion functions: Converting from one data type to another data type

    • General function: NVL function DECODE function

character Functions


--Lower () converts the string to lowercase select ename from emp where lower (ename) like '%a% '; select ename from emp where ename like '%a% ' or ENA Me like '%a% '; --Upper () converts the string to uppercase select Ename from EMP where Upper (ename) like '%a% ';--initcap () capitalizes the first letter of the word, and the remaining letters to lowercase select initcap (' H Ello World ") from Dual;select Initcap (ename) from emp;--concat () Connect strings to select concat (' Hello ', ' world ') from Dual;select ' Hello ' | | ' World ' from dual;--substr () string intercept select Ename, substr (ename, 1, 2), substr (ename, -3, 3) from emp;--length () calculated lengths Select Length (' abcdef '), Length (' 12345678 ') from the dual;--replace () string replaces the Select substr (' Hello ', 3, 2) with substring lengths (' Hello '), rep Lace (' Hello ', ' l ', ' X ') from dual;--Chr () converts the number to its corresponding ASCII character, select Chr, Chr ($) from dual;--ASCII () converts the corresponding character to its ASCII code counterpart The number select ASCII (' A '), ASCII (' B ') from dual;


Numeric Functions


--round () rounded, the default precision to the digit, specify the precision to the decimal point after several select round (23.652) from dual; Select Round (23.652, 1) from Dual;select round (23.652,-1) from dual;--trunc () truncates decimal places, truncates decimals or integers by specified precision (without rounding) select Tru NC (25.46, 1) from Dual;select round (25.46, 1) from Dual;select trunc (25.46,-1) from Dual;select round (25.46,-1) from Dua l;--mod () to a number of remainder select mod (3) from dual;


Date Function


Oracle provides a number of functions related to date operations, mainly including add and subtract.

There are some rules to follow when adding and subtraction dates.

Date-Number = Date

Date + Number = Date

Date-date = number indicates the number of days separated by two dates

--Shows the number of weeks that the 10-door employee entered the company select Empno, ename, Deptno, round ((sysdate-hiredate)/7) from emp where deptno = 10;--Months_between  () returns the number of months between two given dates--the number of months that the employee working on the 10 department is queried select Deptno, empno, ename, Months_between (Sysdate, HireDate) from emp where deptno = 10;select Deptno, Empno, ename, round (Months_between (Sysdate, HireDate)) from emp where deptno = 10;--add_months () returns the given Date plus specified number of months after select Empno, ename, HireDate, Add_months (HireDate, 5), Add_months (HireDate,-1) from emp;--next_day () Specify date The next specified day of the week. Select Sysdate, Next_day (sysdate, ' Monday ') from dual;--Last_day () find the last date in the month of the given date select Last_day (sysdate) From Dual;select last_day (to_date (' 2015-02-13 ', ' Yyyy-mm-dd ')) from dual;

Conversion Functions


To_char () Use the format control character, the format control character is not case-sensitive letter

Year: Y, the year is four digits, it should be written as: yyyy or YYYY

Month: M, the month is two digits, it should be written as: mm or mm

Day: D, the day is two digits, it should be written as: DD or DD

---To_char () converts a number or date into a string--transforms the system's date format display so that it is displayed according to Chinese date habits, i.e. "yyyy-mm-dd" select Empno, ename, To_char (HireDate, ' Yyyy-mm-dd ') from emp;--FM minus single digit day, month front 0select empno, ename, To_char (hiredate, ' Fmyyyy-mm-dd ') from emp;--on entry date by year, month, day into Row Split Select Empno,ename,to_char (hiredate, ' yyyy ') year, To_char (HireDate, ' mm ') month, To_char (hiredate, ' DD ') DAYF Rom Empselect empno,ename, to_char (hiredate, ' yyyy ') | | ' Year ' | | To_char (hiredate, ' mm ') | | ' Month ' | | To_char (hiredate, ' dd ') | | ' Day ' from emp--to_char () Formats the number select Empno,ename,to_char (Sal, ' 999,999,999 ') from Emp;select Empno,ename,to_char (Sal, ' 000,000,000 ') from the Emp;select Empno,ename,to_char (Sal, ' $99,999 ') from emp;--to_number () converts the current fixed-format string to a digital select Sal From EMP where Sal>to_number (' $1,250.00 ', ' $9,999.99 '), select To_number (' + ') + to_number (' n ') from dual;--to_ Date () Converts a string of the current fixed format to a date-queries the employee who entered the job after 1981-1-1, select Ename, hiredate from Empwhere hiredate >= to_date (' 1981-1-1 ', ' yyyy -mm-dd '); select Ename, HireDate from Empwhere hiredate ≫= date ' 1981-1-1 '; 


General Functions


Decode (), this function has a statement similar to If...elseif...else, used to judge multiple branches

Syntax: decode (COL/EXPRESSION,SEARCH1,RESULT1[,SEARCH2,RESULT2,......] [, default])

Description

Col/expression: for a column name or an expression

Search1,search2......searchi: for a variety of possible conditions

Result1,result2......resulti: The return value when the corresponding Shearch is satisfied

--NVL () processing of fields with null values--for each employee's annual salary (including bonuses) select Empno, ename, (Sal+comm) *12 from Emp;select EMPNO,ENAME,SAL,NVL (comm,0) new_ Comm, (SAL+NVL (comm,0)) *12 incom from emp;--docode () Branch Judging Select decode (2,1, ' content 1 ', 2, ' Content 2 ', 3, ' Content 3 ') from Dual;select  empno Employee number, ENAME employee name,   decode (Job, ' clerk ', ' salesman ', ' salesman ', ' Sales person ',              ' manager ', ' manager ', ' analyst ', ' analyst ', ' President ', ' President ') works from EMP


Links: Oracle Simple query, limited query and sorting


Oracle Single-line function

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.