my SQL截取问题

Question

[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
我要从上面字符串@开始截取，怎么截取它们都是login字段的我是试论好多方法都不行

Answer 1

select mid(`login`,1,instr(`login`,'@')-1) from tb； --@前边的select mid('login`,instr(`login`,'@')+1) from tb;--@后边的

Answer 2

select mid(`login`,instr(`login`,'@')+1) from tb;--@后边的

Answer 3

mysql> select substring('[email protected]',1,INSTR('[email protected]','@')-1);
+---------------------------------------------------------+
| substring('[email protected]',1,INSTR('[email protected]','@')-1) |
+---------------------------------------------------------+
| abc                                                     |
+---------------------------------------------------------+
1 row in set

Answer 4

SELECT RIGHT(LOGIN,INSTR(LOGIN,'@')-1) FROM TT

Answer 5

mysql> select substring_index('[email protected]','@',1);
+---------------------------------------+
| substring_index('[email protected]','@',1) |
+---------------------------------------+
| abc                                   |
+---------------------------------------+
1 row in set (0.00 sec)

Answer 6

用 #6楼的substring_index就可以了。select substring_index(login,'@',-1);

Answer 7

正解！另外提醒下，你的login不能有2个@，否则这样也错。