Oracle single row function, oracle

Oracle single row function, oracle

Single-row function syntax

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

Parameter description:

Function_name function name

Column name

Expression

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])

Note:

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