Skills of using MySql string function

1. Intercept the string from the left

Left (str, length)

Description: left (intercepted field, intercepted length)

Example: select left (content,200) as abstract from my_content_t

2. Intercept the string from the right

Right (str, length)

Description: right (intercepted field, intercepted length)

Example: select right (content,200) as abstract from my_content_t

3. Intercept the string

Substring (str, pos)

Substring (str, pos, length)

Description: substring (intercepted field, which bit to intercept)

Substring (intercepted field, which bit to intercept, intercept length)

Example: select substring (content,5) as abstract from my_content_t

Select substring (content,5200) as abstract from my_content_t

(note: if the number of digits is negative, such as-5, it is the length from the last digit to the end of the string or truncated.)

4. Intercept a string by keyword

Substring_index (str,delim,count)

Description: substring_index (intercepted fields, keywords, number of keyword occurrences)

Example: select substring_index ("blog.jb51.net", "." , 2) as abstract from my_content_t

Results: blog.jb51

(note: if the number of times the keyword appears is negative, such as-2, it is reciprocal from the back to the end of the string)

5. INSTR (str,substr); LOCATE (str,substr); POSITION (str IN 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.

6. REVERSE (str)

Reverse str

7. TRUNCATE (XQuery D)

Returns the number X that is rounded to D digits after the decimal point. If the value of D is 0, the result has no decimal point or decimal part. You can set D to a negative number, and to truncate (return to zero) all low values following the D position from the left of the X decimal point.

There happens to be a demand today to query the percentage of two fields in the standard table.

Example:

The select TRUNCATE (progress/video_duration,2) as percent from mime_study_logs; video_duration field returns null even if the denominator is 0 or null, instead of using the php calculation to verify the validity of the division

The division of two numbers is calculated here, and 2 represents the number of digits after the decimal point of the result, for example: 1Accord3 = 0.33