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 a string. strASCII code value of the leftmost character. If strIs a null string and returns 0. If strYes NULL, Return NULL.

mysql> select ASCII('2');           -> 50mysql> select ASCII(2);           -> 50mysql> 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 ((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 and ASCII()The same value returned by the function.

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

CONV(N,from_base,to_base)

Convert numbers between different digit bases. Return number NString number, from from_baseBase transform to_baseBase, if any parameter is NULL, Return NULL. Parameters NIt is interpreted as an integer, but can be specified as an integer or a string. Minimum base is 2And the largest base is 36. If to_baseIs a negative number, NIt is considered as a signed number. Otherwise, NIt is treated as an unsigned number. CONVWork with 64-point precision.

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

BIN(N)

Returns the binary value. NIn NIs a long integer ( BIGINT) Number, which is equivalent CONV(N,10,2). If NYes NULL, Return NULL.

mysql> select BIN(12);           -> '1100'

OCT(N)

Returns the octal value. NRepresents a string in NIs a long integer, which is equivalent CONV(N,10,8). If NIs NULL, return NULL.

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

HEX(N)

Returns the hexadecimal value. NRepresents a string. NIs a long integer ( BIGINT) Number, which is equivalent CONV(N,10,16). If NYes NULL, Return NULL.

mysql> select HEX(255);           -> 'FF' 

CHAR(N,...)

CHAR()Interpreted as an integer and returns a string consisting of ASCII code characters of these integers. NULLThe value is skipped.

mysql> select CHAR(77,121,83,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');           -> NULLmysql> select CONCAT(14.3);           -> '14.3'

LENGTH(str)

　

OCTET_LENGTH(str)

　

CHAR_LENGTH(str)

　

CHARACTER_LENGTH(str)

Returns a string. str.

mysql> select LENGTH('text');           -> 4mysql> 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 a substring. substrIn the string strThe first position that appears, if substrNot in strAnd returns 0.

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

This function is multi-byte reliable.

LOCATE(substr,str,pos)

Returns a substring. substrIn the string strThe first position that appears, starting from the position posStart. If substrNot in strAnd returns 0.

mysql> select LOCATE('bar', 'foobarbar',5);           -> 7

This function is multi-byte reliable.

INSTR(str,substr)

Returns a substring. substrIn the string strThe first position in. This is in the form of two parameters LOCATE()Same, except that the parameters are reversed.

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

This function is multi-byte reliable.

LPAD(str,len,padstr)

Returns a string. str, Use a string on the left padstrFill strYes lenCharacters long.

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

RPAD(str,len,padstr)

Returns a string. str, String on the right padstrFill strYes lenCharacters long.

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

LEFT(str,len)

Returns a string. strThe leftmost area lenCharacters.

mysql> select LEFT('foobarbar', 5);           -> 'fooba'

This function is multi-byte reliable.

RIGHT(str,len)

Returns a string. strThe rightmost lenCharacters 。

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)

From string strReturns lenSubstring, starting from position posStart. Use FROMThe variant form is the ANSI SQL92 syntax.

mysql> select SUBSTRING('Quadratically',5,6);           -> 'ratica'

This function is multi-byte reliable.

SUBSTRING(str,pos)

　

SUBSTRING(str FROM pos)

From string strStart position posReturns a substring.

mysql> select SUBSTRING('Quadratically',5);           -> 'ratically'mysql> select SUBSTRING('foobarbar' FROM 4);           -> 'barbar'