Re: regexp problem
Ouch! I wish I could say elegant, but well, it's a solution, i guess ;-).
But I'll try and match you silly for silly, so here we go:
update foo
set c = concat(
if(substring(c,1,1) between '0' and '9',substring(c,1,1),'')
,if(substring(c,2,1) between '0' and '9',substring(c,2,1),'')
,if(substring(c,3,1) between '0' and '9',substring(c,3,1),'')
,if(substring(c,4,1) between '0' and '9',substring(c,4,1),'')
,if(substring(c,5,1) between '0' and '9',substring(c,5,1),'')
,if(substring(c,6,1) between '0' and '9',substring(c,6,1),'')
,if(substring(c,7,1) between '0' and '9',substring(c,7,1),'')
,if(substring(c,8,1) between '0' and '9',substring(c,8,1),'')
,if(substring(c,9,1) between '0' and '9',substring(c,9,1),'')
,if(substring(c,10,1) between '0' and '9',substring(c,10,1),'')
,if(substring(c,11,1) between '0' and '9',substring(c,11,1),'')
,if(substring(c,12,1) between '0' and '9',substring(c,12,1),'')
,if(substring(c,13,1) between '0' and '9',substring(c,13,1),'')
)
obviously, repeat as many times as the maximum number of characters in the column, thereby incrementing the second argument in the substring calls.
Note that does does not do exactly as you requested, this just removes all non-digit characters from the column values, not just those characters from the a-z range.
Edited 1 time(s). Last edit at 08/03/2005 09:11AM by Roland Bouman.