SQL quine (self-reproducing SQL)

| category: Programming | author: st
Tags:

A quine is a computer program which takes no input and produces a copy of its own source code as its only output. The challenge for programmers is to write the shortest code in the given language. Here are my examples in Transact SQL and in ANSI SQL.

Note that we do not take in account the output format printing by any console SQL clients. For example, we are unable to suppress the output of column names or separators in the common case.

Example 1. Transact SQL

Transact SQL example is based on PRINT statement. Indeed, the PRINT is the RAISERROR having the severity of 0.

DECLARE @c varchar(255), @b varchar(255), @d char(1)
SELECT @d = ''''
SELECT @b = 'DECLARE @c varchar(255), @b varchar(255), @d char(1)
SELECT @d = '
SELECT @c = 'PRINT @b + @d + @d + @d + @d
PRINT substring(@b, 55, 7) + substring(@c, 7, 2) + substring(@b, 64, 3) + @d + @b + @d
PRINT substring(@b, 55, 7) + substring(@b, 9, 2) + substring(@b, 64, 3) + @d + @c + @d
PRINT @c'
PRINT @b + @d + @d + @d + @d
PRINT substring(@b, 55, 7) + substring(@c, 7, 2) + substring(@b, 64, 3) + @d + @b + @d
PRINT substring(@b, 55, 7) + substring(@b, 9, 2) + substring(@b, 64, 3) + @d + @c + @d
PRINT @c

Example 2. Transact SQL

Transact SQL without output statements. However, the order in UNION is not defined in the common case so we may use UNION ALL or add ORDER BY.

DECLARE @c varchar(255), @b varchar(255), @d char(1)
SELECT @d = ''''
SELECT @b = 'DECLARE @c varchar(255), @b varchar(255), @d char(1)
SELECT @d = '
SELECT @c = 'SELECT @b + @d + @d + @d + @d
UNION ALL SELECT substring(@b, 55, 7) + substring(@c, 8, 2) + substring(@b, 64, 3) + @d + @b + @d
UNION ALL SELECT substring(@b, 55, 7) + substring(@b, 9, 2) + substring(@b, 64, 3) + @d + @c + @d
UNION ALL SELECT @c'
SELECT @b + @d + @d + @d + @d
UNION ALL SELECT substring(@b, 55, 7) + substring(@c, 8, 2) + substring(@b, 64, 3) + @d + @b + @d
UNION ALL SELECT substring(@b, 55, 7) + substring(@b, 9, 2) + substring(@b, 64, 3) + @d + @c + @d
UNION ALL SELECT @c

Example 3. Transact SQL

Same solution but using only one SELECT statement

DECLARE @c varchar(512), @b varchar(512), @d char(1)
SELECT @d = ''''
SELECT @b = 'DECLARE @c varchar(512), @b varchar(512), @d char(1)
SELECT @d = '
SELECT @c = 'SELECT @b + @d + @d + @d + @d + char(13) + char(10) +
substring(@b, 55, 7) + substring(@c, 8, 2) + substring(@b, 64, 3) + @d + @b + @d + char(13) + char(10) +
substring(@b, 55, 7) + substring(@b, 9, 2) + substring(@b, 64, 3) + @d + @c + @d + char(13) + char(10) +
@c'
SELECT @b + @d + @d + @d + @d + char(13) + char(10) +
substring(@b, 55, 7) + substring(@c, 8, 2) + substring(@b, 64, 3) + @d + @b + @d  + char(13) + char(10) +
substring(@b, 55, 7) + substring(@b, 9, 2) + substring(@b, 64, 3) + @d + @c + @d + char(13) + char(10) +
@c

Example 4. ANSI SQL with SUBSTRING function

The SUBSTRING function is a part of SQL standard so it should be implemented in the most of DBMS. However it may have a little different name or signature like SUBSTR in Oracle.

SELECT substring(S.v, 2, 7) + A.v + substring(S.v, 1, 8) + C.v + A.v + C.v +
substring(S.v, 22, 4) + substring(S.v, 20, 2) + substring(S.v, 22, 4) + substring(S.v, 23, 1) + char(13) + char(10) +
substring(S.v, 9, 11) + substring(S.v, 1, 8) + C.v + S.v + C.v + substring(S.v, 22, 4) +
substring(S.v, 20, 2) + substring(S.v, 22, 4) + substring(S.v, 24, 1) + char(13) + char(10) +
substring(S.v, 9, 11) + substring(S.v, 1, 8) + C.v + C.v + C.v + C.v + substring(S.v, 22, 4) +
substring(S.v, 20, 2) + substring(S.v, 22, 4) + substring(S.v, 6, 1)
FROM
(SELECT 'substring(S.v, 2, 7) + A.v + substring(S.v, 1, 8) + C.v + A.v + C.v +
substring(S.v, 22, 4) + substring(S.v, 20, 2) + substring(S.v, 22, 4) + substring(S.v, 23, 1) + char(13) + char(10) +
substring(S.v, 9, 11) + substring(S.v, 1, 8) + C.v + S.v + C.v + substring(S.v, 22, 4) +
substring(S.v, 20, 2) + substring(S.v, 22, 4) + substring(S.v, 24, 1) + char(13) + char(10) +
substring(S.v, 9, 11) + substring(S.v, 1, 8) + C.v + C.v + C.v + C.v + substring(S.v, 22, 4) +
substring(S.v, 20, 2) + substring(S.v, 22, 4) + substring(S.v, 6, 1)
FROM
' AS v) AS A
CROSS JOIN (SELECT '(SELECT CROSS JOIN v) AS ' AS v) AS S
CROSS JOIN (SELECT '''' AS v) AS C

Example 5. ANSI SQL with LEFT and RIGHT functions

The shortest statement which I have done. Do you want to make it shorter?

SELECT left(A.v, 81) + char(39) + A.v + char(39) + right(A.v, 11)
FROM (SELECT 'SELECT left(A.v, 81) + char(39) + A.v + char(39) + right(A.v, 11)
FROM (SELECT AS v) AS A' AS v) AS A

P.S. If you use SQL Server Management Studio, please check that the maximum length of output strings is not limited by 255 characters, and make it about 2000 in the settings.