Thursday, February 24, 2011

Mysql function to remove non-alphanumeric character from a string

12 comments


"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)

12 Responses so far

  1. Anonymous says:

    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 :)

  2. Anonymous says:

    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

  3. Could you please add your query here? Because I could not generate the test case.

  4. JeffSalam says:

    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,

  5. 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?

  6. Unknown says:

    i was looking for a list of blogs for commenting thanks Tutuapp APK

  7. ston sen says:

    Very Interesting and wonderful information keep sharing this post kindly check
    Bluetooth In Windows

  8. 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

  9. The figure looks cool. Thank you for sharing the collection. Sp flash tools Leapdroid Deezloader

Leave a Reply

Labels