MySql: how to remove special characters from column in query What i get value in php

    $subject = preg_replace('/[^A-Za-z0-9]/', '', 'abc - 123');

// abc123


Is it possible to use preg_replace in my mysql query?

I want to something like this. want to compare like following

SELECT `id`,`user_id`,`subject` FROM `mail` where preg_replace('/[^A-Za-z0-9]/', '',`subject`)=$subject


I know what i write above is not available. Still i want help. I didn't get proper answer. 

I tried with function

CREATE DEFINER=`root`@`localhost` FUNCTION `removeExtraChar`(`str` VARCHAR(255)) RETURNS varchar(255) CHARSET latin1
    NO SQL
BEGIN 
  DECLARE i, len SMALLINT DEFAULT 1; 
  DECLARE ret CHAR(32) DEFAULT ''; 
  DECLARE c CHAR(1); 
  SET len = CHAR_LENGTH( str ); 
  REPEAT 
    BEGIN 
      SET c = MID( str, i, 1 ); 
      IF c REGEXP '[[:alnum:]]' THEN 
        SET ret=CONCAT(ret,c); 
      END IF; 
      SET i = i + 1; 
    END; 
  UNTIL i &gt; len END REPEAT; 
  RETURN ret; 
END


and call it like

My table data

id   user_id   subject
----------------------
1      1        abc-123
2      1        xyz-234
3      2        new-post

SELECT `id`,`user_id`,`subject` FROM `mail` where  removeExtraChar(`subject`)=$subject


but this goes in infinite loop. Nothing executes
Note: I don't want to use extra mysql plugin.
For similar purposes, you can also rely on a series and GROUP_CONCAT:

SET @str:='abc - 123';
SELECT GROUP_CONCAT(c SEPARATOR '')
FROM (
    SELECT MID(@str, id, 1) AS c FROM myseries
    WHERE id BETWEEN 1 AND LENGTH(@str)
) a
WHERE c REGEXP '[[:alnum:]]';


For this you could keep a myseries table for general use, it can be quickly created with 10,000 rows like this:

CREATE TABLE myseries (id int NOT NULL AUTO_INCREMENT PRIMARY KEY);
INSERT INTO myseries
SELECT NULL FROM information_schema.columns, information_schema.columns c2 LIMIT 10000;


Or create one on the fly from any table large enough:

SET @str:='abc - 123';
SET @len:=LENGTH(@str);
SELECT GROUP_CONCAT(c SEPARATOR '')
FROM (
    SELECT MID(@str, id, 1) AS c
    FROM (
        SELECT @num:=@num+1 AS id
        FROM (SELECT @num:=0) num, yourtable
        WHERE @num&lt;=@len
    ) myseries
) a
WHERE c REGEXP '[[:alnum:]]';