Skip to content

Instantly share code, notes, and snippets.

@wbailey
Created April 1, 2011 08:29
Show Gist options
  • Save wbailey/897888 to your computer and use it in GitHub Desktop.
Save wbailey/897888 to your computer and use it in GitHub Desktop.
converting some bad sql to correct sql with proper phone numbers
def keypad str
case str.downcase
when 'a'..'c' then '2'
when 'd'..'f' then '3'
when 'g'..'i' then '4'
when 'j'..'l' then '5'
when 'm'..'o' then '6'
when 'p'..'s' then '7'
when 'a'..'v' then '8'
when 'w'..'z' then '9'
when /\d/ then str
else raise Exception, 'invalid character in phone number'
end
end
def convert str
current = str.match(/phone = (.*) where/)[1]
begin
new = current.scan(/./).inject('') {|s,v| s << keypad(v.to_s)}
raise Exception, 'phone number is not 10 digits' unless new.size == 10
rescue Exception => e
$stderr.puts "#{str} => #{e.message}"
new = 'NULL'
end
str.sub(current, new)
end
ARGF.each_line {|v| $stdout.puts convert(v.chomp)}
wesbailey@feynman:~/tmp> ruby keypad.rb < pn.sql 2> bad.sql
update addresses set phone = 8774377822 where id = 4128190;
update addresses set phone = NULL where id = 4128231;
update addresses set phone = NULL where id = 4128420;
update addresses set phone = 9048074663 where id = 4129879;
update addresses set phone = 8667248772 where id = 4412364;
update addresses set phone = 2033344332 where id = 4465578;
update addresses set phone = 7249334673 where id = 4465875;
update addresses set phone = 7027347323 where id = 4543548;
update addresses set phone = 2257536284 where id = 4670014;
update addresses set phone = 2812426284 where id = 4685251;
update addresses set phone = 8123366683 where id = 4689043;
update addresses set phone = 3055293476 where id = 5106997;
update addresses set phone = 8002464878 where id = 5107007;
update addresses set phone = 2523493273 where id = 5153314;
update addresses set phone = 8883134332 where id = 5153381;
update addresses set phone = 8004336277 where id = 5300449;
update addresses set phone = 8778788499 where id = 5300460;
wesbailey@feynman:~/tmp> cat bad.sql
update addresses set phone = 866GRO 025 where id = 4128231; => invalid character in phone number
update addresses set phone = 8005PAENT where id = 4128420; => phone number is not 10 digits
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment