How to keep collation after substr()?

(1) By anonymous on 2022-01-28 23:32:06 [link] [source]

Although a column is defined with collate nocase, when passing the value through substr, the collation is lost.

CREATE TABLE t(s text collate nocase);

INSERT INTO t VALUES('AAA'),('aaa');
CREATE VIEW a AS SELECT s FROM t;
CREATE VIEW b AS SELECT substr(s,1,3) s FROM t;

SELECT s FROM t WHERE s = 'aaa';
+-----+
|  s  |
+-----+
| AAA |
| aaa |
+-----+
SELECT s FROM t WHERE substr(s,1,3) = 'aaa'; -- expected both rows
+-----+
|  s  |
+-----+
| aaa |
+-----+

SELECT s FROM a WHERE s = 'aaa';
+-----+
|  s  |
+-----+
| AAA |
| aaa |
+-----+
SELECT s FROM b WHERE s = 'aaa'; -- expected both rows
+-----+
|  s  |
+-----+
| aaa |
+-----+

Is this expected? If so, how to overcome?

(2.1) By Keith Medcalf (kmedcalf) on 2022-01-28 23:48:26 edited from 2.0 in reply to 1 [source]

The collation attribute (and affinity) travel (are attributes of) the column. Expression results do not have any affinity nor do they have a collation.

Use the following:

create view b as select substr(s, 1, 3) collate nocase s from t;

which will "attach" the collation attribute NOCASE to the result of the expression substr(s, 1, 3).

You need to do similarly as this:

SELECT s FROM t WHERE substr(s,1,3) = 'aaa' collate nocase;

SELECT s FROM t WHERE substr(s,1,3) collate nocase = 'aaa';

The former attaches the collate nocase to the equality operator (that is, the standard manner). The latter attaches the collate nocase attribute to the result of the expression substr(s, 1, 3) which is then used by the equality operator to perform a comparison using the NOCASE collation.