My MYSQL Study Notes (4) -- functions and mysql Study Notes Functions

My MYSQL Study Notes (4) -- functions and mysql Study Notes Functions

Numeric Functions
1. Evaluate the remainder function MOD (X, Y)

MOD (X, Y) returns the remainder of x after division by y. MOD () also applies to values with decimals. It returns the exact remainder after division.

2. Function TRUNCATE (X, Y)

TRUNCATE (X, Y) returns the number x rounded to the y-digit after the decimal point. If the value of y is 0, the result does not contain the decimal point or decimal part.

If y is set to a negative number, all the low-level values after the start of the nth digit on the left of the decimal point are truncated.

TIPS: The ROUND (X, Y) function rounds the value while TRUNCATE (x, y) directly removes the value.
3. HEX (N)
Convert N to hexadecimal notation and return it as a string (N is a BIGINT number, equivalent to CONV
(N, 10, 16 ))

UNHEX is the inverse function of HEX.

Character Functions
1. CHAR_LENGTH (STR) returns the number of characters contained in the string 'str. A multi-byte character is counted as a single character.

2. Merge string functions CONCAT_WS (x, s1, s2 ,......)

CONCAT_WS (x, s1, s2 ,......), CONCAT_WS stands for CONCAT with Separator, which is a special form of the CONCAT () function.

The first parameter x is the delimiter of other parameters. The separator is placed between the two strings to be connected. The delimiter can be a string or

Other parameters. If the Delimiter is NULL, the result is NULL. The function ignores the NULL value after any separator parameter.

3. INSERT (str, pos, len, newstr)
Returns the str string, which is the substring starting from the position pos and is replaced by the newstr string with the len character length.

4. String filling functions: LPAD (S1, LEN, S2) and RPAD (S1, LEN, S2)

LPAD (S1, LEN, S2) returns the string s1, whose left is filled by string s2 to the length of the len string. If s1 is longer than len, the return value is shortened to len.


Note the differences between the above two images