Thursday, February 24, 2011

Mysql function to remove non-alphanumeric character from a string

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

13 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. i was looking for a list of blogs for commenting thanks Tutuapp APK

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

  10. Project says:

    Great Article
    Cloud Computing Projects


    Networking Projects

    Final Year Projects for CSE


    JavaScript Training in Chennai

    JavaScript Training in Chennai

    The Angular Training covers a wide range of topics including Components, Angular Directives, Angular Services, Pipes, security fundamentals, Routing, and Angular programmability. The new Angular TRaining will lay the foundation you need to specialise in Single Page Application developer. Angular Training

Leave a Reply

Labels