« Internationalizing database validation error messages in Ruby on Rails
Validate email addresses in Mysql with a pure select »
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

This entry was posted on Monday, May 14th, 2007 at 7:52 pm and is filed under sql. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply

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