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)

• Lake: Thank you very much, your answer is indeed covering my concern.

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

• Martin Smith: Notice any pattern in these results? rextester.com/CJVT91769
• KumarHarsh: Thanks @MartinSmith,check my edited.My answer should not be downvoted as it is like "Test case" for others.