Shulou(Shulou.com)05/31 Report--

Mysql how to intercept 1 decimal place, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

The left,right,substr,instr intercepts the string and intercepts the decimal point float

The left,right,substr,instr of mysql intercepts the string and the float of the decimal point

2007-04-22 17:31

/ /-

Select avg (stu_oder_percent) from tb_sch_collect

Results:

Avg (stu_oder_percent):

60.60962

/ /-

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

Results:

Left (avg (stu_oder_percent), 4):

60.6

/ /-

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

Results:

Right (avg (stu_oder_percent), 7)

0.60962

/ /-

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

One place after the decimal point, which is accurate to one place after the decimal point

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

Results:

60.6

/ /-

String function of MySql

ASCII (str)

Returns the ASCII code value of the leftmost character of the string str. Returns 0 if str is an empty string. Returns NULL if str is NULL.

Mysql > select ASCII ('2')

-> 50

Mysql > select ASCII (2)

-> 50

Mysql > select ASCII ('dx')

-> 100

You can also see the ORD () function.

ORD (str)

If the leftmost character of the string str is a multibyte character, by using the format ((first byte ASCII code) * 256 + (second byte ASCII code)) [* 256+third byte ASCII code...] Returns the ASCII code value of the character to return the multibyte character code. If the leftmost character is not a multibyte character. Returns the same value as the ASCII () function.

Mysql > select ORD ('2')

-> 50

CONV (NParth from Basebook Basebook totalbase)

Convert numbers between different digital bases. Returns the string number of the number N, transformed from the from_base base to the to_ base, and NULL if any parameter is NULL. The 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 to be a signed number, otherwise N is regarded as an unsigned number. CONV works at 64-bit precision.

Mysql > select CONV ("a", 16pm 2)

-> '1010'

Mysql > select CONV ("6e", 18penny 8)

-> '172'

Mysql > select CONV (- 17, 10, 10, 14, 18)

->'- H'

Mysql > select CONV (10 + "10" +'10)

-> '40'

BIN (N)

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

Mysql > select BIN (12)

-> '1100'

OCT (N)

Returns a string representation of the octal value N, where N is a long integer number, which is equivalent to CONV. If N is NULL, return NULL.

Mysql > select OCT (12)

-> '14'

HEX (N)

Returns a string representation of the hexadecimal value N, where N is a BIGINT number, which is equivalent to CONV. If N is NULL, return NULL.

Mysql > select HEX (255)

-> 'FF'

CHAR (NJI...)

CHAR () interprets parameters as integers and returns a string consisting of the ASCII code characters of those integers. The NULL value is skipped.

Mysql > select CHAR (77, 121, 83, 81, 76)

-> 'MySQL'

Mysql > select CHAR (77, 7. 3, 7. 3, 7. 3)

-> 'MMM'

CONCAT (str1,str2,...)

Returns the string from the parameter link. Returns NULL if any parameter is NULL. There can be more than 2 parameters. A numeric parameter is transformed into an equivalent string.

Mysql > select CONCAT ('My',' slots, '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 string str.

Mysql > select LENGTH ('text')

-> 4

Mysql > select OCTET_LENGTH ('text')

-> 4

Note that for multibyte characters, their CHAR_LENGTH () is evaluated only once.

LOCATE (substr,str)

POSITION (substr IN str)

Returns the position where the substring substr appears first in the string str, and returns 0. 0 if substr is not in str.

Mysql > select LOCATE ('bar',' foobarbar')

-> 4

Mysql > select LOCATE ('xbar',' foobar')

-> 0

This function is multibyte reliable.

LOCATE (substr,str,pos)

Returns the position where the substring substr appears first in the string str, starting with the position pos. If substr is not in str, return 0.

Mysql > select LOCATE ('bar',' foobarbar',5)

-> 7

This function is multibyte reliable.

INSTR (str,substr)

Returns the first occurrence of the substring substr in the string str. This is the same as LOCATE (), which has the form of two parameters, except that the parameters are reversed.

Mysql > select INSTR ('foobarbar',' bar')

-> 4

Mysql > select INSTR ('xbar',' foobar')

-> 0

This function is multibyte reliable.

LPAD (str,len,padstr)

Returns the string str, padded on the left with the string padstr until str is len characters long.

Mysql > select LPAD ('hi',4,'??')

->'?? hi'

RPAD (str,len,padstr)

Returns the string str, padded on the right with the string padstr until str is len characters long.

Mysql > select RPAD ('hi',5,'?')

-> 'hi???'

LEFT (str,len)

Returns the leftmost len characters of the string str.

Mysql > select LEFT ('foobarbar', 5)

-> 'fooba'

This function is multibyte reliable.

RIGHT (str,len)

Returns the rightmost len characters of the string str.

Mysql > select RIGHT ('foobarbar', 4)

-> 'rbar'

This function is multibyte reliable.

SUBSTRING (str,pos,len)

SUBSTRING (str FROM pos FOR len)

MID (str,pos,len)

Returns a substring of len characters from the string str, starting with the position pos. The variant form of using FROM is the ANSI SQL92 syntax.

Mysql > select SUBSTRING ('Quadratically',5,6)

-> 'ratica'

This function is multibyte reliable.

SUBSTRING (str,pos)

SUBSTRING (str FROM pos)

Returns a substring from the starting position of the string str, pos.

Mysql > select SUBSTRING ('Quadratically',5)

-> 'ratically'

Mysql > select SUBSTRING ('foobarbar' FROM 4)

-> 'barbar'

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.