• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1811
  • Last Modified:

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?
0
Steve Samson
Asked:
Steve Samson
  • 3
  • 2
1 Solution
 
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
 
Kevin CrossChief 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
 
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now