update TEST t set t.gender =
CASE
WHEN LENGTH(t.xinxi)=15 AND to_char(SUBSTR(t.xinxi,15,1))<>'X' AND MOD(SUBSTR(t.xinxi,15,1),2)=1 THEN '男'
WHEN LENGTH(t.xinxi)=15 AND to_char(SUBSTR(t.xinxi,15,1))<>'X' AND MOD(SUBSTR(t.xinxi,15,1),2)=0 THEN '女'
WHEN LENGTH(t.xinxi)=18 AND MOD(SUBSTR(t.xinxi,17,1),2)=1 THEN '男'
WHEN LENGTH(t.xinxi)=18 AND MOD(SUBSTR(t.xinxi,17,1),2)=0 THEN '女'
ELSE NULL END
----突然发现update写的好别扭,本来想创建一张临时查询的表来做,结果不行,估摸着应该创建一张历史表,用merge into 更好一些。
update test set newcolumn=(
case when length(xinxi)=15 and mod(substr(xinxi,15,1),2)=1 then '男'
when length(xinxi)=15 and mod(substr(xinxi,15,1),2)=0 then '女'
when length(xinxi)=18 and mod(substr(xinxi,17,1),2)=1 then '男'
when length(xinxi)=18 and mod(substr(xinxi,17,1),2)=0 then '女'
else '' end )
瞎写下