If you are importing data into a table and some of the data like a customer fields has blank spaces on it and you want to get rid of then a trigger for Insert event may a quick solution.
Here is a request:
“
Is it possible to create a trigger that will remove all blanks from a field? They have an check number and when we read it through Import process, it keeps putting random spaces in the number.
”
Sample trigger: DELIMITER // CREATE TRIGGER dwdata2.tr_iRemoveBlankSpaces BEFORE INSERT ON dwdata2.pe_docs_mysql FOR EACH ROW BEGIN /** SET THE NEW VALUE **/ SET NEW.CUSTOMER_NUMBER = REPLACE(NEW.CUSTOMER_NUMBER, ' ', ''); END; //
Notes:
Dwdata2 = name of the database
pe_docs_mysql = name of the table
CUSTOMER_NUMBER = name of the column where the spaces are been removed
Also keep in mind that the quotes, one has a space and the last one does not.
Results:
Data before if goes to database.
Result in Database:
