SQL - Power

POWER( base, exponent )

SELECT POWER(2,3) ;

Example 1

DECLARE @input1 float; 

DECLARE @input2 float; 

SET @input1= 2; 

SET @input2 = 2.5; 

SELECT POWER(@input1, 3) AS Result1, POWER(@input2, 3) AS Result2; 


Example 2

SELECT  

POWER(CAST(2.0 AS float), -100.0) AS FloatResult, 

POWER(2, -100.0) AS IntegerResult, 

POWER(CAST(2.0 AS int), -100.0) AS IntegerResult, 

POWER(2.0, -100.0) AS Decimal1Result, 

POWER(2.00, -100.0) AS Decimal2Result, 

POWER(CAST(2.0 AS decimal(5,2)), -100.0) AS Decimal2Result; 

Example 3

DECLARE @value int, @counter int; 

SET @value = 2; 

SET @counter = 1; 

 

WHILE @counter < 5 

   BEGIN 

      SELECT POWER(@value, @counter) 

      SET NOCOUNT ON 

      SET @counter = @counter + 1 

      SET NOCOUNT OFF 

   END; 

GO  

SQL - Generate a Range of Numbers between Two Numbers

Example 1 

Tactics...

SELECT ones.n

FROM (VALUES (0.0023),(1.1158),

             (2.6699),(3.7788),

             (4.1122),(5.6699),

             (6.4477),(7.3321),

             (8.0235),(9.2258))

             ones(n)

WHERE n = 3.7788

Example: 3.7788

SELECT ones.n

FROM (VALUES (0.0023),(1.1158),

             (2.6699),(3.7788),

             (4.1122),(5.6699),

             (6.4477),(7.3321),

             (8.0235),(9.2258))

             ones(n)

WHERE n = 1

Example: ...


=======================================================================

SELECT ones.n

FROM (VALUES (0.0023),(1.1158),

             (2.6699),(3.7788),

             (4.1122),(5.6699),

             (6.4477),(7.3321),

             (8.0235),(9.2258))

             ones(n)

WHERE n BETWEEN 1 AND 10

Example: 1.1158,2.6699,3.7788,4.1122,5.6699,6.4477,7.3321,8.0235,9.2258

SELECT ones.n

FROM (VALUES (0.0023),(1.1158),

             (2.6699),(3.7788),

             (4.1122),(5.6699),

             (6.4477),(7.3321),

             (8.0235),(9.2258))

             ones(n)

WHERE n BETWEEN 10 AND 100

Example: ....

=======================================================================

SELECT ones.n

FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n)  

WHERE ones.n  BETWEEN 0 AND 100

ORDER BY 1

Example: 0,1,2,3,4,5,6,7,8,9

SELECT 10*tens.n 

FROM  (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n)

WHERE  10*tens.n  BETWEEN 0 AND 1000

ORDER BY 1

Example: 0,10,20,30,40,50,60,70,80,90

SELECT  100*hundreds.n

FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n)

WHERE 100*hundreds.n BETWEEN 0 AND 1000

ORDER BY 1

Example: 0,100,200,300,400,500,600,700,800,900

SELECT  1000*thousands.n

FROM  (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n)

WHERE 1000*thousands.n BETWEEN 0 AND 1000

ORDER BY 1

Example: 0,1000

SELECT ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n

FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),

     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n),

     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n),

     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n)

WHERE ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n BETWEEN 1000 AND 1020

ORDER BY 1

Example:

1000,1001,1002,1003,1004,1005,1006,1007,1008,1009,1010,1011,1012,1013,1014,1015,1016,1017,

1018,1019,1020

Example 2

WITH x

AS

(

       SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n)

)

SELECT ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n

FROM x ones, x tens, x hundreds, x thousands

ORDER BY 1

WITH ones AS (

 SELECT *

 FROM (VALUES (0), (1), (2), (3), (4),

 (5), (6), (7), (8), (9)) AS numbers(x)

)

SELECT 1000*o1000.x + 100*o100.x + 10*o10.x + o1.x x

FROM ones o1, ones o10, ones o100, ones o1000

ORDER BY x