af83

Strip non numeric characters in pure SQL (mysql)

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…

select (concat(
CASE
WHEN substring(phone_number, 1,1) REGEXP '[0-9]'
THEN substring(phone_number, 1,1) ELSE '' END ,
CASE
WHEN substring(phone_number, 2,1) REGEXP '[0-9]'
THEN substring(phone_number, 2,1) ELSE '' END ,
CASE
WHEN substring(phone_number, 3,1) REGEXP '[0-9]'
THEN substring(phone_number, 3,1) ELSE '' END ,
CASE
WHEN substring(phone_number, 4,1) REGEXP '[0-9]'
THEN substring(phone_number, 4,1) ELSE '' END ,
CASE
WHEN substring(phone_number, 5,1) REGEXP '[0-9]'
THEN substring(phone_number, 5,1) ELSE '' END ,
CASE
WHEN substring(phone_number, 6,1) REGEXP '[0-9]'
THEN substring(phone_number, 6,1) ELSE '' END ,
CASE
WHEN substring(phone_number, 7,1) REGEXP '[0-9]'
THEN substring(phone_number, 7,1) ELSE '' END ,
CASE
WHEN substring(phone_number, 8,1) REGEXP '[0-9]'
THEN substring(phone_number, 8,1) ELSE '' END ,
CASE
WHEN substring(phone_number, 9,1) REGEXP '[0-9]'
THEN substring(phone_number, 9,1) ELSE '' END ,
CASE
WHEN substring(phone_number, 10,1) REGEXP '[0-9]'
THEN substring(phone_number, 10,1) ELSE '' END ,
CASE
WHEN substring(phone_number, 11,1) REGEXP '[0-9]'
THEN substring(phone_number, 11,1) ELSE '' END ,
CASE
WHEN substring(phone_number, 12,1) REGEXP '[0-9]'
THEN substring(phone_number, 12,1) ELSE '' END ,
CASE
WHEN substring(phone_number, 13,1) REGEXP '[0-9]'
THEN substring(phone_number, 13,1) ELSE '' END ,
CASE
WHEN substring(phone_number, 14,1) REGEXP '[0-9]'
THEN substring(phone_number, 14,1) ELSE '' END ,
CASE
WHEN substring(phone_number, 15,1) REGEXP '[0-9]'
THEN substring(phone_number, 15,1) ELSE '' END ,
CASE
WHEN substring(phone_number, 16,1) REGEXP '[0-9]'
THEN substring(phone_number, 16,1) ELSE '' END ,
CASE
WHEN substring(phone_number, 17,1) REGEXP '[0-9]'
THEN substring(phone_number, 17,1) ELSE '' END ,
CASE
WHEN substring(phone_number, 18,1) REGEXP '[0-9]'
THEN substring(phone_number, 18,1) ELSE '' END ,
CASE
WHEN substring(phone_number, 19,1) REGEXP '[0-9]'
THEN substring(phone_number, 19,1) ELSE '' END ,
CASE
WHEN substring(phone_number, 20,1) REGEXP '[0-9]'
THEN substring(phone_number, 20,1) ELSE '' END ,
CASE
WHEN substring(phone_number, 21,1) REGEXP '[0-9]'
THEN substring(phone_number, 21,1) ELSE '' END ,
CASE
WHEN substring(phone_number, 22,1) REGEXP '[0-9]'
THEN substring(phone_number, 22,1) ELSE '' END ,
CASE
WHEN substring(phone_number, 23,1) REGEXP '[0-9]'
THEN substring(phone_number, 23,1) ELSE '' END ,
CASE
WHEN substring(phone_number, 24,1) REGEXP '[0-9]'
THEN substring(phone_number, 24,1) ELSE '' END )) as cleaned,phone_number from members

blog comments powered by Disqus