Oracle single row function, oracle

Source: Internet
Author: User
Tags control characters

Oracle single row function, oracle

Single-row function syntax

Function_name (column | expression, [arg1, arg2,...])

Parameter description:

Function_name function name

Column name


Arg1, arg2,... Parameters

Single Row function category:

  • Character function: receives character input and returns a character or value.

  • Numeric function: receives data input and returns a value.

  • Date functions: operate on date data

  • Conversion Function: converts a 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 ename like '% A %'; -- upper () converts the string into uppercase select ename from emp where upper (ename) like '% A %'; -- initcap () converts the first letter of A word to uppercase, And the other letters to lowercase select initcap ('Hello World') from dual; select initcap (ename) from emp; -- concat () connects the string to select concat ('hello', 'World') from dual; select 'hello' | 'World' from dual; -- substr () string truncation select ename, substr (ename, 1, 2), substr (ename,-3, 3) from emp; -- length () calculates the length of select length ('abcdef '), length ('20140901') from dual; -- replace () string replace select substr ('hello', 3, 2) substring, length ('hello') length, replace ('hello', 'l', 'x') from dual; -- chr () converts numbers into their corresponding ascii characters select chr (65), chr (66) from dual; -- ascii () converts the corresponding characters into the numbers corresponding to the ascii code select ascii ('A'), ascii ('B') from dual;

Numeric Functions

-- Round () Rounding. By default, it is precise to a single digit. It specifies the number of digits after the decimal point. select round (23.652) from dual; select round (23.652, 1) from dual; select round (23.652,-1) from dual; -- trunc () truncates decimal places and truncates decimal places or integers Based on the specified precision (No rounding operation is performed) select trunc (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 dual; -- mod () select mod (10, 3) from dual for the remainder of a number;

Date Functions

Oracle provides many date-related functions, including addition and subtraction.

Some rules should be followed for addition and subtraction of dates

Date-number = Date

Date + number = Date

Date-date = number indicates the number of days between two dates

-- Display the number of weeks for employees in department 10 to enter the company. select empno, ename, deptno, round (sysdate-hiredate)/7) from emp where deptno = 10; -- Months_between () returns the number of months separated by two given dates. query the number of months for employees in 10 departments. 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 select empno, ename, hiredate, add_months (hiredate, 5), add_months (hiredate,-1) from emp; -- next_day () select sysdate, next_day (sysdate, 'monday') from dual; -- last_day () select last_day (sysdate) from dual; select last_day (to_date ('2017-02-13 ', 'yyyy-mm-dd') from dual;

Conversion functions

To_char () is used with format control characters, which are case-insensitive.

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

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

Day: D. The day is two digits. Therefore, it should be written as DD or dd.

-- To_char () converts a number or date to a string -- converts the system's date format display method so that it is displayed according to Chinese date habits, that is, "YYYY-MM-DD" select empno, ename, to_char (hiredate, 'yyyy-mm-dd') from emp; -- fm removes 0 select empno, ename, to_char (hiredate, 'fmyyyy-mm-dd') from emp; -- split the employment date by year, month, or day. select empno, ename, to_char (hiredate, 'yyyy') year, to_char (hiredate, 'mm') month, to_char (hiredate, 'dd') dayfrom empselect empno, ename, to_char (hiredate, 'yyyy ') | 'Year' | to_char (hiredate, 'mm') | 'month' | to_char (hiredate, 'dd ') | 'day' from emp -- to_char () format the number select empno, ename, to_char (sal, '20170101') from emp; select empno, ename, to_char (sal, '200') from emp; select empno, ename, to_char (sal, '$000,000,000') from emp; -- to_number () convert the current fixed format string to the number select sal from emp where sal> to_number ('$1,250.00', '$9,999.99'); select to_number ('123 ') + to_number ('123') from dual; -- to_date () converts the string in the current fixed format to a date -- query the select ename of the employee who joins the company after 1981-1-1, hiredate from empwhere hiredate> = to_date ('2017-1-1 ', 'yyyy-mm-dd'); select ename, hiredate from empwhere hiredate> = date '2017-1-1 ';

Common functions

Decode (). This function is similar to the If... elseif... else statement and is used to judge multiple branches.

Syntax: decode (col/expression, search1, result1 [, search2, result2,…] [, Default])


Col/expression: column name or expression

Search1, search2 ...... SearchI: multiple possible conditions

Result1, result2 ...... Resulti: return value when the corresponding shearch is satisfied

-- Nvl () is used to process fields with null values -- calculate the annual salary (including bonus) of each employee 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; -- Judge select decode, 'content 1', 2, 'content 2', 3, 'content 3') from dual; select empno employee number, ename employee name, decode (job, 'cler ', 'salesman', 'manager', 'manager', 'analyst', 'analysts', 'President ', 'President') from emp

Link: A simple oracle query that limits query and sorting

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

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: and provide relevant evidence. A staff member will contact you within 5 working days.