システム屋

ばらばらにしていた電話番号等をを一つの項目に統合するSQL文
CASEの使用方法。

update customer_info set
zipcode = case zipcode1 + ‘-‘ + zipcode2 when ‘-‘ then ” else zipcode1 + ‘-‘ + zipcode2 end  ,
main_tel = replace(main_tel1 + ‘-‘ + main_tel2 + ‘-‘ + main_tel3,’–‘,”) ,
tel = replace(tel1 + ‘-‘ + tel2 + ‘-‘ + tel3,’–‘,”),
fax = replace(fax1 + ‘-‘ + fax2 + ‘-‘ + fax3,’–‘,”),
mtel = replace(mtel1 + ‘-‘ + mtel2 + ‘-‘ + mtel3,’–‘,”),
personal_zipcode = case personal_zipcode1 + ‘-‘ + personal_zipcode2 when ‘-‘ then ” else personal_zipcode1 + ‘-‘ + personal_zipcode2 end  ,
personal_tel = replace(personal_tel1 + ‘-‘ + personal_tel2 + ‘-‘ + personal_tel3,’–‘,”) ,
personal_fax = replace(personal_fax1 + ‘-‘ + personal_fax2 + ‘-‘ + personal_fax3,’–‘,”) ,
personal_mtel = replace(personal_mtel1 + ‘-‘ + personal_mtel2 + ‘-‘ + personal_mtel3,’–‘,”) 
;