Generating Random numbers in a SELECT statement using MS SQL Server

Generating random numbers in SQL statement is not as trivial as it may look. Simply invoking the rand() method in a query will not work. For example, if you have a table with 1000 rows, doing:

select rand() from my_table

Will not work.

The result of this query will be 1000 rows of the same random number. MS SQL optimizer evaluates the rand() function only once.

In order to override this problem, we can do the following steps:

1) Create a view named: random_view that selects rand() function:

create VIEW [dbo].[random_view]
AS
SELECT rand() 'rnd'   

2) Create a function that makes a select on the random_view:

CREATE FUNCTION [dbo].[random]()
RETURNS float
AS
BEGIN
return (select rnd from random_view)
END

Now we can use our new generated function in a any query:

select dbo.random() from my_table

Assuming my_table has has 1000 rows, we will get 1000 different random numbers.

Inputting only numbers using Java Script and HTML

The basic input text field of HTML is very limited when it comes numeric data. There are times in which a better user experience is needed.
In order to generate a better user experience, 2 main features can be easily added to the input text field by using JavaScript:

• Allow user to input only numeric data. That means that any character other than numbers dots or commas is not allowed. This feature prevents from the user to entering undesired data to the field.
• Show data in a formatted manner when field is not edited. That means that when field lost focus, its contents are shown with commas or dots. That makes the numeric data on the text field to be more readable.

Note, that we also would like the text to be formatted according to a specific Locale. That means that we want to use decimal separator and grouping separator to be according to our demands. There are countries and cultures in which the decimal separator is comma and some other countries in which decimal separator is dot. The same applies to grouping separator.
In order to achieve our 2 goals we will build 3 java script functions. Each function will be assigned to a specific JavaScript event.

• onkeypress: This event is activated whenever a key is being pressed when the cursor is in the text field. A function that returns "false" on this event cancels the event and thus prevent from the pressed character to be updated on the text field. We will assign to this even a function named: numbersOnly. This function receives 4 parameters:
  • field: The text field that called the function.
  • e: The onkeypress event.
  • isDec: Indicate that we would like the input to be decimal and not only integer.
  • decimalSeperator: The character used as a decimal separator (probably “.” or “,”).
  In return this function will return true whenever the pressed character is allowed to be inputted by the user or false if character is not allowed to be inputted.
• onfocus: This event is activated when the text field receives focus. That means, when we are entering to the text field. This event will be assigned with a function named: plainNumber. This function takes the numeric data in the text field and converts it to a plain number, meaning, any numeric formatted data containing dots or command will be converted to a plain number ready to be edited. This function has 2 parameters:
• field: The text field field that called the function.
• decimalSeperator: The character used as a decimal separator (probably “.” or “,”).
• onblur: This event is activated when the field loses focus. That means when we are entering the field by moving to another element on the page. This event will be assinged with a function named: formattedNumber. This function converts the plain numeric data on the text field to a formatted data containing grouping separators. This function has 3 parameters:
• field: The text field field that called the function.
• decimalSeperator: The character used as a decimal separator (probably “.” or “,”).
• groupingSeperator: The character used as a grouping separator (probably “.” or “,”).

I made an example showing a text field utilizing the 3 events to create the described behavior. The example contains a simple HTML page containing the JavaScript methods and a simple text field. The JavaScript code used in this example is not state of the art, but working fine on both FireFox and Explorer. This code can be further refined by encapsulating all the behavior to a single static JavaScript class.

Here is the example code showing input numbers only of an Integer number:

<html>
 <head>
  <title> JavaScript Input Example </title>
 </head>
 <body>
<script>
function numbersOnly(field, e, isDec, decimalSeperator)
{
  var key;
  var keychar;
if (window.event)
   key = window.event.keyCode;
else if (e)
   key = e.which;
else
   return true;
  keychar = String.fromCharCode(key);
// control keys
  if ((key == null) || (key == 0) || (key == 8) ||
      (key == 9) || (key == 13) || (key == 27) || (key == 39) /*|| (key == 37)*/)
  {
    return true;
  }
  else if ((("0123456789").indexOf(keychar) > -1) ||
    (isDec && (keychar == decimalSeperator) && (field.value.indexOf(decimalSeperator) < 0)))
  {
    return true;
  }
 return false;
}
function formattedNumber(field, decimalSeperator, groupingSeperator)
{
    field.value = addGroupingSeperator(field.value, decimalSeperator, groupingSeperator);
}
function plainNumber(field, decimalSeperator)
{
    field.value = field.value.replace(
        new RegExp("[^0123456789" + decimalSeperator + "" + "]", 'g'), "");
}
function addGroupingSeperator(nStr, decimalSeperator, groupingSeperator)
{
    nStr += '';    
    x = nStr.split(decimalSeperator);
    x1 = x[0];
    x2 = x.length > 1 ? decimalSeperator + x[1] : '';
    var rgx = /(\d+)(\d{3})/;
    while (rgx.test(x1)) {
        x1 = x1.replace(rgx, '$1' + groupingSeperator + '$2');
    }
    return x1 + x2;
}
</script>
    <input id="inputInteger" name="inputInteger" maxLength="7"
       onfocus="plainNumber(this, '.');"
       onblur="formattedNumber(this, '.', ',')"
       onkeypress="return numbersOnly(this, event, false, '.')">
 </body>
