Azhar Azhar - 26 days ago 5
SQL Question

Sql - delete all rows of a column containing special characters(not alphanumeric)

I wanna delete all the rows of a

table

where
column
contains special characters such as
@#$_&-+()/*'';!?~|•√π÷¶∆£¢¥^°={}\%©™℅[]


Basically all of it where all the elements are not alphabet or a number.

Answer

Codecaster gave you the path to the solution. Decompose to get it.

You have this :

SELECT * FROM table WHERE column REGEXP '^[A-Za-z0-9]+$'

Which returns all rows that contains only alphanumeric chars

If you want to delete all other rows, you can do :

DELETE FROM table 
WHERE ID NOT IN
(
    SELECT ID FROM table WHERE column REGEXP '^[A-Za-z0-9]+$'
)

or negate the regex :

DELETE FROM table WHERE column NOT REGEXP '^[A-Za-z0-9]+$'
Comments