This shortest code puzzle asks for 1000000000 without writing that literal or using arithmetic operators. Short-code puzzles always bring a flood of clever answers, and this one is a good test of how well you know T-SQL’s built-in functions.
The Rules
- You may use numbers, but not the literal 1000000000 itself.
- You may not use arithmetic operators.
The benchmark is ten characters, including spaces. Submit the exact code, its character count and the value it returns in SQL Server. Can you meet or beat that length while following both rules?
Before you post, run your answer and check two things. Does it return exactly 1000000000, and not 1000000000.0 or a string? Does it avoid every arithmetic operator, including the ones hiding inside an expression? A clever answer that breaks a rule is still a miss.
A compact expression is not a solution until checked, it is a candidate against the puzzle rules.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
33 Comments. Leave new
select power(10,9)
select ‘1’ + replicate(‘0’,9)
select 1E9
Does the 10 count include a select at the start? Otherwise SELECT POWER(10,9)
I think scientific notation is the answer to this one:
SELECT 1E9
10 characters, including space.
I believe you are correct James!
select convert(int,’10000’+’00000′)
SELECT CAST(CONCAT(‘100000000′,’0’ ) AS INT)
SELECT POWER(10,9)
SELECT CONCAT(‘1’, ‘000000000’)
SELECT SQRT(1E18)
Let’s split hairs….
If you are obeying the rules you cannot use scientific notation. Rule 1 says you cannot use the number “One billion”. 1E9 is the number one billion, just represented differently.
It also doesn’t say that the output format must be standard notation. It says the number “One billion”. So, if Scientific notation is allowed, we can do:
PRINT 1E9
The rules don’t specify the value must be returned, just “produced”.
But if we can’t use scientific notation:
PRINT power(10,9)
But you may consider POWER to be an arithmetic operator, as it is in other languages. In VB you could say
Debug.Print 10^9
My final contribution then, is:
PRINT’1’+’000000000′
Where we drop the space after PRINT because it’s not required for parsing, and the ‘+’ is concatenation and not an arithmetic operator.
Hairs split. What a lovely parting I now have :-)
I love reading this comment. Such a brilliant note.