Things on this page are fragmentary and immature notes/thoughts of the author. Please read with your own judgement!

split¶

SELECT
    A.state,
    split.A.value('.', 'VARCHAR(100)') AS String
FROM (
    SELECT 
        state,  
        CAST('<M>' + REPLACE(city, ',', '</M><M>') + '</M>' AS XML) AS string  
    FROM
        TableA
    ) AS A
CROSS APPLY String.nodes ('/M') AS split(a)

Things on this page are fragmentary and immature notes/thoughts of the author. Please read with your own judgement!

Comments¶

1. UPSERT does NOT work with virtual table in SQLite3 currently!

The UPSERT clause (following PostgreSQL syntax) is supported in SQLite 3.24.0+.

:::sql
INSERT INTO players (
    user_name, age
) VALUES (
    'steven', 32
) ON CONFLICT (user_name) DO UPDATE
SET age=excluded.age
;

Tips and Traps¶

1. Do NOT use SQLite3 on network filesystems (NFS, SAMBA, etc).

2. .schema show create statement of a table

3. You can force query to keep the original order of rows by applying order by rowid.

4. SQLite3 supports full-text search by the FTS5 extension (since 3.9.0). It is suggested that you use the porter

FTS5 (Full-text Search) in SQLite3¶

1. If tokens in the search phrase are separated by either spaces or plus signs, then they are matched in order by tokens in the text. To match tokens orderless, you can separate tokens in the search phrase with the keyword AND.