SQLite Forum

Is there a non-trigger way to format data on INSERT/UPDATE?
Login

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