MySQL Data processing function

The data processing function sometimes obtains from the database table to take some processing. If you replace the lowercase letter with the corresponding uppercase letter. This process can be done on the client or on the database. is more efficient on the database. There are corresponding data processing functions in the database to process the data, but using special data processing functions in SQL reduces their portability. Different DBMS systems have different data processing functions.
Most SQL supports the following types of functions

• Functions for working with text strings, such as deleting, populating values, converting case;
• A function for arithmetic operations on numeric data;
• Used to process date and time values and extract special components from these values, such as a function of two date-only difference;
• System functions that return the special information that the DBMS is using, such as returning the user to log in and checking the version;

Text Processing functions

Example: Converting a column character to uppercase characters

Common Text Processing functions

Function	Description
Left ()	Returns the character to the left of the string
Length ()	Returns the length of a string
Locate ()	Find a substring of a string
Lower ()	Convert a string to lowercase
LTrim ()	Remove the left space of the string
Right ()	Returns the character to the right of the string
RTrim ()	Remove the blanks to the right of the string
Soundex ()	Returns the Soundex value of a string
SubString ()	Returns the character of a substring
Upper ()	Convert a string to uppercase

The Soundex () function, to find a similar pronunciation of a string, such as due to spelling errors, recorded in the table pronunciation similar names, if you use like or other matching search is not efficient. Using Soundex () is a lot easier.

As the following example, the name is Y.lee, but the table contains the name Y.lie, if you use a matching search will find that the search is not, you can use the Soundex () function:

Date and time processing functions common date and time processing functions

function	description
adddate ()	Add a date (days, weeks)
AddT IME ()	Add a time (hours, minutes)
curdate ()	return current date
curtime ()	return current time
Date ()	returns the datetime part of the date time
DateDiff ()	calculates the difference of two dates
date_add ()	highly flexible date calculation function
date_format	Returns a formatted date or string
Day ()	returns the number of days of a date part
DayOfWeek ()	returns the corresponding day of the week for a date
Hour ()	returns the hour portion of a time
Minute ()	returns the minute portion of a time
Month ()	returns the month portion of a time
Now ()	returns the current date
Second ()	returns the second part of a time
time ()	Returns a date time part of a datetime
Year ()	Returns the years of a date part

Simple Time Comparison:

But if there is time behind the order_date, then the SQL statement above will not match, so we can use the DATE function to compare only the dates in the table and ignore the time:

Search for a one-month order;

But sometimes don't want to calculate how many days this month, you can achieve the following:

Numeric processing functions numeric processing functions handle only numeric data, which are generally used for algebraic, triangular, or geometric operations. Commonly used numeric processing functions are as follows

Function	Description
Abs ()	Returns the absolute value of a number
Cos ()	Returns the cosine of an angle
EXP ()	Returns the exponential value of a number
Mod ()	Returns the remainder of the operation
Pi ()	return pi
Rand ()	Returns a random number
Sin ()	Returns the sine of an angle
SQRT ()	Returns the square root of a number
Tan ()	Returns the tangent of a number

Here is an example of a tangent: