求根据订单长度，更新字段值的sql

Question

需求是
更新RECHARGE_NOTE，判断字段flow_code的长度
如果是20位，纯数字，修改pay_kind=2(盛付通)
如果是16位，纯数字，修改pay_kind=1(国付宝)
如果是16位，有字母，修改pay_kind=0(易宝)

Answer 1

  update RECHARGE_NOTE t
     set t.pay_kind = case
                        when length(t.flow_code) = 20 and
                             REGEXP_LIKE(t.flow_code, '\d{20}') then
                         2
                        when length(t.flow_code) = 16 and
                             REGEXP_LIKE(t.flow_code, '\d{16}') then
                         1
                        when length(t.flow_code) = 16 then
                         0
                      end;

Answer 2

改进了下：  update RECHARGE_NOTE t
     set t.pay_kind = case
                        when length(t.flow_code) = 20 and
                             REGEXP_LIKE(t.flow_code, '\d{20}') then
                         2
                        when length(t.flow_code) = 16 and
                             REGEXP_LIKE(t.flow_code, '\d{16}') then
                         1
                        when length(t.flow_code) = 16 and
                             REGEXP_LIKE(t.flow_code, '[[:alpha:]]+') then
                         0
                        else
                         t.pay_kind
                      end;