StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<pre><code>set search_path='tmp'; WITH ztab AS ( SELECT idx as idx , substring ( 'WTF!' FROM idx FOR 1) as str FROM generate_series(1, char_length( 'WTF!' )) idx ) SELECT t1.str, t2.str FROM ztab t1 JOIN ztab t2 ON t2.idx > t1.idx ; </code></pre> <p>Result:</p> <pre><code> str | str -----+----- W | T W | F W | ! T | F T | ! F | ! (6 rows) </code></pre> <p>Unfortunately I cannot find a way to avoid the double string constant. (but the whole thing could be packed into a function) If there are no duplicate characters (or you want to suppres them) you could do the anti-join on the str instead of the idx.</p> <p>UPDATE (hint from ypercube) It appears that the OP wants the strings to be concatenated. So be it::</p> <pre><code>WITH ztab AS ( SELECT idx as idx , substring ( 'WTF!' FROM idx FOR 1) as str FROM generate_series(1, char_length( 'WTF!' )) idx ) SELECT t1.str || t2.str AS results FROM ztab t1 JOIN ztab t2 ON t2.idx > t1.idx ; </code></pre> <p>Results:</p> <pre><code> results --------- WT WF W! TF T! F! (6 rows) </code></pre> <p>UPDATE2: (here comes the recursive thingy...)</p> <pre><code>WITH RECURSIVE xtab AS ( WITH no_cte AS ( SELECT 1::int AS len , idx as idx , substring ( 'WTF!' FROM idx FOR 1) as str FROM generate_series(1, char_length( 'WTF!' )) idx ) SELECT t0.len as len , t0.idx , t0.str FROM no_cte t0 UNION SELECT 1+t1.len , tc.idx , t1.str || tc.str AS str FROM xtab t1 JOIN no_cte tc ON tc.idx > t1.idx ) SELECT * FROM xtab ORDER BY len, str -- WHERE len=2 ; </code></pre> <p>Results 3:</p> <pre><code> len | idx | str -----+-----+------ 1 | 4 | ! 1 | 3 | F 1 | 2 | T 1 | 1 | W 2 | 4 | F! 2 | 4 | T! 2 | 3 | TF 2 | 4 | W! 2 | 3 | WF 2 | 2 | WT 3 | 4 | TF! 3 | 4 | WF! 3 | 4 | WT! 3 | 3 | WTF 4 | 4 | WTF! (15 rows) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload