I’ve got a very dull SQLite table with names of Linux packages. Linux software comes in packages with a special naming convention
I want to right split these
pkg strings into three columns -
arch. This is what I would do in Google Sheets or
visidata to work with the data further. However, I am puzzled how to do this in SQLite / Observable, and what would be the best way to proceed?
Have you a link to the database in question and I googled and the best result I got was https://pkgs.org/about/ but they keep the DB hidden.
My guess would be to map over the pkg using JS split on . to construct the datatable you need.
Doing that with SQL.
STRING_SPLIT doesn’t work. I suppose that’s because it is not an SQLite function.
charindex is also not an SQLite function, but it is available.
StackOverflow hack with
reverse to right split prefix from the end of string works, but for the second prefix the query will become unreadable.
There is also SQLite native way to split string using recursion, but it looks intimidating, and I can’t imagine copy/pasting it every time a need to
So I am looking for other SQL functions in Observable to see if there is something like
SPLIT(str, substr, -2) to get me the last prefix.