PHP/MySQL ->
Function that helps to clear email address string

This UDF function clears string that supposed to be e-mail address. So it's aim is to remove characters ilegal for e-mails:
USE colombo;

DROP FUNCTION IF EXISTS fn_clear_email;

CREATE FUNCTION fn_clear_email
   (p_inword VARCHAR(100)) RETURNS VARCHAR(100)
BEGIN
/*
this function clears string from special characters and duplicate blank spaces
*/


DECLARE v_temp, v_outword VARCHAR(100);
DECLARE i, n, acode INT;

-- trim blanks
SET v_temp = LOWER(TRIM(p_inword));

-- reduce blank space
SET v_temp = REPLACE(v_temp,CONCAT(CHAR(32),CHAR(32),CHAR(32)),CHAR(32));
SET v_temp = REPLACE(v_temp,CONCAT(CHAR(32),CHAR(32),CHAR(32)),CHAR(32));
SET v_temp = REPLACE(v_temp,CONCAT(CHAR(32),CHAR(32)),CHAR(32));
SET v_temp = REPLACE(v_temp,CONCAT(CHAR(32),CHAR(32)),CHAR(32));

SET v_outword = '';
SET i = 0;
SET n = LENGTH(v_temp);

-- run over characters of the string

WHILE i < n DO
  SET acode = ASCII(MID(v_temp,i,1));
  -- allow lowcase english characters, dot, @, underscore
  -- and Blank Space to be passed to the result string

  IF ((acode >= 97 && acode <= 122)
  || (acode = 46) || (acode = 46)
  || (acode = 64) || (acode = 95))
  THEN
   SET v_outword = CONCAT(v_outword,MID(v_temp,i,1));
  END IF;

  SET i = i + 1;

END WHILE;

-- replace blank space by under score
SET v_outword = REPLACE(v_outword,CHAR(32),'_');

-- final
SET v_outword = RTRIM(LTRIM(v_outword));
IF v_outword = '' THEN
  SET v_outword = 'NoValue';
END IF;

RETURN v_outword;
-- end of procedure

END;


Then we can call this function using single SELECT:

SELECT fn_clear_email ('123$Darwin.tf@^(George).com==$001 !');

Reciving in the message window: darwin.tf@george.com

Or call function for every row in SELECT statement:
ALTER TABLE Customers ADD email VARCHAR(80);

UPDATE Customers SET email = 'krips@morning.tv'
WHERE CustomerID = 2;

UPDATE Customers SET email = 'pOliv234@hotmail.com'
WHERE CustomerID = 4;

SELECT CustomerID, email,
fn_clear_email(email) AS clear_email
FROM Customers
WHERE CustomerID <= 4;

The results will came as following:
CustomerIDCustomerNameDelivPay
2krips@morning.tvkrips@morning.tv
3   NoValue
4pOliv234@hotmail.compoliv@hotmail.com


sqlexamples.info