Extract from last slash till the end in sqlite-CodePudding

I have a table with an url column with urls like so:

https://shop.domain.com/product/12345/this-is-great-product

I need to extract the last part from the last slash to the end:

this-is-great-product

I was planning in using something REGEXP_SUBSTR but its an extension that I don want to install. How can i do this in SQLite version 3.39.4 ?

CodePudding user response：

You can use JSON: convert your delimited string (url) into a valid JSON array, then take the last element:

CREATE TABLE IF NOT EXISTS urls(url);
DELETE FROM urls;
INSERT INTO urls(url) VALUES ('https://shop.domain.com/product/12345/this-is-great-product');

SELECT json_extract('["' || replace(url, '/', '", "') || '"]', '$[#-1]') AS suffix FROM urls;

See the fiddle.