Solved

# remove all non-numeric characters FROM A COLUMN?

Posted on 2011-05-12
1,316 Views
How would I query a field and remove all non-numeric characters and then put the new data back in the field?
0
Question by:Steve Samson

LVL 59

Expert Comment

Hi.

In my Article, I talk about checking for isnumeric using regular expressions.  You can use this as part of a routine that pulls each character in ensures it is numeric to determine if it should remain in the string.  I have an example of this approach, but trying to think of more efficient ways to do this for you.

To assist with that, what is the pattern of the data, i.e., is it consistent with number value possibly at beginning or end?
Can you post a sample of data and expected results for each?
Please clarify non-numeric to determine if includes '.' that may pass the isnumeric test, i.e., see my isinteger function in Article.
0

LVL 59

Accepted Solution

Okay, here is one approach, using a numbers utility table http://www.experts-exchange.com/Database/MySQL/A_3573-A-MySQL-Tidbit-Quick-Numbers-Table-Generation.html copy code from Utility: Numbers Table section and will create a table called numbers in schema util for you.

test data:
``````create table numeric_test(id int auto_increment primary key, val varchar(15));
insert into numeric_test(val)
select 'A1B2C3'
union select 'Price: \$25.50'
union select '5 french hens'
union select '25A30'
union select '10MOD5=2'
;
``````

test code:
``````select id
, group_concat(
substring(val, n, 1)
order by n
separator ''
) as new_val
from numeric_test
join util.numbers
on n between 1 and char_length(val)
and substring(val, n, 1) regexp '^[0-9]+\$'
-- may help with performance if some rows are already all numeric
where not val regexp '^[0-9]+\$'
group by id
``````
- regular expression used matches to IsInteger() UDF, I would recommend also creating those under schema util and use IsNumeric(...) = 1 for example.

To update the values:
``````update numeric_test tto, (
select id
, group_concat(
substring(val, n, 1)
order by n
separator ''
) as new_val
from numeric_test
join util.numbers
on n between 1 and char_length(val)
and substring(val, n, 1) regexp '^[0-9]+\$'
-- may help with performance if some rows are already all numeric
where not val regexp '^[0-9]+\$'
group by id
) tfr
set tto.val = tfr.new_val
where tfr.id = tto.id
;
``````

Another approach would be to use a UDF: http://www.mysqludf.org/lib_mysqludf_preg/

Hope that helps!

Kevin
0

Author Comment

for exacmple i might have a phone number (580)555-1212 i want it to be 5805551212
or i have a value agc1234 and i want it to be 1234or agc1234a i want it to be 1234
0

LVL 59

Expert Comment

Okay, so it can be varying patterns.  See my suggestion here then: http:#a35750560
The choice of IsNumeric versus IsInteger or some other REGEXP is how \$25.50 should be treated.  Sounds like it should result in 2550, but in a pure IsNumeric check 25.50 or \$25.50 is valid ... that is why I introduced IsInteger in my article as a bonus for those times when numeric means 0-9 only.
0

LVL 40

Expert Comment

0

Author Closing Comment

cleared my issue up
0

## Featured Post

### Suggested Solutions

More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…