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? — Kzqai, Commented Mar 12, 2014 at 21:29

O. Jones · Accepted Answer · 2012-09-11 12:25:51Z

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, 2012 at 12:25

O. Jones

109k17 gold badges131 silver badges183 bronze badges

Add a comment |

Collectives™ on Stack Overflow

Replace Non alpha numeric characters with empty string MYSQL

1 Answer 1

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Linked

Related