Lousy Lousy - 1 year ago 75
SQL Question

Fixed array and random numbers in an SQL query

What's the easiest way to select one of five fixed strings returned in an SQL Server query, randomly?

I.e. the equivalent of:

function randomColumn() {
var values = ['apple', 'banana', 'orange', 'cherry', 'lemon'];
var idx = Math.floor(Math.random() * 5);
return values[idx];

I need to change my existing SQL script to have a certain column return one of these values, without the need to change my client code.

Do I need to create a temp table?

I'm using SQL Server 2008 R2.

Answer Source

You don't need a temporary table for a few strings, you can create the result on the fly:

select str
from (
  select 0 as id, 'apple' as str
  union all
  select 1, 'banana'
  union all
  select 2, 'orange'
  union all
  select 3, 'cherry'
  union all
  select 4, 'lemon'
) x
where id = floor(rand() * 5)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download