"remove_non_alphanum_char_f" - this function is used remove non-alphanumeric character from a string
Input: varchar(255)
Output: varchar(255)
-------------------
Source Code:
------------------
delimiter //
DROP FUNCTION IF EXISTS remove_non_alphanum_char_f //
CREATE FUNCTION remove_non_alphanum_char_f (prm_strInput varchar(255))
RETURNS VARCHAR(255)
DETERMINISTIC
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE v_char VARCHAR(1);
DECLARE v_parseStr VARCHAR(255) DEFAULT ' ';
WHILE (i <= LENGTH(prm_strInput) ) DO
SET v_char = SUBSTR(prm_strInput,i,1);
IF v_char REGEXP '^[A-Za-z0-9 ]+$' THEN #alphanumeric
SET v_parseStr = CONCAT(v_parseStr,v_char);
END IF;
SET i = i + 1;
END WHILE;
RETURN trim(v_parseStr);
END
//
------------------
Usage Example:
------------------
select remove_non_alphanum_char_f('Sakib &^*))( scored a brilliant ---=== %% century') ;
Sakib scored a brilliant century
1 row in set (0.00 sec)
Hey, thanks for that Rumman, I couldn't seem to find a working pure MySQL solution to the alphanumeric string stripping on the internet, I found many long-winded non-mysql solutions to this specific problem requested by people searching for this problem in MySQL - the usual (un)Semantic web propagated by un Semantic people, so it was refreshing to find that you actually had a solution, without any of the "fluff". Excellent! I shall endeavor to put more time into MySQL functions i think :)
HI;
thanks for the function remove_non_alphanum_char_f . when i use this in a IF or CASE query i get error [Err] 1271 - Illegal mix of collations for operation 'case'
any help would be appreciated
jeff
Could you please add your query here? Because I could not generate the test case.
hi;
here it is
(CASE
WHEN prod_cat LIKE 'NET' THEN CAT_ID
WHEN prod_cat LIKE 'SOUND' THEN CAT_ID
WHEN prod_cat LIKE 'TV' THEN remove_non_alphanum_char_f(prod.prod)
WHEN prod_cat LIKE 'CAPTURE' THEN catid
END)AS resrult,
But the following query works fine for me :-
SELECT
CASE WHEN remove_non_alphanum_char_f(Q.C) = 'ABC' THEN
1
ELSE
0
END AS C1
FROM
(SELECT 'ABC^87' AS C) AS Q
What is your database collation settings?
EXCELLENT POST
i was looking for a list of blogs for commenting thanks Tutuapp APK
Very Interesting and wonderful information keep sharing this post kindly check
Bluetooth In Windows
Really I Appreciate The Effort You Made To Share The Knowledge. This Is Really A Great Stuff For Sharing. Keep It Up . Thanks For Sharing.
Cloud Training
Cloud Training in Chennai
Great Article
Final Year Project Domains for CSE
Project Centers in Chennai
The figure looks cool. Thank you for sharing the collection. Sp flash tools Leapdroid Deezloader
tutu helper