MySQL String function usage tips

Source: Internet
Author: User
Tags truncated

1. Intercept string from left
Left (str, length)
Description: Left (truncated field, intercept length)
Example: Select Left (content,200) as abstract from my_content_t


2, starting from the right to intercept the string
Right (str, length)
Description: Right (truncated field, intercept length)
Example: Select Right (content,200) as abstract from my_content_t


3. Intercepting strings
SUBSTRING (str, POS)
SUBSTRING (str, pos, length)
Description: Substring (intercepted field, starting from the first intercept)
SUBSTRING (intercepted field, starting from the first intercept, intercept length)
Example: Select substring (content,5) as abstract from my_content_t
Select substring (content,5,200) as abstract from my_content_t
(Note: If the number of digits is negative, 5 is the number of digits from the back to the end of the string or the length of the Intercept)


4. Intercept strings by keyword
Substring_index (Str,delim,count)
Description: Substring_index (the number of occurrences of the intercepted field, keywords, keywords)
Example: Select Substring_index ("Blog.jb51.net", ". ", 2) as abstract from my_content_t
Results: blog.jb51
(Note: If the number of occurrences of a keyword is negative, 2 is the inverse from the back to the end of the string)

5, INSTR (STR,SUBSTR); LOCATE (STR,SUBSTR); POSITION (str in substr)

Returns the position of the first occurrence of a substring substr in the string str. This is the same as the locate () with 2 parameters, except that the parameters are reversed.

6, REVERSE (str)

Reverse str;

7. TRUNCATE (x,d);

Returns the number x that is removed to the D-digit after the decimal point. If the value of D is 0, the result does not have a decimal point or a fractional part. D can be set to a negative number to intercept (zero) the value of all lows after the beginning of the D bit of the decimal point.
Today there is a need to query the percentage of a 2 field in a standard table ....

Example:

Select TRUNCATE (progress/video_duration,2) as percent from  mime_study_logs;
The Video_duration field even if (the denominator here is 0 or null), the return is also null, without using PHP to verify the legality of the Division

This calculates the division of two numbers, and 2 indicates the number of digits following the decimal point, for example: 1/3 =0.33

MySQL String function usage tips

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.