Oracle Use data manipulation functions

One 、 summary ：

Like almost all other computer languages ,SQL Support the use of functions to manipulate data , Functions are usually used to perform operations on data , For conversion and operation .

Two 、 The function classification

• Text functions ： Used to manipulate text strings （ for example , Trim or fill values , And converting values to upper and lower case ）.
• Numerical function ： Used to perform mathematical operations on numerical data （ for example ： Return absolute values and perform algebraic calculations ）.
• Date and time functions ： Used to operate date and time values , And extract specific parts from these values （ for example ： Return the difference between dates and check the validity of dates ）.
• System function ： Returns all specific DBMS Information about （ for example ： Return the user login information or check the version details ）.

1. Text manipulation function

Example ：

SELECT vend_name, Upper(vend_name) AS vend_name_upcase
FROM vendors
ORDER BY vend_name;

result ：

It can be seen that ,Upper() Convert text to uppercase . The following table lists some commonly used text manipulation functions .

--1、 Query character length bits 6 Number of numbers :length()
select * from table_name where length(column_name) = 6;

--2、 Convert a string to lowercase ：lower()
select lower(column_name) from table_name;

--3、 Fill the left side of the string ：lpad(String , Interception length , Added string ). Adding a string is not accurate , A more accurate statement is that String Intercept the string , If the intercept length is greater than String The length of , It's in String Add a string to the left of the to fill , If the third parameter is not specified , Then fill in the blanks .
select lpad('test',10,'ee') from dual;
-- result ：
    lpad('test',10,'ee')
----------------------------
eeeeeetest
————————————————------------

--4、 Trim white space from the left of the string ： LTRIM(c1,[,c2])

【 function 】 Delete the string that appears on the left 

【 Parameters 】C1  character string ,c2  Append string , Default is space 

【 return 】 Character 

select ltrim('abcddee','abc') from dual;
-- result ：
    ltrim('abcddee','abc')
---------------------------
ddee
————————————————-----------

--5、 Fill the right side of the string ：rpad(string, c1, [c2])
【 function 】 Fill the right side of the string 
【 Parameters 】string： String to be operated on  c1： String length after operation  c2： Operator for padding , Default is space 

--6、 Trim white space from the right side of the string ： RTRIM(c1,[,c2])
【 function 】 Delete the string that appears on the right 
【 Parameters 】c1： character string  c2： String to be deleted 
【 return 】 Character 
select rtrim('abcddee','ddee') from dual;
-- result ：
    ltrim('abcddee','ddee')
---------------------------
abc
————————————————-----------

--7、 Returns the sub characters in the string ：substring(string, index1, [,length1])
【 function 】 Returns the substring in the string 
【 Parameters 】string： The source string of the operation  index1： The substring is at the beginning of the source string  length1： Length of substring 
【 return 】 Character 
select substring('string', 0, 3) from dual;
-- result ：
    substr('string', 0, 3)
---------------------------
str
————————————————-----------

--8、 Returns the uppercase form of a string ：upper(string)
【 function 】 Returns the uppercase form of a string 
【 Parameters 】：string： The source string that needs operation 
【 return 】 Character 
select upper('string') from dual;
-- result ：
    upper('string')
---------------------------
STRING
————————————————-----------

Be careful ：

SOUNDEX It's an algorithm , Used to convert any text string into an alphanumeric pattern , Describe the voice representation of the text .SOUNDEX Similar pronunciation characters and syllables are considered , Allow pronunciation by string （ Instead of how they are entered ） To compare them . Even though SOUNDEX Is not a SQL Concept ,Oracle（ Like many others DBMS equally ） It still provides the right SOUNDEX Support for .

Here is an example of using Soundex() Example of a function .customers There is one in the table Coyote Inc. Customers , Contact name is Y. Lee. however , If there is an input error , The contact person is actually Y. Lie, What will happen ？ obviously , Searching by the correct contact name will not return any data , As shown below ：

SELECT cust_name, cust_contact
FROM customers
where cust_contact = 'Y. Lie'

Now try using Soundex() Function performs the same search , To match pronunciation similar to Y. Lie Names of all contacts .

SELECT cust_name, cust_contact
FROM customers
WHERE Soundex(cust_contact) = Soundex('Y Lie');

In this example ,WHERE Clause use Soundex() Function cust_contact Column values and search strings are converted to their SOUNDEX value . because Y. Lee and Y. Lie Sound alike , Their SOUNDEX The value will match , therefore WHERE Clause will correctly filter out the desired data .

2. Date and time manipulation functions

The following table lists some commonly used date and time operation functions

-- Use cases ;
--1、 Query the current system time plus 3 Time after month ：add_month()
select add_month(sysdate,3) from dual;

--2、 Inquire about 6 Monthly data ：extract()
select * from extract(month from date1);

--3、 Query the last day of the month :last_day()  Hypothetical bit 2022/6
select last_day(sysdate) from dual;
--> result ：2022/06/30

--4、 Query the number of months between two times ：months_between()
select months_between(to_date('2014-3-21','yyyy-mm-dd'), to_date('2014-1-10','yyyy-mm-dd')) months from dual;
 
    MONTHS
----------
2.3548387
————————————————

--5、 Returns the day after the specified date :next_date()
select sysdate from dual;

SYSDATE
-------------------
2022-07-13 19:30:26

--6、 Returns the current date and time :sysdate()
select sysdate from dual;

--7、 Convert a string to a date :to_date()
select * from table_name where date1 = to_date('2022/07/13', 'yyyy/mm/dd');

Be careful ： The most important Extract() function

One thing to remember ： Data formatting can be tricky . After all ,”2022-03-04“ It refers to that day ？03 Is the month and 04 Is it heaven ？ Or vice versa ？ therefore , Whenever a date is provided to Oracle, Must clearly explain how it is formatted .

SELECT cust_id, order_num
FROM orders
WHERE order_date = TO_DATE('2015-02-01', 'yyyy-mm-dd');

More flexible date operations require the ability to extract specific parts of the date or time . here ,Extract() Functions come in handy . seeing the name of a thing one thinks of its function ,Extract() Used to extract some parts of the date and time , It is allowed to process only YEAR、MONTH、DAY、HOUR、MINUTE and SECOND.

Here is another solution to the previous problem （ This solution does not require you to figure out how many days there are in a month , Or worry about the leap year 2 month ）：

SELECT cust_id, order_num
FROM orders
WHERE Extract(Year FROM order_date) = 2015
   AND Extract(Month FROM order_date) = 2

analysis ：

Extract(Year) Returns the year in the date . Similarly ,Extract(Month) Return the month in the date . therefore ,WHERE Extract(Year FROM order_date) = 2015 AND Extract(Month FROM order_date) = 2 Will retrieve order_date stay 2015 Years and 2 All lines of the month .

3. Numeric operation function

Numerical manipulation functions are of course used to manipulate numerical data . These functions only need to be used in algebra , Trigonometric or geometric operations , Therefore, it is not as commonly used as string or date and time manipulation functions .

The following table lists the commonly used numerical operation functions ：