MySQL Intercept string function locate, POSITION

Source: Internet
Author: User
Tags abstract truncated

MySQL string intercept function: Left (), right (), substring (), Substring_index (). There is also mid (), substr (). where mid (), substr () is equivalent to the substring () letter

LOCATE (SUBSTR,STR)
POSITION (substr in str)

Returns the position of the substring substr the first occurrence in string str. If substring substr does not exist in STR, the return value is 0:

The code is as follows Copy Code
mysql> SELECT LOCATE (' Bar ', ' Foobarbar ');
-> 4
mysql> SELECT LOCATE (' Xbar ', ' foobar ');
-> 0

This function is multibyte-safe. In MySQL 3.23, this function is case-sensitive, and when in MySQL 4.0, if any argument is a binary string, it is case-sensitive.

The code is as follows Copy Code

LOCATE (Substr,str,pos)

Returns the position of the substring substr the first occurrence after the POS position in string str. If SUBSTR does not return 0 in str:

The code is as follows Copy Code
mysql> SELECT LOCATE (' Bar ', ' Foobarbar ', 5);
-> 7

This function is multibyte-safe. In MySQL 3.23, this function is case-sensitive, and when in MySQL 4.0, if any argument is a binary string, it is case-sensitive.

Comprehensive Example:

The code is as follows Copy Code
Select info.* from info left join attributes as a on POSITION (CONCAT (substr (' 00000 ', 1,5-length (Info.fid)), Info.fid) in a.fi D) <>0

Some other methods of intercepting characters

1. Intercept string from left
Left (str, length)

Description: Left (intercepted field, intercept length)
Cases:

The code is as follows Copy Code
Select Left (content,200) as abstract from my_content_t

2, from the right start to intercept the string
Right (str, length)

Description: Right (intercepted field, intercept length)
Cases:

The code is as follows Copy Code
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, starting from the first few)
substring (truncated field, intercept from first, intercept length)
Cases:

The code is as follows Copy Code
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 such as-5 is the number of digits from the back, to the end of the string or the length of the Intercept)

4, by keyword intercept string
Substring_index (Str,delim,count)

Description: Substring_index (truncated fields, keywords, number of keyword occurrences)
Cases:

  code is as follows copy code
select Substring_index ("blog.chinabyte.com", ".", 2) as abstract from my_content_t
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.