MySQL data processing function _ MySQL

MySQL data processing functions

Sometimes the data obtained from the database table needs to be processed. For example, replace lowercase letters with uppercase letters. This process can be performed on the client or the database. Database migration is more efficient. The database has corresponding data processing functions to process the data, but using special data processing functions in SQL will reduce the portability. Different DBMS systems have different data processing functions.

Most SQL statements support the following types of functions:

• Functions used to process text strings, such as deletion, filling, and case-sensitive conversion;
• A function used to perform arithmetic operations on numeric data;
• It is used to process date and time values and extract special components from these values, such as two functions with only difference in date;
• System functions that return the special information being used by the DBMS, such as user logon and version check;

Text processing functions

Example: convert a column of characters to uppercase characters

Common text processing functions

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

The Soundex () function is used to find a string with similar pronunciation. for example, if a spelling error occurs, a name with similar pronunciation is recorded in the table. in this case, it is not efficient to search by using LIKE or other matches. It is much easier to use SoundEX.

In the following example, the name is Y. Lee, but the name stored in the table is Y. Lie. if you use the matching search, 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)
AddTime ()	Add a time (hour, minute)
CurDate ()	Returns the current date.
CurTime ()	Returns the current time.
Date ()	Returns the date part of the date and time.
DateDiff ()	Calculate the difference between two dates
Date_Add ()	Highly flexible date computing functions
Date_Format	Returns a formatted date or string.
Day ()	Returns the number of days of a date.
DayOfWeek ()	Returns the day of the week for a date.
Hour ()	Returns the hour of a time.
Minute ()	Returns the minute of a time.
Month ()	Returns the month of a time.
Now ()	Returns the current date.
Second ()	Returns the second part of a time.
Time ()	Returns the time part of a date and time.
Year ()	Returns the year of a date.

Simple time comparison:

However, if there is time after order_date, the preceding SQL statement cannot match. Therefore, we can use the date function to compare only the date in the table, regardless of the time:

Search for orders for one month;

However, if you do not want to calculate the number of days in a month, you can do the following:

Numeric processing functions

Numeric processing functions only process numerical data. these functions are generally used for algebra, triangles, or ry operations.

Common 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 division operation.
Pi ()	Returns the circumference rate.
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.

The following is an example of tangent: