CODEWITHSUNDEEP
mysql
Mich Dart
Mich Dart

Reputation: 2422

MySQL automatic conversion on lowercase

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

Upvotes: 4

Views: 19406

Answers (2)

eggyal
eggyal

Reputation: 125865

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);

Upvotes: 30

Sudhir Bastakoti
Sudhir Bastakoti

Reputation: 100175

you could use:

LOWER("some_string");

See: LOWER

Did you mean something like this..

Added: You could convert into lowercase while inserting the data in table, like:

INSERT INTO your_table (name, language)
    VALUES ( "Some Name", LOWER( "SOME VALUE" ) );

Or a better solution would be converting the value to be inserted to language field to lowercase from your server side script before inserting to db.

You could do the automatic conversion thing using triggers though, but i suggest using LOWER() function during fetching your data from table, like:

SELECT LOWER(language) AS language FROM your_table_name

Upvotes: 3

Related Questions