CODEWITHSUNDEEP
mysql
hpaknia
hpaknia

Reputation: 3118

how to manipulate a mysql column field on insert?

In Persian (it's better to say Farsi), a decimal number like 32.98 is written 32/98 (slash is used instead of point)

For Example I have a table X and column L(varchar 20). I want to define a MySQL function that replaces any / with. In case of inserting any value to L, values are replaced before saving.

For sure it can be achieved in executed query, but I want to do it in MySQL.

Upvotes: 2

Views: 209

Answers (1)

John Woo
John Woo

Reputation: 263733

Just use REPLACE()

SELECT L AS origL, REPLACE(L, '/', '.') AS newL

So in your INSERT statement,

INSERT INTO X (L) VALUES(REPLACE('yourValueHere', '/', '.'))

UPDATE 1

using Trigger:

DELIMITER $$
CREATE TRIGGER replaceToPeriod
BEFORE INSERT ON X
FOR EACH ROW 
BEGIN
    SET NEW.L = REPLACE(NEW.L, '/', '.');
END $$
DELIMITER ;

After the TRIGGER has been executed, you can now directly insert a record and the trigger replaces the value automatically.

INSERT INTO X (L) VALUES('32/97')

UPDATE 2

using STORED PROCEDURE

DELIMITER $$
CREATE PROCEDURE InsertRecord(IN _val VARCHAR(20))
BEGIN
    INSERT INTO X (L) VALUES(REPLACE(_val, '/', '.'));
END $$
DELIMITER ;

calling the procedure:

CALL InsertRecord('32/97');

Upvotes: 6

Related Questions