I’ve got a very dull SQLite table with names of Linux packages. Linux software comes in packages with a special naming convention packagename-version.platform.architecture.
I want to right split these pkg strings into three columns - namever, platform and 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.
Interestingly enough, 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 rsplit something.
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.
Attached there.