LOCATE and POSITION of string functions intercepted by MySQL

Source: Internet
Author: User

We know that mysql does not include charindex function truncation characters like mssql, but mysql seems to provide more character truncation functions than mssql, next I will introduce to you the usage of the mysql character truncation function.

MySQL string truncation functions: left (), right (), substring (), substring_index (). There are also mid () and substr (). Here, mid () and substr () are equivalent to substring ().

LOCATE (substr, str)
POSITION (substr IN str)

Returns the position of the substring substr that appears for the first time in the str string. If the 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 multi-byte secure. In MySQL 3.23, this function is case-sensitive. in MySQL 4.0, if any parameter is a binary string, it is case-sensitive.

The Code is as follows: Copy code

LOCATE (substr, str, pos)

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

The Code is as follows: Copy code
Mysql> select locate ('bar', 'foobarbarbar ', 5 );
-> 7

This function is multi-byte secure. In MySQL 3.23, this function is case-sensitive. in MySQL 4.0, if any parameter is a binary string, it is case-sensitive.

Example:

The Code is as follows: Copy code
Select info. * from info left join attribute as a on POSITION (CONCAT (substr ('20140901', 00000-LENGTH (info. fid), info. fid) IN. fid) <> 0

 

Other methods for intercepting characters

1. Extract strings from the left
Left (str, length)

Note: left (truncated field, truncated length)
Example:

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

2. Extract strings from the right
Right (str, length)

Note: right (intercepted field, truncated length)
Example:

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

3. truncate a string
Substring (str, pos)
Substring (str, pos, length)

Description: substring (truncated field, starting from the nth digit)
Substring (truncated field, starting from the nth digit and intercepting the length)
Example:

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, for example,-5, it is the length from the last to the end or truncation of the string)

4. truncate a string by keyword
Substring_index (str, delim, count)

Note: substring_index (intercepted field, keyword, number of times the keyword appears)
Example:

The 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.