Re: sudoku in an sql statement

2009/11/5 marcin mank <marcin(dot)mank(at)gmail(dot)com>:
>> I think the Oracle guy's version could easily be adapted to PG 8.4 ---
>> those little rownum subqueries seem to be just a substitute for not
>> having generate_series(1,9), and everything else is just string-pushing.
>
> indeed.
>
> marcin=# with recursive x( s, ind ) as
> ( select sud, position( ' ' in sud )
>  from  (select '53  7    6  195    98    6 8   6   34  8 3  17   2
> 6 6    28    419  5    8  79'::text as sud) xx
>  union all
>  select substr( s, 1, ind - 1 ) || z || substr( s, ind + 1 )
>       , position(' ' in repeat('x',ind) || substr( s, ind + 1 ) )
>  from x
>     ,  (select gs::text as z from generate_series(1,9) gs)z
>  where ind > 0
>  and not exists ( select null
>                   from generate_series(1,9) lp
>                   where z.z = substr( s, ( (ind - 1 ) / 9 ) * 9 + lp, 1 )
>                   or    z.z = substr( s, mod( ind - 1, 9 ) - 8 + lp * 9, 1 )
>                   or    z.z = substr( s, mod( ( ( ind - 1 ) / 3 ), 3 ) * 3
>                                      + ( ( ind - 1 ) / 27 ) * 27 + lp
>                                      + ( ( lp - 1 ) / 3 ) * 6
>                                   , 1 )
>                 )
> )
> select s
> from x
> where ind = 0;
>                                         s
> -----------------------------------------------------------------------------------
>  534678912672195348198342567859761423426853791713924856961537284287419635345286179
> (1 row)
>
I'd prefer the output be with question and formatted :)

SELECT regexp_replace(regexp_split_to_table(regexp_replace(s,
'.{9}(?!$)', '\\&-', 'g'), '-'), '.{3}(?!$)', '\\&|', 'g') AS answer
,regexp_replace(regexp_split_to_table(regexp_replace(org, '.{9}(?!$)',
'\\&-', 'g'), '-'), '.{3}(?!$)', '\\&|', 'g') AS question
FROM(
SELECT
*, first_value(s) OVER () AS org
FROM
x
)x
WHERE position(' ' in s) = 0;
answer | question
-------------+-------------
534|678|912 | 53 | 7 |
672|195|348 | 6 |195|
198|342|567 | 98| | 6
859|761|423 | 8 | 6 | 3
426|853|791 | 4 |8 3| 1
713|924|856 | 7 | 2 | 6
961|537|284 | 6 | |28
287|419|635 | |419| 5
345|286|179 | | 8 | 79
(9 rows)

Regards,

--
Hitoshi Harada