Replacing NULL values with one string from provided list

Question

I am trying to replace NULL values in table by using SELECT with one (randomized?) string from the set that I provide to the compiler.

Example:

|id | date |
+---+------+    
| 1 | 2017 |
| 2 | NULL |
| 3 | NULL |

I want the NULL values to be '2015', '2012', etc, taken randomly from the set I specify.

Unfortunately COALESCE returns only first not null value rather than one from the specified. Is there some function that works like:

IF column_value = 'NULL (or something)' THEN RAND(string1, string2, string3, string4).

Thanks in advance.

Answer 1

You can use something like

SELECT Id,
       ISNULL(Date, (SELECT TOP 1 *
                     FROM   (VALUES(2015),
                                   (2012)) R(Ryear)
                     ORDER  BY CRYPT_GEN_RANDOM(DATALENGTH(Id))))
FROM   YourTable 

The reference to Id in the ORDER BY is just to make it correlated so that SQL Server is less likely to spool the result and replay it for multiple rows. Select a column that is unique for this.

BTW: There is a CHOOSE function that looks tempting but when this gets expanded out to CASE the random number function gets copied out too and so this is not suitable for the task.

Don't use this

SELECT Id,
       CHOOSE(1 + ABS(CRYPT_GEN_RANDOM(4) % 2),2015,2012)
FROM   YourTable 

Because it is evaluated as

CASE
  WHEN (1 + ABS(CRYPT_GEN_RANDOM(4) % 2)) = 1
    THEN 2015
  ELSE
    CASE
      WHEN (1 + ABS(CRYPT_GEN_RANDOM(4) % 2)) = 2
        THEN 2012
      ELSE NULL
    END
END 

(And CASE ABS(CRYPT_GEN_RANDOM(4) % 2) WHEN 0 THEN 2012 WHEN 1 THEN 2015 END would have the same problem)

Answer 2

Alternatively you can use NEWID() in combination with BINARY_CHECKSUM()

declare @t table(id int,dates varchar(20))
insert into @t VALUES (1,'2017'),(2,NULL),(3,NULL)

declare @parameter table(para varchar(20))
insert into @parameter VALUES ('2015'),('2012')

    SELECT Id,
       ISNULL(Dates, (SELECT TOP 1 *
                     FROM   @parameter 
                     ORDER  BY  (ABS(CAST(
(BINARY_CHECKSUM
(id, NEWID())) as int))
% 100)))
FROM   @t 

Great Martin Smith, I learn new thing now that I will share.

When I use table variable example then Inside (Select top 1 *..) fire for each row ,hence it work fine.It row get diff value

SELECT Id,
       ISNULL(Dates, (SELECT TOP 1 *
                     FROM   @parameter 
                     ORDER  BY  NEWID()))
FROM   @t 

but in same query if I use temp table or permanent table then it fire only once so each null rows get same value

SELECT Id,
       ISNULL(Dates, (SELECT TOP 1 *
                     FROM   @parameter 
                     ORDER  BY  NEWID()))
FROM   #t

So one can deduce one difference between table variable and temp table/permanent.

Therefore one should be beware when using table variable with even 10 rows and more and that too using sub-query like above