Is there a property that I can add to a column so that it converts its value to lowercase? Instead of doing it for every single value through PHP?
Asked
Active
Viewed 4,348 times
3 Answers
1
You could probably do it through a trigger that fires on insert or update. Myself, I'd rather just create a view that has a lower-case version of the column in question. The SQL for the view might look like
SELECT ID, LOWER(MY_COLUMN) AS MY_COLUMN_LOWERCASE
FROM MY_TABLE;
FrustratedWithFormsDesigner
- 26,726
- 31
- 139
- 202
-
@daniel.tosaba: I've added links for triggers and views. – FrustratedWithFormsDesigner Apr 01 '11 at 20:52
-
excellent, but how do u tell mysql to convert selected column to lowercase? – daniel.tosaba Apr 01 '11 at 20:57
-
@daniel.tosaba: See here: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_lower – FrustratedWithFormsDesigner Apr 01 '11 at 21:16
-
2The one thing that might catch you up here is you'll need to change your code (PHP I believe) to use the view, not query the table anymore. Another option might be to use the lower() function in your select query from PHP. – Erik Apr 01 '11 at 21:40
1
Yes, but don't do it.
If you want exclusively lowercase characters in a column, convert them when you insert (or update) them.
If you need a column to be case insensitive in comparisons, use a case insensitive collation (which are used by default in e.g. utf8 columns)
MarkR
- 62,604
- 14
- 116
- 151
