我有一个表MobileInfo,其中有一个appversion的字段,根据这个字段截分为三部分,然后把三部分赋值给MobileInfo表的另外三个字段(company,brand,maincom);
比如appversion的值是1.0_1.6_9,maincom=1.0 company=1,brand=6_9要截成这样,然后修改MobileInfo表?请问这个要怎么写sql语句,我用的是mysql数据库! 在线等,很急!!!!
比如appversion的值是1.0_1.6_9,maincom=1.0 company=1,brand=6_9要截成这样,然后修改MobileInfo表?请问这个要怎么写sql语句,我用的是mysql数据库! 在线等,很急!!!!
UPDATE MobileInfo
SET company = SUBSTR(appversion,1,INSTR(appversion,'_')-1),
brand = SUBSTR(SUBSTR(appversion,INSTR(appversion,'_')+1),1,INSTR(SUBSTR(appversion,INSTR(appversion,'_')+1),'.')-1),
maincom = SUBSTR(SUBSTR(appversion,INSTR(appversion,'_')+1),INSTR(SUBSTR(appversion,INSTR(appversion,'_')+1),'.')+1) ;
maincom=1.0 company=1,brand=6_9,大致就是这样的,做修改操作!!
INSERT INTO MobileInfo(appversion,company,brand,maincom)
VALUES ('1.0_1.6_9',
SUBSTR('1.0_1.6_9',1,INSTR('1.0_1.6_9','_')-1),
SUBSTR(SUBSTR('1.0_1.6_9',INSTR('1.0_1.6_9','_')+1),1,INSTR(SUBSTR('1.0_1.6_9',INSTR('1.0_1.6_9','_')+1),'.')-1),
SUBSTR(SUBSTR('1.0_1.6_9',INSTR('1.0_1.6_9','_')+1),INSTR(SUBSTR('1.0_1.6_9',INSTR('1.0_1.6_9','_')+1),'.')+1));
SET company = SUBSTR(appversion,1,INSTR(appversion,'_')-1),
brand = SUBSTR(SUBSTR(appversion,INSTR(appversion,'_')+1),1,INSTR(SUBSTR(appversion,INSTR(appversion,'_')+1),'_')-1),
maincom = SUBSTR(SUBSTR(appversion,INSTR(appversion,'_')+1),INSTR(SUBSTR(appversion,INSTR(appversion,'_')+1),'_')+1) ;
实在不行就贴几条表数据出来看看