MySQL intercepts the two digits after the decimal point

Proportion: the content of the field is 12. 34567. I want to extract the exact two digits after the decimal point 12.34. However, the number of digits before the decimal point is not fixed. You can use the following method.

 

Instr (STR, substr)

Returns a substring. Substr

In the string Str

The first position in. This is in the form of two parameters Locate ()

Same, except that the parameters are reversed.

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

This function is multi-byte reliable.

 

 

Locate (substr, STR, POS)



Returns a substring. Substr

In the string Str

The first position that appears, starting from the position Pos

Start. If Substr

Not in Str

And returns 0

.

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

This function is multi-byte reliable.

 

Substring (STR, POs, Len)



Truncates a filed string whose length starts from the M character;

Method 1:

Select substring ('Password', 1, instr (password, '.') + 2) as P <br/> from 'user' where id = 8
 

Method 2:

Select substring ('Password', 1, locate ('.', password) + 2) as P <br/> from 'user' where id = 8
 

Introduction:

I encountered a MySQL Field update problem at work. <br/> In the custom table, there is a tariffurl value in the following situations: <br/> uploadfiles ariff2007031172720306698.jpg and uploadfiles upload; uploadfiles upload form <br/> to be changed to uploadfiles/tariff/200703/1172720306698 .jpg and uploadfiles/tariff/200703/1172720306690 .jpg; uploadfiles/tariff200703/1172720306691.jpg form <br/> the substring function and cancat function implementation, steps: <br/> 1. Update custom set tar Iffurl = Concat ('uploadfiles/tariff/200703/', substring (tariffurl, 24 )) where tariffurl like 'uploadfiles ariff200703 % '<br/> 2. Update m set tariffurl = Concat (substring (tariffurl, 200703),'/tariff/', substring (tariffurl, 68) <br/> where tariffurl like 'uploadfiles/tariff/200703/% 'and tariffurl like' %; uploadfiles % '<br/> substring (filed, m ): truncates a string from the start to the end of the filed field. <br/> substring (filed, m, n): truncates fil. The length of the Ed field starts from the M character to N. <br/> cancat (string1, sting2 ,......) : Set string1, string2 ,...... String.