SQL for list of keywords

I wanted to get a list of my keywords as an alphabetical list and found in the forum the SQL command for this. How cool is that!

But this leaves questions:

Yes and Yes.

Are you concerned that you are seeing something else?

Thanks @Mylio_Deon. I’m not seen much wrong as far a I can tell.

Only odd things in the output is the first line. It says:


but I think I don’t have this keyword in my library. Is it something like a column header?

Yes, column header. The query says to return a list of all unique entries in the MediaKeywords_Keyword field.

It still puts a space in front of most of the keywords for me, but then adds another smaller list without. :frowning:

That happens if you have a space after your commas between the keywords. To not show that space, just use “trim” around the MediaKeywords_Keyword column. e.g.


sql select distinct TRIM(MediaKeywords_Keyword) from (WITH RECURSIVE split(MediaKeywords_id, MediaKeywords_Keyword, rest) AS (SELECT id, ‘’, keywordsstr || ‘,’ FROM Media WHERE id UNION ALL SELECT MediaKeywords_id, substr(rest, 0, instr(rest, ‘,’)), substr(rest, instr(rest, ‘,’)+1) FROM split WHERE rest <> ‘’) SELECT MediaKeywords_id, MediaKeywords_Keyword FROM split WHERE MediaKeywords_Keyword <> ‘’ ORDER BY MediaKeywords_id, MediaKeywords_Keyword) order by MediaKeywords_Keyword COLLATE NOCASE

Thank you, @Deon - I have always used spaces as well as commas in keyword lists, because that’s what other apps I have used produce, and it helps readability.

With the quotes turned back into non-smart ones, that SQL command does remove the spaces - but it still has the shorter second list (presumably of ‘first’ keywords, the ones without a space that start the lists) at the end, rather than a combined and de-duplicated list. Nevertheless, it is a useful list, and not too hard to sort and de-dup outside Mylio.

Thanks for sending this nifty improvment to the keyword sql command, @Mylio_Deon.

But as @aearenda mentioned in passing the command has the wrong quotes; copy-and-pasting it as is does not work! Just emphasising this here, so that nobody else falls over it…

Can you give me an example of what you’re seeing? (TRIM should remove spaces from either end).

Sure - without TRIM, the list would go rather like this, using an underscore to represent a space to avoid confusion:


With Trim, it does this:


Note the repeated keywords after Zebra.