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?

Answer 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.