Is there a non-trigger way to format data on INSERT/UPDATE?
(1) By anonymous on 2020-04-19 23:26:13 [link]
Example: When inserting / updating some column, I want to convert it to lowercase and change dashes to colons. So that, `AB-cD-12` becomes `ab:cd:12` Without using triggers, is it possible to do this at the table definition level, similar to how a `CHECK` constraint is used?
(2) By Ryan Smith (cuz) on 2020-04-20 00:00:58 in reply to 1
Sure, as long as you control the INSERT statement, in which case this will do: ``` INSERT INTO t(a, b) VALUES (111, replace(lower(:myparam),'-',':')); ``` If however you don't, but want to check/adjust an inserted value AFTER the INSERT happened and without running a next query, well, the one and only SQL mechanism for that is a Trigger, so if you won't allow that, then no - you cannot do so.
(3) By Keith Medcalf (kmedcalf) on 2020-04-20 01:32:38 in reply to 1 [link]
You can make the text contents of the column case-insensitive by declaring the column to have the NOCASE collating sequence: ``` create table x ( x text collate nocase ); ``` column x still contains whatever you put into it exactly, but ASCII text will be case insensitive for the purposes of comparison (which includes indexing). So for replacement of characters within a field, no. You must do that yourself.
(4) By Mark Lawrence (mark) on 2020-04-20 05:51:20 in reply to 1 [link]
If space is not an issue then on recent versions of SQLite you could use generated columns to achieve the equivalent.  https://www.sqlite.org/gencol.html