Puzzle of the day:
How to simulate the SIGN function in Oracle SQL by only using CEIL, FLOOR, and ABS Oracle SQL functions along with arithmetic operators? No PL/SQL.
Solution:
SIGN(x)=CEIL(x/(1+ABS(x)))+FLOOR(x/(1+ABS(x)))
In SQL, we can demonstrate it as follows:
WITH r AS ( SELECT dbms_random.VALUE(-999,999) rnd FROM dual CONNECT BY LEVEL<=10 UNION ALL SELECT 0 FROM dual ) SELECT rnd, SIGN(rnd), CEIL(rnd/(1+ABS(rnd)))+FLOOR(rnd/(1+ABS(rnd))) "MySign" FROM r
Result:
RND SIGN(RND) MySign ---------- ---------- ---------- -519.606 -1 -1 -657.62692 -1 -1 414.625079 1 1 736.175183 1 1 268.689074 1 1 -647.12649 -1 -1 338.192233 1 1 784.780876 1 1 -529.69184 -1 -1 -596.56803 -1 -1 0 0 0
As you can see, “MySign” column perfectly matches SIGN column.
Comment:
WITH clause is needed to generate 10 random values in the range of -999 .. +999. “0” value is added to demonstrate a special case as it is unlikely that zero will be randomly generated.
My Oracle Group on Facebook:
If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/
Would you like to read about many more tricks and puzzles?
For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.