|
|
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:
| CustomerID | CustomerName | DelivPay |
| 2 | krips@morning.tv | krips@morning.tv |
| 3 | | NoValue |
| 4 | pOliv234@hotmail.com | poliv@hotmail.com |
|