SQLite Forum

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

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

(1) By anonymous on 2020-04-19 23:26:13


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]

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[1] to achieve the equivalent.

[1] https://www.sqlite.org/gencol.html