SQL Common functions

String functions:

String connections: CONCAT () and Concat_ws ()

Concat primarily implements string concatenation as a string:

SELECT CONCAT ('My','S','QL')

The Concat_ws () function is CONCAT with Separator and is a special form of the CONCAT () function. The function Concat_ws () is defined as follows:
Concat_ws (Sep,s1,s2,... SN)
Compared to concat (), the above function has an sep parameter that represents the delimiter, that is, not only joins the other parameters that are passed in, but also splits the individual strings through the delimiter. The delimiter can be a string, or it can be a different parameter. if the delimiter is null, the returned result is null. The function ignores null values after any delimiter parameters.

Compare string size function strcmp ()

STRCMP (STR1,STR2)
The above functions are used to compare string parameters str1 and str2, if the parameter str1 is greater than str2, the result 1 is returned, and if the parameter str1 is less than str2, the result 1 If the parameter str1 equals str2, the result 0 is returned.

SELECT STRCMP ('abc','abd'),    STRCMP ('  ABC','abc'),    STRCMP ('ABC  ','ABB');

Gets the string length function, Length (), and number of characters function char_length ()

LENGTH (str)
The above function gets the length of the passed parameter str. The space length is calculated.

The function char_length () is defined as follows:

Char_length (str)

Implements the letter case conversion function upper () and the number of characters function lower ()
In MySQL software, all letters of a string can be converted to uppercase by using the functions upper () and UCase (). To view the Help document, the function upper () is defined as follows:
UPPER (S)
The above function converts all the letters in the passed string object s into uppercase letters.
In addition to upper (), the implementation of the letter capitalization conversion function can also be implemented by the function UCase (), which is defined as follows:
UCASE (S)

Find string

1. The function that returns the position of the string find_in_set () can get the position of the matched string in the MySQL software through the function Find_in_set (). The position is evaluated starting with the first string.

2. function that returns the position of the specified string field ()
FIELD (Str,str1,str2 ...) the above function will return the position of the first string that matches the string str. Returns 0 if not found;

To intercept substrings from an existing string

1. Truncate substring from left or right

 Left (str, num)  Right (str, num)

2. Intercepts the specified position and length substrings

SUBSTRING (str, num,len) MID (str, num,len)

Remove the leading and trailing spaces of a string

LTRIM (), function RTrim (), and function trim ().
1. Remove space at start of string
LTRIM (str)

2. Remove space at end of string
RTRIM (str)
3. Remove string and trailing spaces
TRIM (str)

Replace string

1. Use the Insert () function

INSERT (str, POS,len, newstr)) SELECT ' This is the MySQL database management system ' String, INSERT (' This is MySQL database management system ',3,5,'Oracle ') Converted string;

(2) Execute SQL function Insert (), when the replacement start position is greater than the string length , the specific SQL statement is as follows:

SELECT ' This is the MySQL database management system ' string, Char_length (' This is the MySQL database management system ') string character number,
INSERT (' This is MySQL database management system ', 16,15, ' Oracle ') converted string;
(3) Execute the SQL function insert (), and when the length of the replacement is greater than the length of the string remaining in the original string , replace all from the starting position with the following SQL statement:
SELECT ' This is the MySQL database management system ' string, char_length (' MySQL database management system ') the number of characters remaining,
INSERT (' This is MySQL database management system ', 3,15, ' Oracle ') converted string;
2. Use the Replace () function
REPLACE (STR,SUBSTR,NEWSTR))
SELECT ' This is the MySQL database management system ' original string,
Replace (' This is MySQL database management system ', ' MySQL ', ' Oracle ') after replacing the string;

Numeric functions:

Get random number

SELECT rand (), RAND (), RAND (3), Rand (3);

functions that get integers
Ceil (x)-Rounding up

Floor (x)--Rounding down

Intercept numeric functions

TRUNCATE (x, y)

Y-bit precision is reserved for x, and the precision is minimized when y=-1.

Rounding function
ROUND (x)
ROUND (x, y)

Date-time Functions:

SELECT now () mode, Current_timestamp () TIMESTAMP mode,
LocalTime () localtime mode, sysdate () systemdate mode;

Display dates and times in various ways
SELECT now () Current time,
Unix_timestamp (now ()) UNIX format,
From_unixtime (now ()) normal format; unix_timestamp;

SELECT now () Current time,
Unix_timestamp () UNIX format,
Unix_timestamp (now ()) UNIX format;

You can use From_unixtime to convert an integral type to a datetime, which calculates the number of seconds that have been calculated since 1970.

System Information functions:

Get MySQL System Information

SELECT
Version () number,
Database () name,
User () Username;

Other:

SQL Common functions