Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL automatic conversion on lowercase

Tags:

mysql

I have multiple web services that write data inside a database table. I'd like to automatic convert uppercase strings into lowercase ones, for a specific field. Is there any mysql function that performs this task?

Suppose this is the table:

id | name | language

Sometimes, inside the language field, web services write an uppercase string (IT). I want to convert it into a lowercase string ("it"), directly inside MySQL.

thanks

like image 464
Mich Dart Avatar asked Nov 28 '22 07:11

Mich Dart


1 Answers

Define triggers on the table:

CREATE TRIGGER lcase_insert BEFORE INSERT ON my_table FOR EACH ROW
SET NEW.language = LOWER(NEW.language);

CREATE TRIGGER lcase_update BEFORE UPDATE ON my_table FOR EACH ROW
SET NEW.language = LOWER(NEW.language);

Then update the existing data:

UPDATE my_table SET language = LOWER(language);
like image 87
eggyal Avatar answered Dec 06 '22 04:12

eggyal