62,040
社区成员
发帖
与我相关
我的任务
分享
--> 测试时间:2009-07-09 11:44:19
--> 我的淘宝: http://shop36766744.taobao.com/
if object_id('[A1]') is not null drop table [A1]
create table [A1]([id] int,[List] varchar(24))
insert [A1]
select 1,'Mon=1;DE=22;BO=33;K=jin;' union all
select 2,'Mon=210;DE=22;BO=23;k=P;' union all
select 3,null union all
select 4,'Mon=12' union all
select 5,null union all
select 6,'DE=1;'
select ID,
mon=case when charindex('Mon',List)>0
then isnull(substring(List+';',charindex('Mon=',List+';')+4,charindex(';',List+';')-charindex('Mon=',List+';')-4),0)
else 0 end
from [A1]
/*
ID mon
----------- -----------
1 1
2 210
3 0
4 12
5 0
6 0
(所影响的行数为 6 行)
*/
--> 测试时间:2009-07-09 11:44:19
--> 我的淘宝: http://shop36766744.taobao.com/
if object_id('[A1]') is not null drop table [A1]
create table [A1]([id] int,[List] varchar(24))
insert [A1]
select 1,'Mon=1;DE=22;BO=33;K=jin;' union all
select 2,'Mon=210;DE=22;BO=23;k=P;' union all
select 3,null union all
select 4,'Mon=12' union all
select 5,null union all
select 6,'DE=1;'
select ID,mon=isnull(substring(List+';',charindex('=',List+';')+1,charindex(';',List+';')-charindex('=',List+';')-1),0) from [A1]
/*
ID mon
----------- -------------------------
1 1
2 210
3 0
4 12
5 0
6 1
(所影响的行数为 6 行)
*/
drop table [A1]
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>