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.


|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.

2 Answers 2


You can use something like

       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

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

Because it is evaluated as

  WHEN (1 + ABS(CRYPT_GEN_RANDOM(4) % 2)) = 1
    THEN 2015
      WHEN (1 + ABS(CRYPT_GEN_RANDOM(4) % 2)) = 2
        THEN 2012

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

  • Thank you very much, your answer is indeed covering my concern.
    – Lake
    Nov 23, 2017 at 21:15

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(
(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

       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

       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

  • 1
    Notice any pattern in these results? rextester.com/CJVT91769 Nov 24, 2017 at 7:34
  • Thanks @MartinSmith,check my edited.My answer should not be downvoted as it is like "Test case" for others.
    – KumarHarsh
    Nov 24, 2017 at 8:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.