On 2017/11/23 10:15 PM, Shane Dev wrote:
Perfect! I guessed this could be achieved with a recursive CTE but I could not find one that would produce my desired view. Your CTE is simply a table of strings keyed by the length and then you join it with the stringlengths table to create the final view. Thanks
It's a pleasure, but let me note that I simply made a CTE to do the job because that was the most trivial (to me anyway), I'm not 100% sure it's the most efficient way, but then I have this notion that the question was more out of interest than planning/designing a really big needing-to-be-amazingly-efficient system.
If I were to ever do it in a real system, I would probably compute one string of adequate length in a single-row CTE and just join that and use substr() to copy as many characters as needed from it. I believe that might be more efficient (and more scalable).
PS: If you do like the SQlite features and CTEs (which is one of my favourite additions ever), I could post you the CTE example tutorials made to accompany an sqlite DB manager (which I made very long ago, after the introduction in 3.8 I think) - they have some nifty stuff, like splitting CSV data from a column, drawing graphs etc. (or you can just get it from sqlitespeed yourself - but it's only windows for now, sadly)
There's also real great examples on the sqlite.org pages. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
