Is there a non-trigger way to format data on INSERT/UPDATE?
(1) By anonymous on 2020-04-19 23:26:13 [link] [source]
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 [link] [source]
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 [source]
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] [source]
If space is not an issue then on recent versions of SQLite you could use generated columns[1] to achieve the equivalent.
[1] https://www.sqlite.org/gencol.html