Mysql问SQL语句

Question

http://topic.csdn.net/u/20090709/11/7e37f27a-01e8-400a-842c-aa7e7bcb1e03.html表A有字段 id， List ,
数据形如下：  id              List
  1                Mon=1;DE=22;BO=33;K=jin;
  2                Mon=210;DE=22;BO=23;k=P;
  3                null
  4                Mon=12
  5                ''
  6                DE=1;求SQL语句得到：
  id                Mon
  1                1
  2                210
  3                0
  4                12
  5                0
  6                0谢谢。

Answer 1

select *,
if(instr(list,'Mon=')=0,0,
mid(list,instr(list,'Mon=')+4,
if(instr(list,';')=0,length(list)+1,instr(list,';'))
-instr(list,'Mon=')-4)
)
from tty4

Answer 2

感觉不如你取出数据集到datatable中处理反而方便。mysql> select * from t_smallkonrad;
+----+--------------------------+
| id | list                     |
+----+--------------------------+
|  1 | Mon=1;DE=22;BO=33;K=jin; |
|  2 | Mon=210;DE=22;BO=23;k=P; |
|  3 | NULL                     |
|  4 | Mon=12                   |
|  5 |                          |
|  6 | DE=1;                    |
+----+--------------------------+
6 rows in set (0.00 sec)mysql>
mysql> select id,if(ifnull(instr(list,'Mon='),0)>0,
    ->          SUBSTR(list,instr(list,'Mon=')+4,
    ->                  if(LOCATE(';',list,instr(list,'Mon='))>0,
    ->                          LOCATE(';',list,instr(list,'Mon=')),
    ->                          10000
    ->                  )-instr(list,'Mon=')-4
    ->          ),
    ->  0) as Mon
    -> from t_smallkonrad;
+----+------+
| id | Mon  |
+----+------+
|  1 | 1    |
|  2 | 210  |
|  3 | 0    |
|  4 | 12   |
|  5 | 0    |
|  6 | 0    |
+----+------+
6 rows in set (0.00 sec)mysql>

Answer 3

OR
SELECT *,
IF(LENGTH(LIST)-LENGTH(NEWLIST)=0,0,
IF(INSTR(NEWLIST,';')>0,
MID(NEWLIST,1,INSTR(NEWLIST,';')-1),NEWLIST))
 FROM (
select *,
replace(list,'Mon=','') AS NEWLIST
from tty4) A1

Answer 4

or
SELECT *,
IF(INSTR(LIST,'Mon=')=0,0,
REPLACE(FF,'Mon=','')
) as newlist
 FROM (
select *,
REPLACE(LIST,MID(LIST,INSTR(LIST,';'),LENGTH(LIST)),'') AS FF
from tty4) A1