regex mysql | regex mysql query | mysql regex function

regex mysql | regex mysql query | mysql regex function

• if you want to get the only text and numbers from varchar type field.
• first you have to create on one function in you database.
• than you fired query for using regexreplace();

Note :- 

this function have three argument.

Syntax :- regexreplace(regex,text ,replace text);

1. your regex. 
2. your text which you perform regex.
3. your replace text.

Output :- 

----------------------------------------------------------------------------------

| input                              |   output                         |
----------------------------------------------------------------------------------

| Located (please select) |   Located please select  |

----------------------------------------------------------------------------------

Function :-

DELIMITER $$
 CREATE FUNCTION `regexreplace`(pattern VARCHAR(500),plaintext VARCHAR(500),replaceing VARCHAR(100))
RETURNS VARCHAR(500)
 DETERMINISTIC
 BEGIN
  DECLARE repl VARCHAR(500);
  DECLARE ch VARCHAR(1);
  DECLARE i INT;
  SET i = 1;
  SET repl = '';
  IF plaintext REGEXP pattern THEN
   loop_label: LOOP
    IF i>CHAR_LENGTH(plaintext) THEN
     LEAVE loop_label; 
    END IF;
    SET ch = SUBSTRING(plaintext,i,1);
    IF NOT ch REGEXP pattern THEN
     SET repl = CONCAT(repl,ch);

   ELSE
     SET repl = CONCAT(repl,replaceing);
    END IF;
    SET i=i+1;
   END LOOP;
  ELSE
   SET repl = plaintext;
  END IF;
  RETURN repl;
 END$$
 DELIMITER ;

Query :-

SELECT description as 'Input',
        regexreplace([^A-Za-z0-9 ]',description ,'') as 'Output'
 FROM your table_name
 where description REGEXP '[^A-Za-z0-9 ]';