0

I have a table with a column abc that has some special characters like ",.` . I want to remove those and clean up the column using a stored procedure. I have looked up online ( How to remove all non-alpha numeric characters from a string?) and found that the below function that works. But, I need a procedure that I can execute to clean up the column. I am very new to programming and MySQL and using phpMyAdmin to create a routine. Any help on coverting the below and validating would be of great help.

DROP FUNCTION IF EXISTS alphanum; 
DELIMITER | 
CREATE FUNCTION alphanum( str CHAR(32) ) RETURNS CHAR(16) 
BEGIN 
  DECLARE i, len SMALLINT DEFAULT 1; 
  DECLARE ret CHAR(32) DEFAULT ''; 
  DECLARE c CHAR(1); 
  SET len = CHAR_LENGTH( str ); 
  REPEAT 
    BEGIN 
      SET c = MID( str, i, 1 ); 
      IF c REGEXP '[[:alnum:]]' THEN 
        SET ret=CONCAT(ret,c); 
      END IF; 
      SET i = i + 1; 
    END; 
  UNTIL i > len END REPEAT; 
  RETURN ret; 
END | 
DELIMITER ;

Also, How would I run it ? Should I just create a routine and then just use the call statement to clean up the colum ?

Community
  • 1
  • 1
Kumar
  • 113
  • 1
  • 2
  • 11
  • Have you created this function? Mysql procedure call statement is `Call procedure_name()` and function call statement is `SELECT functionName()` – 1000111 Feb 09 '16 at 05:45
  • I was reading a little and it says that function has a return value and procedure does not...so, to cleanse data, I need to use procedure. I have not created it yet, wanted to see, if I just create the routine in myphpadmin ? – Kumar Feb 09 '16 at 15:15
  • How frequent do you want to call that procedure to do the cleansing job? – 1000111 Feb 09 '16 at 15:18

1 Answers1

0

Just write Update to clean up column values with the help of function you have in question.

Assuming you have table name as table1 and column abc

UPDATE table1
SET abc=alphanum(abc);

This will update table column abc with new value.

Put this query in your procedure.

Abhishek Ginani
  • 4,511
  • 4
  • 23
  • 35
  • Well, I am not sure, if I should directly put it. My understanding after reading is that, functions have return values and procedures are meant to carry out an action. How would I have a return value here where I want to cleanse data ? – Kumar Feb 09 '16 at 15:17
  • Stored procedures not returns as functions. Anyway if you want to get something from it like number of rows updated then put `select row_count();` just after the update statement to get the affected rows. – Abhishek Ginani Feb 09 '16 at 15:21
  • I think you got my question wrong I do not want to return anything. I just want to cleanse data. My goal is to have a procedure and execute it so that it cleanses data. Thats the question. The info I shared is for function and it requires that there is a return value for that...But, I do not want that. Does it make sense ? – Kumar Feb 10 '16 at 01:53