新萄京计算机网络

 新萄京计算机网络     |      2019-12-12

MySQL查询用户注册邮箱的种类的语句

MySQL的substring_index函数简析

 

 

查询用户注册邮箱的种类

假定有一张表,其中有个字段是 email 保存了电子邮箱,然后,我想统计每种邮箱的占比情况。

 

我们知道电子信箱的格式是: [email protected] ,要实现上面的统计,必须取得 email
字符串中 @ 后面的域名部分。

Sql代码  

MySQL提供了一个名为 substring_index 的函数,能够满足我们的需求:

 

MySQL 5.5 Reference Manual 写道  www.2cto.com  

SELECT SUBSTRING_INDEX(email,'@',-1) mailbox,

SUBSTRING_INDEX(str,delim,count)

 

 

COUNT(SUBSTRING_INDEX(email,'@',-1))  

Returns the substring from string str before count occurrences of the delimiter delim.
If count is positive, everything to the left of the final delimiter (counting from the left)
is returned. If count is negative, everything to the right of the final delimiter (counting
from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when
searching for delim.

www.2cto.com

 

FROM user_register WHERE email IS NOT NULL AND email<>''

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);

GROUP BY mailbox  

 

 

        -> 'www.mysql'

 

 

 

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);

 

 

作者 SuperCustomer

        -> 'mysql.com'

查询用户注册邮箱的种类 Sql代码 SELECT SUBSTRING_INDEX(email,@,-1) mailbox, COUNT(SUBSTRING_INDEX(email,@,-1)) www.2cto....

 

This function is multi-byte safe.