More about self-reproducing T-SQL

In yesterday's entry, I talked about self-replicating programs and how educational it can be to construct them.  Commonly known in hacking parlance as quines, these self-reproducing programs force us to think about how our language works and are a good way to pass the time when your employer thinks you should be working.

I posed a challenge to those of you out there in the blogsphere to write a self-replicating program in T-SQL.  The submissions I received were of two distinct varieties:  those that tried to cheat by accessing a cached copy of the source code somewhere external to the code (e.g., from syscomments) and those that genuinely tried to replicate themselves.  Some readers tried rendering an answer using SELECT, but then quickly changed course to PRINT when they realized the original (their T-SQL batch) wasn't a result set (i.e., it had no column header), so its replica couldn't be one, either.

Of the ones that used PRINT and actually took a stab at writing code that really reproduced itself, there were again two distinct varieties:  those that took a fresh, albeit sometimes convoluted, approach and those that merely translated code found elsewhere on the web.  Here's an example of one that takes a fresh (and T-SQL-specific) approach to creating a quine:

declare

@ char(444)

set

@ = 'declare @ char(444)

set @ = "declare @ char(444)"

exec sp_executesql N"print left(@,29)+char(39)+@+char(39) print right(replace(@,0x22,0x27),113)",N"@ char(444)",@'

exec

sp_executesql N'print left(@,29)+char(39)+@+char(39) print right(replace(@,0x22,0x27),113)',N'@ char(444)',@

This one was by Steve Kass and is a pretty good stab at the problem considering the constraints.  Here's another:

print replace(space(1)+char(39)+space(1)+char(39)+char(41),space(1),'print replace(space(1)+char(39)+space(1)+char(39)+char(41),space(1),')

This one is by Itzik Ben-Gan and is actually a riff on the many one-line C/C++ quines out there.  Many of these C/C++ quines have been translated into other languages, including T-SQL.  This particular one is a version of one originally linked on the SQL Server MVP newsgroup by Anith Sen.  To see how the translation from C/C++ works, check out this page of C/C++ quines, for example.  If you take the top example on this page and translate it to T-SQL, you basically have the above query. 

I intentionally avoided the use of the word "quine" and the term "self-reproducing code" in the original post in order to get people to think and to prevent them from just grabbing a canned solution off the 'net.  That said, I think both approaches deserve a good look.  Working through either would be beneficial to most T-SQL developers.  The first one gets a prize for originality; the other for having the sense to prefer the tried-and-true over reinventing the wheel.  I encourage those of you who haven't already done so to have a look at yesterday's entry and work through some of the code that was submitted.  I think these things provide an opportunity to learn the language from the inside out and give us a fresh perspective on developing sophisticated apps with it.

Comments

• Anonymous
  October 26, 2005
  The comment has been removed
• Anonymous
  October 31, 2005
  another version:
  
  select replace ( a.a, char ( 37 ) + char ( 115 ), char ( 39 ) + a.a + char ( 39 ) ) from ( select 'select replace ( a.a, char ( 37 ) + char ( 115 ), char ( 39 ) + a.a + char ( 39 ) ) from ( select %s as a ) as a' as a ) as a
• Anonymous
  November 05, 2005
  The comment has been removed
• Anonymous
  September 13, 2006
  ? good site, good short contents of the[url=http://plussizewomen.blogo.pl/]plus size women[/url]good work ?ongratulations !<a href=" http://plussizewomen.blogo.pl/ ">plus size women</a>