mysql - 使用多种情况将空格更新为 null

Question

update RawAddress 
set RawAddrZip = 
  case 
  When len(RawAddrZip) <= 5 
        Then right(('00000' + RawAddrZip), 5)

  When len(RawAddrZip) > 5 and len(RawAddrZip) <= 9
         Then right(('000000000' + RawAddrZip), 9)

  When len(RawAddrZip) = 0   then  NULL
  Else 

     RawAddrZip 
  End

where  RawAddressId in (210981,210982)

更新前

RawAddreessID   RawAddressZip
210981            
210982          1234

更新后输出将是

RawAddreessID   RawAddressZip
210981          0000 
210982          1234

我想要 Null 而不是 0000 。请更正我的sql

Answer 1

0也小于5 。只需更改案例的顺序即可

case 
  When len(RawAddrZip) = 0
        Then NULL
  When len(RawAddrZip) <= 5 
        Then right(('00000' + RawAddrZip), 5)

  -- or change the condition
  When len(RawAddrZip) > 0 and len(RawAddrZip) <= 5
        Then right(('00000' + RawAddrZip), 5)

您还应该检查不可见字符，因为如果在第一个 right() 上输入空字符串应该给你五个 00000不是四个0000

添加测试用例，例如:

 CASE WHEN RawAddrZip = ''    THEN 'blank'
 CASE WHEN RawAddrZip IN NULL THEN 'null'