怎样在MySql中处理这类格式的字符？

Question

在一张表里有近百万条数据，id = 0_1013377_161_1;现要将id字段的末尾1改成001，末尾本来是int型转来的，都是数字的，如果是_2则要得到002,如果是_11则换成_011,如果是_111,则就是_111。总之就是换算成三位表示的，如何做呢？

Answer 1

select
concat(
left(id,length(id)-SUBSTR(id,CHAR_LENGTH(id)-instr(REVERSE(id),'_')+2)),
 case when LENGTH(SUBSTR(id,CHAR_LENGTH(id)-instr(REVERSE(id),'_')+2))=3 then SUBSTR(id,CHAR_LENGTH(id)-instr(REVERSE(id),'_')+2) 
when LENGTH(SUBSTR(id,CHAR_LENGTH(id)-instr(REVERSE(id),'_')+2))=2 then concat('0',SUBSTR(id,CHAR_LENGTH(id)-instr(REVERSE(id),'_')+2))
when LENGTH(SUBSTR(id,CHAR_LENGTH(id)-instr(REVERSE(id),'_')+2))=1 then concat('00',SUBSTR(id,CHAR_LENGTH(id)-instr(REVERSE(id),'_')+2)) end) from test21;

Answer 2

update table1 set id=LPAD(SUBSTRING_INDEX(id,'_',-1),3,'0');
这个语句的问题是：一旦你的ID的尾数为12345，只会取前3个数字123。

Answer 3

select 
   case 
     when length(substring_index(id,'_',-1))=1   
        then 
          concat(substring_index(id,'_',3),'_00',substring_index(id,'_',-1))
     when length(substring_index(id,'_',-1))=2
        then 
          concat(substring_index(id,'_',3),'_0',substring_index(id,'_',-1))
     else 
          id    
     end
   as id;

Answer 4

我一直找不到倒着查找的函数，原来是  substring_index

Answer 5

set @a := "0_1013377_161_1";
set @b := "0_1013377_161_12";
set @c := "0_1013377_161_123";select  @a as 原数据, concat(SUBSTRING_INDEX(@a, '_', 3), '_', LPAD(SUBSTRING_INDEX(@a, '_',-1),3,'0' )) as 处理后数据
union
select  @b, concat(SUBSTRING_INDEX(@b, '_', 3), '_', LPAD(SUBSTRING_INDEX(@b, '_',-1),3,'0' ))
union
select  @c, concat(SUBSTRING_INDEX(@c, '_', 3), '_', LPAD(SUBSTRING_INDEX(@c, '_',-1),3,'0' ));+-------------------+-------------------+
| 原数据                  | 处理后数据        |
+-------------------+-------------------+
| 0_1013377_161_1   | 0_1013377_161_001 |
| 0_1013377_161_12  | 0_1013377_161_012 |
| 0_1013377_161_123 | 0_1013377_161_123 |
+-------------------+-------------------+实际操作，如下即可：
update 表 set id = concat(SUBSTRING_INDEX(id, '_', 3), '_', LPAD(SUBSTRING_INDEX(id, '_',-1),3,'0' ));

Answer 6

居然还可以用lpad()这个函数，呵呵，我还用case when呢。学习啦