Mysql truncates one decimal point _ MySQL

Mysql intercepts one decimal point after bitsCN.com

Copy others, just to keep learning

Mysql's left, right, substr, instr truncate string, truncate float
Mysql's left, right, substr, instr truncate string, truncate float

//-----------------------------------------------------------------

Select avg (stu_oder_percent) from tb_sch_collect

Result:

Avg (stu_oder_percent ):

60.60962

//-----------------------------------------------------------------

Select left (avg (stu_oder_percent), 4) from tb_sch_collect

Result:

Left (avg (stu_oder_percent), 4 ):

60.6

//-----------------------------------------------------------------

Select right (avg (stu_oder_percent), 7) from tb_sch_collect

Result:

Right (avg (stu_oder_percent), 7)

0.60962

//-----------------------------------------------------------------

Instr (avg (stu_oder_percent), '.') + 1 ):

The last digit after the decimal point

Select substr (avg (stu_oder_percent), 1, instr (avg (stu_oder_percent), '.') + 1) from tb_sch_collect

Result:

60.6

// Configure //--------------------------------------------------------------------------------------------------------------------------
MySql string functions

ASCII (str)
Returns the ASCII code value of the leftmost character of the str string. If str is a null string, 0 is returned. If 'str' is NULL, return NULL.

Mysql> select ASCII ('2 ');
-> 50
Mysql> select ASCII (2 );
-> 50
Mysql> select ASCII ('dx ');
-> 100

For more information, see the ORD () function.
ORD (str)
If the leftmost character of a string 'str' is a multi-byte character, use the format (first byte ASCII code) * 256 + (second byte ASCII code )) [* 256 + third byte ASCII code...] returns the ASCII code value of a character to return the multi-byte code. If the leftmost character is not a multi-byte character. Returns the same value as that returned by the ASCII () function.

Mysql> select ORD ('2 ');
-> 50

CONV (N, from_base, to_base)
Convert numbers between different digit bases. Returns the string number of number N, which is converted from from_base base to to_base base. if any parameter is NULL, NULL is returned. Parameter N is interpreted as an integer, but can be specified as an integer or a string. The minimum base is 2 and the maximum base is 36. If to_base is a negative number, N is considered as a signed number. otherwise, N is considered as an unsigned number. CONV works with 64-bit precision.

Mysql> select CONV ("a", 16, 2 );
-> '123'
Mysql> select CONV ("6E", 18, 8 );
-> '123'
Mysql> select CONV (-17,10,-18 );
-> '-H'
Mysql> select CONV (10 + "10" + '10' + 0xa, 10, 10 );
-> '40'

BIN (N)
Returns a string of the binary value N. Here N is a long integer (BIGINT) number, which is equivalent to CONV (N, 10, 2 ). If N is NULL, return NULL.

Mysql> select BIN (12 );
-> '123'

OCT (N)
Returns the representation of a string with an octal value N. Here N is a long integer, which is equivalent to CONV (N, 10, 8 ). If N is NULL, return NULL.

Mysql> select OCT (12 );
-> '14'

HEX (N)
Returns the representation of a hexadecimal value N string. Here N is a long integer (BIGINT) number, which is equivalent to CONV (N, 10, 16 ). If N is NULL, return NULL.

Mysql & gt; select HEX (255 );
-> 'Ff'

CHAR (N ,...)
CHAR () interprets the parameter as an integer and returns a string consisting of ASCII code characters of these integers. The NULL value is skipped.

Mysql> select CHAR (77,121, 81, '76 ');
-> 'Mysql'
Mysql> select CHAR (77, 77.3, '77. 3 ');
-> 'Mmm'

CONCAT (str1, str2 ,...)
Returns a string from the parameter link. If any parameter is NULL, return NULL. There can be more than two parameters. A numeric parameter is converted to an equivalent string.

Mysql> select CONCAT ('My, s', 'ql ');
-> 'Mysql'
Mysql> select CONCAT ('My, NULL, 'ql ');
-> NULL
Mysql> select CONCAT (14.3 );
-> '14. 3'

LENGTH (str)
　
OCTET_LENGTH (str)
　
CHAR_LENGTH (str)
　
CHARACTER_LENGTH (str)
Returns the length of the str string.

Mysql> select LENGTH ('text ');
-> 4
Mysql> select OCTET_LENGTH ('text ');
-> 4

Note that for multi-byte characters, its CHAR_LENGTH () is calculated only once.
LOCATE (substr, str)
　
POSITION (substr IN str)
Returns the position of the substring substr in the first occurrence of the str. if the substring is not in the str, the return value is 0.

Mysql> select LOCATE ('bar', 'foobarbar ');
-> 4
Mysql> select LOCATE ('xbar', 'foobar ');
-> 0

This function is multi-byte reliable.

LOCATE (substr, str, pos)
Returns the position of the substring substr at the first occurrence of the substring, starting from the position pos. If substr is not in str, 0 is returned.

Mysql> select LOCATE ('bar', 'foobarbarbar ', 5 );
-> 7

This function is multi-byte reliable.
INSTR (str, substr)
Returns the position where the substring substr first appears in the str string. This is the same as LOCATE () in the form of two parameters, except that the parameters are reversed.

Mysql> select INSTR ('foobar', 'bar ');
-> 4
Mysql> select INSTR ('xbar', 'foobar ');
-> 0

This function is multi-byte reliable.
LPAD (str, len, padstr)
Returns the str string. the left side is filled with the string padstr until str is a string of len characters.

Mysql> select LPAD ('hi', 4 ,'?? ');
-> '?? Hi'

RPAD (str, len, padstr)
Returns the str string. fill it with the string padstr on the right until it is a string of len characters.

Mysql> select RPAD ('hi', 5 ,'? ');
-> 'Hi ??? '

LEFT (str, len)
Returns the leftmost len character of the str string.

Mysql> select LEFT ('foobarbar', 5 );
-> 'Fooba'

This function is multi-byte reliable.
RIGHT (str, len)
Returns the rightmost len character of the str string.

Mysql> select RIGHT ('foobarbar', 4 );
-> 'Rbar'

This function is multi-byte reliable.
SUBSTRING (str, pos, len)
　
SUBSTRING (str FROM pos FOR len)
　
MID (str, pos, len)
Returns a substring of len characters from the str string, starting from the position pos. The variant form of FROM is ANSI SQL92 syntax.

Mysql> select SUBSTRING ('quadratically ', 5, 6 );
-> 'Ratica'

This function is multi-byte reliable.
SUBSTRING (str, pos)
　
SUBSTRING (str FROM pos)
Returns a substring from the start position of the str string pos.

Mysql> select SUBSTRING ('quadratically ', 5 );
-> 'Ratically'
Mysql> select SUBSTRING ('foobarbar' FROM 4 );
-> 'Barbar'

BitsCN.com