MySQL string function example (string truncation)

Note: This article is from the blog "afei". If you want to repost this article, please contact the author! And indicate the source: http://blog.csdn.net/faye0412/article/details/7915300

These two days, I have been helping my colleagues with the data import module and encountered many problems. For example, after importing a large CSV file into MYSQL as mentioned in the previous article, data Sorting and replication of keywords, and some string truncation and other processing in SQL ....

This article makes a summary of the string truncation operations in MySQL, describes the use of string functions, and provides various examples, as shown below:

----------------------------------------

Here, we only illustrate the common MySQL string truncation functions: Left (), right (), substring (), and substring_index (). There are also mid () and substr ().

Here, mid () and substr () are equivalent to substring () functions. substring () functions are very powerful and flexible. The examples are as follows (Table creation SQL is omitted ):

1. String truncation: Left (STR, length)

Mysql> select left ('starhub. com', 7 );
+ ----------------------------- +
| Left ('starhub. com', 7) |
+ ----------------------------- +
| Starhua |
+ ----------------------------- +

2. String truncation: Right (STR, length)

Mysql> select right ('starhub. com', 3 );
+ -------------------------- +
| Right ('starhub. com', 3) |
+ -------------------------- +
| Com |
+ -------------------------- +

3. String truncation: substring (STR, POS); substring (STR, POs, Len)
3.1 starts from the string's 4th character position and ends.

Mysql> select substring ('starhub. com', 4 );
+ ------------------------------------- +
| Substring ('starhub. com', 4) |
+ ------------------------------------- +
| Rhub.com |
+ ------------------------------------- +

3.2 starts from the string's 4th character position and takes only 2 characters.

Mysql> select substring ('starhub. com', 4, 2 );
+ ---------------------------------------- +
| Substring ('starhub. com', 4, 2) |
+ ---------------------------------------- +
| RH |
+ ---------------------------------------- +

3.3 starts from the position (reciprocal) of the string's 4th characters until the end.

Mysql> select substring ('starhub. com',-4 );
+ -------------------------------------- +
| Substring ('starhub. com',-4) |
+ -------------------------------------- +
|. Com |
+ -------------------------------------- +

3.4 starts from the string's 4th character position (reciprocal) and takes only 2 characters.

Mysql> select substring ('starhub. com',-4, 2 );
+ ----------------------------------------- +
| Substring ('starhub. com',-4, 2) |
+ ----------------------------------------- +
|. C |
+ ----------------------------------------- +

We noticed that in the substring (STR, POs, Len) function, POs can be a negative value, but Len cannot be a negative value.

4. String truncation: substring_index (STR, delim, count)
4.1 intercept all characters before the second.

Mysql> select substring_index ('www .starhub.com.cn ','. ', 2 );
+ ------------------------------------------------------------ +
| Substring_index ('www .starhub.com. sg ','. ', 2) |
+ ------------------------------------------------------------- +
| Www. starhub |
+ ------------------------------------------------------------- +

4.2 intercept all characters after the second '.' (reciprocal.

Mysql> select substring_index ('www .starhub.com.cn ','. ',-2 );
+ ------------------------------------------------- +
| Substring_index ('www .starhub.com. sg ','. ',-2) |
+ ------------------------------------------------- +
| Com. sg |
+ ------------------------------------------------- +

4.3 If the delim parameter value cannot be found in the string, the entire string is returned.

Mysql> select substring_index ('www .starhub.com.cn ','. coc', 1 );
+ ----------------------------------------------------------------- +
| Substring_index ('www .starhub.com. sg ','. con', 1) |
+ ------------------------------------------------------------------ +
| Www.starhub.com.cn |
+ ------------------------------------------------------------------ +
 

5. Other References

For more functions and resources, see:

Http://dev.mysql.com/doc/refman/5.1/zh/functions.html

Http://blog.51yip.com/mysql/965.html

Http://database.51cto.com/art/200509/2479.htm