Replace Non alpha numeric characters with empty string MYSQL

Question

select 
    file,
    REPLACE(FILE, '[:alnum:]'+'.'+,'') AS Collection
FROM
   CollectionData;


select 
    file,
REPLACE(FILENAME, '^[a-zA-Z0-9\.]','') AS Collection
FROM collectiondata;

I would like to replace all the non alpha numeric characters from the string including .file extensions from the string as given below.

AXS00003600.txt  to AXS
NXS4DG00003600.txt to NXS4DG

I am not able to replace the non alpha numeric data when I execute the above query. What could be the problem?

possible duplicate of [MySQL: how to remove all non-alpha numeric characters from a string?](http://stackoverflow.com/questions/6942973/mysql-how-to-remove-all-non-alpha-numeric-characters-from-a-string) — Kzqai, Mar 12 '14 at 21:29

score 1 · Answer 1 · answered Sep 11 '12 at 12:25

1

Unfortunately, MySQL contains no REGEXP_REPLACE operation. The ordinary REPLACE you're trying to use doesn't work with regular expressions. You'll need to do this in client code or maybe in a stored procedure.

answered Sep 11 '12 at 12:25

O. Jones

103,626
17
118
172

Replace Non alpha numeric characters with empty string MYSQL

1 Answers1