Replace alphanumeric values in colum with numeric values

[sugan]

Hi,I have a table column which has alphanumeric values like "212as", "wd2232w"I want to replace all the values with the alpha characters removed.i.e 212as should be replaced as 212wd2232w should be replaced as 2232I tried using mysql convert function but in vain...Regards,Suganya

[vijay]

Hi.. For that retrive data via mysql query and process on it via regular expression /([a-zA-Z]+)([0-9]+)([a-zA-Z]+)/ - match this expression with that column preg_replace('([a-zA-Z]+)([0-9]+)([a-zA-Z]+)','\2','COLUMN_VALUE') with the help of above function use update query to replace with digits only.Regards,Vijay

[sugan]

Hi,To replace the values, do i want to use the PHP function preg_replace.Is there any function in mysql to replace those values?Regards,Suganya

[vijay]

Hi.. In mysql only function regexp only... there would be no function for direct replacement in mysql.. If is there any then let me know..Regards,Vijay

[Yahweh]

You're out of luck, Suganya, there's no function (at least not an elegant function) to remove alpha characters. You should probably do it programmatically.

Hi.. For that retrive data via mysql query and process on it via regular expression /([a-zA-Z]+)([0-9]+)([a-zA-Z]+)/ - match this expression with that column preg_replace('([a-zA-Z]+)([0-9]+)([a-zA-Z]+)','\2','COLUMN_VALUE') with the help of above function use update query to replace with digits only.Regards,Vijay

That expression is too complicated, and it doesn't match strings like "hello324234". You don't need to capture anything, just remove the unwanted characters:

preg_replace('[^\d]', '', COLUMN_VALUE)