Tuesday, August 21, 2007

SQL Puzzle, Sort of...

My group was once asked, "What's the first number that contains an 'A' in it?"

This has become a once a week exercise for fun.

I of course started counting from one to see where it might happen. Then I decided to use the magic of SQL to help me go through all the numbers.

On 10g:


SELECT num
FROM
(
SELECT
TO_CHAR(TO_DATE('01/01/4712 BC', 'MM/DD/YYYY BC') + (rownum - 1), 'JSP' ) num
FROM DUAL
CONNECT BY LEVEL < 1000
)
WHERE INSTR( num, 'A' ) > 0;

NUM
-------------------
ONE THOUSAND
I found the format model in the documention and moved on from there. I think my original query was a bit more elegant, but this works.


Pretty cool stuff.

No comments: