Monthly Archives: June 2011

Project Euler – Problem 7

By listing the first six prime numbers: 2, 3, 5, 7, 11, and 13, we can see that the 6th prime is 13.

What is the 10001st prime number?

My solution:

DECLARE
    v_cnt PLS_INTEGER := 1;
    v_curr PLS_INTEGER := 2;
    c_num_prime CONSTANT PLS_INTEGER := 10001; 

    FUNCTION is_prime
    (   num_in IN NUMBER
    )
    RETURN BOOLEAN
    IS
        v_is_prime BOOLEAN := TRUE;
    BEGIN

        FOR i in 2..ceil(sqrt(abs(num_in)))
        LOOP
            IF mod(num_in,i) = 0
            THEN
                v_is_prime := FALSE;
                exit;
            END IF;
        END LOOP;

        RETURN v_is_prime;

    END is_prime;
BEGIN
    WHILE v_cnt < c_num_prime
    LOOP
        v_curr := v_curr + 1;
        IF is_prime(v_curr)
        THEN
            v_cnt := v_cnt + 1;
        END IF;            
    END LOOP;
    dbms_output.put_line('prime #'||c_num_prime||': '||v_curr);
END;

As always.. I am no mathmatician, so If you know of a better solution please let me know.

I just learned about NULLIF not too long ago, and again today found a good use case for it. I took me a while to find the function again though, so I figure if I put it here it will be easier to find next time.

Basically the function NULLIF compares 2 values. If they are equal, it returns null, otherwise it returns the first value.

In the example below, new_val1 and new_val2 are equivalent:

with data as
(
    select rownum rn
    from dual
    connect by level <= 5
) 
select
    rn,
    nullif(rn,3) new_val1,
    case when rn = 3 then null else rn end new_val2
from data;

        RN   NEW_VAL1   NEW_VAL2
---------- ---------- ----------
         1          1          1
         2          2          2
         3
         4          4          4
         5          5          5