5

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.

7

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)

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

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

  • 1
    Notice any pattern in these results? rextester.com/CJVT91769 – Martin Smith Nov 24 '17 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 '17 at 8:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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