Archive for May 14th, 2007

14 05 2007

Strip non numeric characters in pure SQL (mysql)

Posted by: Ori Pekelman in sql

Ever need to strip non numeric characters from a varchar column ? well here's a solution, That will keep leading zeros and all... this query will treat up to 24 characters but you can repeat this up to 255 if you want...

PLAIN TEXT
SQL:
  1. SELECT (concat(
  2. CASE
  3. WHEN substring(phone_number, 1,1) REGEXP '[0-9]'
  4. THEN substring(phone_number, 1,1) ELSE '' END ,
  5. CASE
  6. WHEN substring(phone_number, 2,1) REGEXP '[0-9]'
  7. THEN substring(phone_number, 2,1) ELSE '' END ,
  8. CASE
  9. WHEN substring(phone_number, 3,1) REGEXP '[0-9]'
  10. THEN substring(phone_number, 3,1) ELSE '' END ,
  11. CASE
  12. WHEN substring(phone_number, 4,1) REGEXP '[0-9]'
  13. THEN substring(phone_number, 4,1) ELSE '' END ,
  14. CASE
  15. WHEN substring(phone_number, 5,1) REGEXP '[0-9]'
  16. THEN substring(phone_number, 5,1) ELSE '' END ,
  17. CASE
  18. WHEN substring(phone_number, 6,1) REGEXP '[0-9]'
  19. THEN substring(phone_number, 6,1) ELSE '' END ,
  20. CASE
  21. WHEN substring(phone_number, 7,1) REGEXP '[0-9]'
  22. THEN substring(phone_number, 7,1) ELSE '' END ,
  23. CASE
  24. WHEN substring(phone_number, 8,1) REGEXP '[0-9]'
  25. THEN substring(phone_number, 8,1) ELSE '' END ,
  26. CASE
  27. WHEN substring(phone_number, 9,1) REGEXP '[0-9]'
  28. THEN substring(phone_number, 9,1) ELSE '' END ,
  29. CASE
  30. WHEN substring(phone_number, 10,1) REGEXP '[0-9]'
  31. THEN substring(phone_number, 10,1) ELSE '' END ,
  32. CASE
  33. WHEN substring(phone_number, 11,1) REGEXP '[0-9]'
  34. THEN substring(phone_number, 11,1) ELSE '' END ,
  35. CASE
  36. WHEN substring(phone_number, 12,1) REGEXP '[0-9]'
  37. THEN substring(phone_number, 12,1) ELSE '' END ,
  38. CASE
  39. WHEN substring(phone_number, 13,1) REGEXP '[0-9]'
  40. THEN substring(phone_number, 13,1) ELSE '' END ,
  41. CASE
  42. WHEN substring(phone_number, 14,1) REGEXP '[0-9]'
  43. THEN substring(phone_number, 14,1) ELSE '' END ,
  44. CASE
  45. WHEN substring(phone_number, 15,1) REGEXP '[0-9]'
  46. THEN substring(phone_number, 15,1) ELSE '' END ,
  47. CASE
  48. WHEN substring(phone_number, 16,1) REGEXP '[0-9]'
  49. THEN substring(phone_number, 16,1) ELSE '' END ,
  50. CASE
  51. WHEN substring(phone_number, 17,1) REGEXP '[0-9]'
  52. THEN substring(phone_number, 17,1) ELSE '' END ,
  53. CASE
  54. WHEN substring(phone_number, 18,1) REGEXP '[0-9]'
  55. THEN substring(phone_number, 18,1) ELSE '' END ,
  56. CASE
  57. WHEN substring(phone_number, 19,1) REGEXP '[0-9]'
  58. THEN substring(phone_number, 19,1) ELSE '' END ,
  59. CASE
  60. WHEN substring(phone_number, 20,1) REGEXP '[0-9]'
  61. THEN substring(phone_number, 20,1) ELSE '' END ,
  62. CASE
  63. WHEN substring(phone_number, 21,1) REGEXP '[0-9]'
  64. THEN substring(phone_number, 21,1) ELSE '' END ,
  65. CASE
  66. WHEN substring(phone_number, 22,1) REGEXP '[0-9]'
  67. THEN substring(phone_number, 22,1) ELSE '' END ,
  68. CASE
  69. WHEN substring(phone_number, 23,1) REGEXP '[0-9]'
  70. THEN substring(phone_number, 23,1) ELSE '' END ,
  71. CASE
  72. WHEN substring(phone_number, 24,1) REGEXP '[0-9]'
  73. THEN substring(phone_number, 24,1) ELSE '' END )) AS cleaned,phone_number FROM members

Click to continue reading

Comments No Comments »

14 05 2007

Internationalizing database validation error messages in Ruby on Rails

Posted by: Benoît Caccinolo in ruby, sql

Ruby on Rails is a great framework, but would you believe it .. it has no built-in support for localized error messages for model level validation.

The problem is that you can internationalize the error messages but not the field name. Which makes for some very weird behavior.

Here is a bit of code to solve the problem. You have to install localization_generator before.

How to have clean internationalized validation error messages in Ruby on Rails?

in the file app/contollers/application.rb

PLAIN TEXT
RUBY:
  1. require 'localization'
  2.  
  3. class ActiveRecord::Errors
  4.   include Localization
  5.   def add_on_blank(attributes, msg = @@default_error_messages[:blank])
  6.     for attr in [attributes].flatten
  7.       value = @base.respond_to?(attr.to_s) ? @base.send(attr.to_s) : @base[attr.to_s]
  8.       if value.blank?
  9.         add(l(attr), msg)
  10.       end
  11.     end
  12.   end
  13.  
  14.   def add(attribute, msg = @@default_error_messages[:invalid])
  15.     @errors[l(attribute).to_s] = [] if @errors[attribute.to_s].nil?
  16.     @errors[l(attribute).to_s] <<msg
  17.   end
  18.  
  19. end

And that's it! the magic is done with the l() function from localization_generator

Click to continue reading

Comments No Comments »

Creative Commons License
This work is licensed under a Creative Commons Attribution 2.0 License.