How to determine if SQLite column created with COLLATE NOCASE

Question

A column in a SQLite db must be COLLATE NOCASE. I assume there is no way to add that capability to an existing table, so I'm prepare to recreate the table with it. How can I determine if the existing column is COLLATE NOCASE in order to avoid recreating the table every time it is opened?

Maybe this helps:https://stackoverflow.com/q/1188749/3270427 — McNets, Mar 03 '18 at 16:49

score 3 · Accepted Answer · answered Mar 03 '18 at 16:59

3

How can I determine if the existing column is COLLATE NOCASE

The query

SELECT sql FROM sqlite_master WHERE type='table' AND tbl_name='my_table'

will give you the CREATE TABLE statement for that table. You could inspect the DDL to determine if the column is already defined as COLLATE NOCASE.

answered Mar 03 '18 at 16:59

Gord Thompson

116,920
32
215
418

Thanks Gord - this was the info I was looking for. I can rapidly determine if I need to change MyTable. If so, with SQLite I create a TempTable with the desired NOCASE; INSERT INTO TempTable SELECT * FROM MyTable; DROP TABLE MyTable; and finally 'ALTER TABLE TempTable RENAME TO MyTable'; Very fast :) – bobonwhidbey Mar 03 '18 at 21:51

score 0 · Answer 2 · answered Mar 03 '18 at 16:51

You might not need to do that at all if it is sufficient to change the collations in the query. I mean you can just overwrite it in the query. It won't affect constraints or index, but depending on your use case, it might be good enough.

To be clear: the collate clause in the table definition is just a default for the queries. You can overwrite this in the queries.

e.g.

 WHERE column = 'term' COLLATE NOCASE

or

 ORDER BY column COLLATE NOCASE

However, not that SQLite's LIKE doesn't honor collate clause (use pragma case_sensitive_like instead).

score 0 · Answer 3 · answered Mar 03 '18 at 18:23

The easiest and most general way is store a version number somewhere (in another table, or with PRAGMA user_version).

If you want to check the column itself, use a query with a comparison that is affected by the column's collation:

SELECT Col = upper(Col)
FROM (SELECT Col
      FROM MyTable
      WHERE 0       -- don't actually return any row from MyTable
      UNION ALL
      SELECT 'x'    -- lowercase; same collation as Col
     );

How to determine if SQLite column created with COLLATE NOCASE

3 Answers3