0

I have a table full of text stuff like usernames. but in that table, there are nonalphanumeric values like @ and `

I want to get the results without any special characters, only all alphanumeric in the result,

I have the table like :

**user_names**
---------   
hitlerX`x`
---------
google_e
---------
@apples
---------
b-e-a-n-s
---------
@mango@
---------
_-amen_-
---------

I want the result like

**user_names** 
---------
hitlerXx    
---------    
googlee   
---------   
apples   
---------    
beans   
---------   
mango
---------   
amen

so far I've tried :

 SELECT * FROM table WHERE column REGEXP '^[A-Za-z0-9]+$'
prem gupta
  • 69
  • 9
  • 2
    MySQL does not have any built in regex _replace_ functionality. `REGEXP`, which you are attempting to use, only supports matching, but not replacement. You might have to handle this outside MySQL, or build your own UDF. – Tim Biegeleisen Oct 06 '17 at 10:58
  • Do you know how many non alpha characters could be present? Or is that not known? – Tim Biegeleisen Oct 06 '17 at 10:58
  • @TimBiegeleisen , i don't know, maybe there is query for it – prem gupta Oct 06 '17 at 11:02
  • 1
    Not the best way, but if nothing works, you can try something like this. select replace( replace( replace( replace( some_column , '`','') , '_','') , '-', '') , '@', '') as user_names from some_table; – Ketan Patil Oct 06 '17 at 12:18
  • MariaDB has `REGEXP_REPLACE`. Otherwise GIGO -- Garbage In Garbage Out. – Rick James Oct 10 '17 at 00:13

0 Answers0