Home › Blogs › Ori Pekelman's blog

Strip non numeric characters in pure SQL (mysql)

By Ori PekelmanMon, 05/14/2007 - 18:52

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...

  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

Laisser une réponse

http://dev.af83.com/trackback/5
0 Comments

Pourquoi ne pas laisser une réponse?

Laisser une réponse