Strip special characters from a column with one regex call on MySQL or PostgreSQL, or a short loop on SQL Server. Or paste text into the tool below to clean a value by hand, no query required.
Modern databases have a regex function; SQL Server needs a small loop:
-- MySQL 8 and PostgreSQL: one regex call
SELECT REGEXP_REPLACE(col, '[^A-Za-z0-9 ]', '', 'g') FROM t; -- PostgreSQL
SELECT REGEXP_REPLACE(col, '[^A-Za-z0-9 ]', '') FROM t; -- MySQL 8
-- SQL Server (no built-in regex): strip in a loop with PATINDEX
WHILE PATINDEX('%[^a-zA-Z0-9 ]%', @s) > 0
SET @s = STUFF(@s, PATINDEX('%[^a-zA-Z0-9 ]%', @s), 1, '');Cleaning a one-off value rather than a whole column? Paste it into the tool above and copy the result.
On MySQL 8 or PostgreSQL use REGEXP_REPLACE(col, '[^A-Za-z0-9 ]', '') to keep letters, digits and spaces. On SQL Server, which has no built-in regex, loop with PATINDEX and STUFF to delete each non-alphanumeric character.
Use PATINDEX to find the position of the first character that is not a letter, digit or space, then STUFF to remove it, and repeat in a WHILE loop until none remain.
Same job in another tool: Python, JavaScript, Excel, Word or SQL.
Remove Special Characters