Oracle Note (c) Single-line function

-function functions like a black box (see the inside of the structure), there are parameter return values, you can do a certain function for us.

-Single line This function evaluates each row in the result once, each row returns a result, and the single-line concept differs from the grouping function.

Single-line functions are divided into the following five categories: Character functions, numeric functions, date functions, conversion functions, general functions;

One, character type function

---character processing- case Conversion

Example: Write a SQL that will ' I love SQL ' converted to full capitalization, all lowercase and capitalized in uppercase form.

SELECT UPPER (' I Love SQL '), LOWER ("I Love SQL"), Initcap (' I Love SQL ')

from dual;

-oracle is a bit more troublesome, even if you want to validate the string, you must write the full SQL statement, so in the Oracle database for user query convenience, so dedicated to provide a "dual" virtual table

-dual is a table under the SYS user, the data in the dual table has no meaning, its function is to complete the SQL statement

-Useful for querying when you don't know the case

---contact (parameter 1, parameter 2)- string Connection

|| Can connect more than two strings, contact can only connect two

SELECT Contact (' I love ', ' Sql ')

from dual;

-Nested functions, in theory, single-line functions can be nested unlimited multilayer, depending on the computing power of the computer, the memory CPU is large enough

-Nested execution order, innermost outward, executed sequentially, inside result to be outside parameter

SELECT Contact ("I ', ' Love '), ' Sql ')

from dual;

|| General use of work | | Connection, this is to explain the concept of nesting

---SUBSTR (source string, m,n) (substring) returns n characters from the source string starting with the first m character

-if n is omitted, the remaining string is truncated to the end

-M can also be a negative number, ze from right to left

-sql starting from 1, a space is counted as a

Example: request to intercept the last three letters of each employee's name

SELECT Ename,substr (ename,-3)

from EMP;

---LENGTH (string)

Example: Querying the length of each employee's name

SELECT ename,

LENGTH (ename)

from EMP;

---INSTR (source strings, strings, M,n)-Returns the substring in the source string, starting with the first m character and the position of the nth occurrence

-If no substring location is found, 0 is returned

-m,n can be omitted, default is 1

Example: Query the EMP table name contains at least an E employee information, not like, without wildcards, how to write.

SELECT *

From EMP

WHERE INSTR (ename, ' E ') >0;

---lpad (source string, N, substring)-fills the right side of the source string with a substring of n length

Secect lpad (' SQL ', 9, ' * ')

from dual;

---TRIM (character from source character)-truncate the character specified by the source string header (tail)

SELSCT TRIM (' L ' from ' Levela ')

from dual;

SELSCT TRIM (BOTH ' L ' from ' Levela ')

from dual;

SELSCT TRIM (Leading ' L ' from ' Levela ')

from dual;

SELSCT TRIM (TRAILING ' L ' from ' Levela ')

from dual;

-both is the default

-Rarely use the trim function, the second algorithm commonly used

---TRIM (String)-the space to truncate the kinsoku

SELECT TRIM (' d Tom ')

from dual;

-a way to intercept trailing blanks, commonly used in web development, when users login to set the password

-Used to add a space, this can be cut off with trim head blanks, and then verify

---replace (source string, s,t)-change s to t

SELECT REPLACE (' a B c d ', ' a ', ' e ')

from dual;

Think: The count in the database all starts from 1, some parameters can also be set to a negative number, indicating that the point specified by the later start

Second, the number function

---ROUND (number, N)-rounds the given number to the N-bit after the decimal point

---TRUNC (number, N)-truncates the given number to the N-bit after the decimal point

---MOD (m,)-returns the remainder after m divided by n

-Not a lot of familiar apps

-Extended

---ABS (digital)-Take absolute value

SELECT ABS (-5)

from dual;

Third, date function

-sysdate-return (server) system time (back to the server-side time, or the client's time, see where the table is stored, the table is saved on the server side, the time of the server is returned)

SELECT sysdate

from dual;

- When doing outsourcing, note that the server may not be in China, to convert

---months_between (date 1, date 2)-Returns the number of months between two dates, larger in front, smaller after, returned as integers, later larger

Example: Querying the EMP table how many months has the employee been working today?

SELECT Ename,hiredate,months_between (sysdate,hiredate)

from EMP;

Example: Calculating your own age and rounding to two decimal places

SELECT ROUND (Months_between (sysdate, ' January-January -200 ')/12,2) age

from dual;

---add_months (date, N)-Returns a date after n months

-If you are given a date that is the last day of a month, the result is the last day of the corresponding month.

-For the January 30, number 31st, the last day of February is returned.

---next_day (date, ' Week X ' |n)-Returns the date of the next week in the week after the given date

-Within a week

-1 for Sunday, 2 for Monday, etc...

---last_day (date): Find the last day of the specified date

Example: Finding the date of the last day of the month

SELECT Last_day (sysdate)

from dual;

---EXTRACT (year|month|day from date)

SELECT EXTRACT (Day from Sysdate)

from dual;

-Basic No, extract is available in Oracle 9i version

-use restricted, only to extract information of the Year of the Sun and moon

Iv. Type Conversion Functions

1. Implicit type conversions

-Reasons for not recommending the use of dietary type conversions

--poor readability

--implicit type conversion reduces operational efficiency

--oracle does not promise to change the rules of implicit type conversions in the next release

2. Display type conversion-function

Dates and characters can be converted to each other, and numbers and characters can be converted to each other

---to_char (date | number, ' format string ')

Example: Converting a system date to a string, format 2015/7/23

SELECT to_char (sysdate, ' yyyy/mm/dd ')

From dual;//results A 0 more, this 0 is called leading zero

- How to remove the leading 0-fm

SELECT to_char (sysdate, ' fmyyyy-mm-dd ')

from dual;

Example: Converting system time to string, format xx:xx:xx

SELECT to_char (sysdate, ' HH:MI:SS ')

from dual;

-----------------------------------------------------to Be Continued---------------------------------------------------------------- ----------------------

Oracle Note (c) Single-line function