remove all non-numeric characters FROM A COLUMN?

How would I query a field and remove all non-numeric characters and then put the new data back in the field?
Steve SamsonAsked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
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'
;

Open in new window


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

Open in new window

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

Open in new window


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

Hope that helps!

Kevin
0
 
Kevin CrossChief Technology OfficerCommented:
Hi.

This may be helpful: http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/A_252-How-to-Mimic-the-T-SQL-IsNumeric-Function-in-MySQL.html
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
 
Steve SamsonAuthor Commented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Kevin CrossChief Technology OfficerCommented:
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
 
Steve SamsonAuthor Commented:
cleared my issue up
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.