On 2017/11/22 11:56 PM, Shane Dev wrote:
Let's say I have a table of stringlengths -
sqlite>select * from stringlengths;
length
4
1
9
...
Can I create a view xstrings containing strings (for example of char 'x')
with the lengths specified in stringlengths?
Pretty easily:
-- SQLite version 3.20.1 [ Release: 2017-08-24 ] on SQLitespeed
version 2.0.2.4.
-- Script Items: 5 Parameter Count: 0
-- 2017-11-23 00:53:19.542 | [Info] Script Initialized,
Started executing...
--
================================================================================================
CREATE TABLE SL(id INTEGER PRIMARY KEY, sLength INT);
INSERT INTO SL(sLength) VALUES (4),(1),(9),(72),(5);
SELECT * FROM SL;
-- id | sLength
-- ------------ | -------
-- 1 | 4
-- 2 | 1
-- 3 | 9
-- 4 | 72
-- 5 | 5
CREATE VIEW xstrings AS
WITH SB(i,xs) AS (
SELECT 0, ''
UNION ALL
SELECT i+1, xs||'x' FROM SB WHERE i<=(SELECT MAX(sLength) FROM SL)
)
SELECT xs
FROM SL,SB
WHERE SB.i = SL.sLength
ORDER BY SL.id
;
SELECT * FROM xstrings;
-- xs
--
------------------------------------------------------------------------
-- xxxx
-- x
-- xxxxxxxxx
--
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
-- xxxxx
-- Script Stats: Total Script Execution Time: 0d 00h 00m and
00.031s
-- Total Script Query Time: 0d 00h 00m and
00.001s
-- Total Database Rows Changed: 5
-- Total Virtual-Machine Steps: 2250
-- Last executed Item Index: 5
-- Last Script Error:
--
------------------------------------------------------------------------------------------------
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
