mysql 拼接字符串，截取字符串 常用方式

1，拼接字符串常用：CONCAT(‘需要拼接的字符串’,列名)

select a.UserState,a.OrgUserId,a.UserName,
CONCAT('+',b.Telephone),b.AreaCode 
from T_UserInfo b 
inner join T_User a on a
.UserId=b.UserId and 
a.UserName!='admin' and a.UserState!='Resigned'

CONCAT('+',b.Telephone)查询出号码，前边加上+号

2，分割字符串

select a.UserState,a.OrgUserId,a.UserName,
substring_index(b.Telephone,'+',1),b.AreaCode from T_UserInfo b 
inner join T_User a on 
a.UserId=b.UserId and a.UserName!='admin' 
and a.UserState!='Resigned' and b.Telephone like '%34+%'

substring_index(b.Telephone,'+',1) 用从左往右的第一个+分割电话号码，获取分割后的从左往右的第一个字符串

比如 86+133333333 执行完后 为 86

substring_index(b.Telephone,'+',-1) 用从右往左的第一个+分割电话号码，获取分割后的从右往左的第一个字符串

比如 86+133333333 执行完后 为 133333333

分割还有这些：

left()

Name: 'LEFT'
Description:
Syntax:
LEFT(str,len)
 
Returns the leftmost len characters from the string str, or NULL if any
argument is NULL.
 
URL: https://dev.mysql.com/doc/refman/5.7/en/string-functions.html
 
Examples:
mysql> SELECT LEFT('foobarbar', 5);
        -> 'fooba'
 
##该函数从最左侧开始向右截取字符串，截取 len 指定的字符后停止，例如上面的例子中 len 为5，所以从最左侧开始向右截取5个字符

right()

Name: 'RIGHT'
Description:
Syntax:
RIGHT(str,len)
 
Returns the rightmost len characters from the string str, or NULL if
any argument is NULL.
 
URL: https://dev.mysql.com/doc/refman/5.7/en/string-functions.html
 
Examples:
mysql> SELECT RIGHT('foobarbar', 4);
        -> 'rbar'
 
##该函数从最右侧开始往左截取字符串，截取 len 指定的字符后停止，例如上面的例子中 len 为4，所以从最右侧开始向左截取4个字符

substring()

Name: 'SUBSTRING'
Description:
Syntax:
SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len),
SUBSTRING(str FROM pos FOR len)
 
The forms without a len argument return a substring from string str
starting at position pos. The forms with a len argument return a
substring len characters long from string str, starting at position
pos. The forms that use FROM are standard SQL syntax. It is also
possible to use a negative value for pos. In this case, the beginning
of the substring is pos characters from the end of the string, rather
than the beginning. A negative value may be used for pos in any of the
forms of this function. A value of 0 for pos returns an empty string.
 
For all forms of SUBSTRING(), the position of the first character in
the string from which the substring is to be extracted is reckoned as
1.
 
URL: https://dev.mysql.com/doc/refman/5.7/en/string-functions.html
 
Examples:
mysql> SELECT SUBSTRING('Quadratically',5);
        -> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
        -> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
        -> 'ratica'
mysql> SELECT SUBSTRING('Sakila', -3);
        -> 'ila'
mysql> SELECT SUBSTRING('Sakila', -5, 3);
        -> 'aki'
mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
        -> 'ki'
 
##该函数从pos参数指定位置开始向右截取 len 参数指定的字符个数(len 如果不指定，则截取到最后一个字符)。如果 pos 指定的是正数，则位置从左往右计数。如果pos指定的负数，则位置从由往左数。from 和 for 关键字可以用逗号代替

substring_index()

Name: 'SUBSTRING_INDEX'
Description:
Syntax:
SUBSTRING_INDEX(str,delim,count)
 
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.
 
URL: https://dev.mysql.com/doc/refman/5.7/en/string-functions.html
 
Examples:
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
        -> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
        -> 'mysql.com'
 
##该函数截取指定的第几个分隔符之前的字符串，
第二个参数用来指定分隔符，第三个参数指定第几个分隔符。如果count 为正数，
从左往右计算分隔符个数，截取指定分隔符之前的所有字符。
如果count 为负数，则从右往走计算分隔符个数，截取指定分隔符及之后的所有字符

以上具体例子

1)从第三个字符开始截取直到结束
[root@127.0.0.1][information_schema][04:42:28]> select substring('www.mysql.com',3);
+------------------------------+
| substring('www.mysql.com',3) |
+------------------------------+
| w.mysql.com                  |
+------------------------------+
1 row in set (0.00 sec)
 
2) 从第三个字符开始截取，一共截取6个字符
[root@127.0.0.1][information_schema][04:42:38]> select substring('www.mysql.com',3,6);
+--------------------------------+
| substring('www.mysql.com',3,6) |
+--------------------------------+
| w.mysq                         |
+--------------------------------+
1 row in set (0.00 sec)
 
3) 从倒数第三个字符开始截取，直到结束(截取的时候从左往右)
[root@127.0.0.1][information_schema][04:42:43]> select substring('www.mysql.com',-3);
+-------------------------------+
| substring('www.mysql.com',-3) |
+-------------------------------+
| com                           |
+-------------------------------+
1 row in set (0.00 sec)
 
4) 截取第一个指定字符/字符串之前所有字符
[root@127.0.0.1][information_schema][04:43:01]> select substring_index('www.mysql.com','.',1);
+----------------------------------------+
| substring_index('www.mysql.com','.',1) |
+----------------------------------------+
| www                                    |
+----------------------------------------+
1 row in set (0.00 sec)
 
5) 截取倒数第二个字符串之后所有的字符
[root@127.0.0.1][information_schema][04:43:30]> select substring_index('www.mysql.com','.',-2);
+-----------------------------------------+
| substring_index('www.mysql.com','.',-2) |
+-----------------------------------------+
| mysql.com                               |
+-----------------------------------------+
1 row in set (0.00 sec)
 
6) 如果指定的分隔符不存在，则输出整个字符串
[root@127.0.0.1][information_schema][04:44:00]> select substring_index('www.mysql.com','/',-2);
+-----------------------------------------+
| substring_index('www.mysql.com','/',-2) |
+-----------------------------------------+
| www.mysql.com                           |
+-----------------------------------------+
1 row in set (0.00 sec)
 
 
7)截取最后一个指定字符之前所有的字符
[root@127.0.0.1][information_schema][04:44:15]> select REVERSE(SUBSTR(REVERSE("/data/mysql/mysql3306/log/3306-bin") , INSTR(REVERSE("/data/mysql/mysql3306/log/3306-bin"),'/')+1));
+-----------------------------------------------------------------------------------------------------------------------------+
| REVERSE(SUBSTR(REVERSE("/data/mysql/mysql3306/log/3306-bin") , INSTR(REVERSE("/data/mysql/mysql3306/log/3306-bin"),'/')+1)) |
+-----------------------------------------------------------------------------------------------------------------------------+
| /data/mysql/mysql3306/log                                                                                                   |
+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)