</html>

Note that the data of the decimal and grouping separator can be taken from your programming language and assigned dynamically according to the specific user Locale information.

Generating a sequence of numbers using plain SQL query

SQL queries are usually used to get information from one or more table(s). But there are times it is useful to simply generate a sequence of numbers. The numbers can be joined with some another table, to create a complete list.

For example, suppose we have a log table named “log” containing only 2 columns: the day of the year starting from 0 and some counted value for that day. The 2 columns are named: “day” and “count”.

For things to be easy, lets assume the days in the log table can be from “0” to “99”.

Note, that there may be days in the log table that are not counted at all. For example, the log may look like:

Day	Count
0	345
1	434
3	255
8	345
13	445

And so on…

Now, suppose we need to output a report, showing the entire log table, including days that has no counts in the log. That means, we need somehow to fill the “gaps” in the log table in order to show a full list of days from “0” to “99”.

In order to produce the report, we need to create a table containing a sequence of numbers from “0” to “99” and than join that table with our “log” table.

Generating a sequence of numbers in plain SQL query is not a trivial task, but it can be done, by taking advantage of the SQL UNION command and using the SQL property: Cartesian join also known as Cross join.

The idea is to create 2 tables:

• One table contains numbers from 0 to 9.
• Second table contains numbers: 0, 10, 20, … 90.

Then, cross join the tables and sum the join products.

The cross join looks like:

Table 1	Table 2	Sum
0	0	0
1	0	1
…	…	…
9	0	9
0	10	10
1	10	11
…	…	…
9	10	19
0	20	20
1	20	21
…	…	…
9	20	29

And so on until 99…

The SQL query looks like:

select t1.x + t2.x
from
(select 0 as `x` union
select 1 union
select 2 union
select 3 union
select 4 union
select 5 union
select 6 union
select 7 union
select 8 union
select 9) as t1,
(select 0 as `x` union
select 10 union
select 20 union
select 30 union
select 40 union
select 50 union
select 60 union
select 70 union
select 80 union
select 90) as t2
order by 1

Now, in order to show complete list of days and log counts for the log table in the example:

select all_days.day, log.count from
(select t1.x + t2.x as 'day'
from
(select 0 as `x` union
select 1 union
select 2 union
select 3 union
select 4 union
select 5 union
select 6 union
select 7 union
select 8 union
select 9) as t1,
(select 0 as `x` union
select 10 union
select 20 union
select 30 union
select 40 union
select 50 union
select 60 union
select 70 union
select 80 union
select 90) as t2
order by 1) as all_days left join log on all_days.day = log.day

Suppose you want a sequence of numbers that is different from 0 to 99. For example, you want a range of numbers between 450 to 480. You can easily change the query (the first one) to do it, by using simple add operation and using the WHERE clause:

select 450 + x from
(select t1.x + t2.x as `x`
from
(select 0 as `x` union
select 1 union
select 2 union
select 3 union
select 4 union
select 5 union
select 6 union
select 7 union
select 8 union
select 9) as t1,
(select 0 as `x` union
select 10 union
select 20 union
select 30 union
select 40 union
select 50 union
select 60 union
select 70 union
select 80 union
select 90) as t2
order by 1) as `numbers_sequence` where 450 + x <= 480

You can use this trick also if you need to generate a range of dates. All you have to do is to use the specific function of your SQL engine (mySQL, SQLServer etc’) that allows adding number of days to a date.

It is important to mention, that there are more solutions that can achieve the same effect:

• Add the missing data in a stored procedure code.
• Add the missing data in a your programming language code (Java, PHP, etc’).

Every method has its own advantages and disadvantages: Stored procedures are not always available, but very efficient and allows you to “speak” directly in the database terminology. Adding the data in your programming language may be a bit more complex. You also don’t always have the privilege of manipulating the data in a programming language. For example, if you are using a generic reporting system, that automatically plots the data of a Resultset. From the other hand, as was said before: you don’t always have the option of using stored procedures. If both: stored procedures and programming language are not available, you can always use the query on this example. It is simple, fast and leaves the solution on the domain of the SQL engine.