Can I recursively concatenate strings?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Can I recursively concatenate strings?

Shane Dev
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?

desired result -

sqlite>select * from xstrings;
string
xxxx
x
xxxxxxxx
...

P.S I know that substr('xxxxxxxxx', 1, stringlengths.length) would work in
this particular case but then I must know maximum value of
stringlengths.length at the point of time when I construct the query. Is
there a more flexible way?


..
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can I recursively concatenate strings?

R Smith

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can I recursively concatenate strings?

Simon Slavin-3
In reply to this post by Shane Dev


On 22 Nov 2017, at 9:56pm, Shane Dev <[hidden email]> wrote:

> P.S I know that substr('xxxxxxxxx', 1, stringlengths.length) would work in
> this particular case but then I must know maximum value of
> stringlengths.length at the point of time when I construct the query. Is
> there a more flexible way?

Your above solution is the simplest fastest way of getting your result.  It will perform extremely quickly and continue to work if you ever decide to move from SQLite to some other SQL engine.  It is the solution I’d use unless the lengths could get above 200 or so.

A technically cleaner solution would be to implement your own external function which takes an integer and produces the appropriate string.  This would be a lot more work, and would stop working if you switched to some other SQL engine.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can I recursively concatenate strings?

petern
In reply to this post by Shane Dev
Shane.  printf() will pad spaces you can replace with 'x' or whatever.

WITH lengths(id,l) AS (VALUES (1,4),(2,1),(3,9))
SELECT id,l,replace(printf('%'||l||'s'),' ','x')mask FROM lengths;
id,l,mask
1,4,xxxx
2,1,x
3,9,xxxxxxxxx

If printf() weren't available, it would be worth the effort to add your own
extension function that does the exact thing efficiently.   Extensions are
extremely powerful.  You can even write extensions to produce desired side
effects.





On Wed, Nov 22, 2017 at 1:56 PM, Shane Dev <[hidden email]> 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?
>
> desired result -
>
> sqlite>select * from xstrings;
> string
> xxxx
> x
> xxxxxxxx
> ...
>
> P.S I know that substr('xxxxxxxxx', 1, stringlengths.length) would work in
> this particular case but then I must know maximum value of
> stringlengths.length at the point of time when I construct the query. Is
> there a more flexible way?
>
>
> ..
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can I recursively concatenate strings?

Shane Dev
In reply to this post by R Smith
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

On 22 November 2017 at 23:55, R Smith <[hidden email]> wrote:

>
> 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
>   -- xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> xxxxxxxxxxxx
>   -- 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can I recursively concatenate strings?

R Smith
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Can I recursively concatenate strings?

Shane Dev
>
>
> 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)
>
> Previously I used SQL for solely for routine data manipulation and
queries. Then I discovered recursive CTEs by accident while reading
wikipedia https://en.wikipedia.org/wiki/Turing_completeness#Examples
"Turing completeness in declarative SQL is implemented through recursive
common table expressions"
That piqued my interest. https://sqlite.org/lang_with.html became one of my
favourite sqlite pages. I would like to see your CTE example tutorials.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users