Tutorial :T-SQL: find an empty column



Question:

In my table there are 10 columns used for storing "items" (game server). When an user picks the item up, it should loop through columns 0-9 (with names "I0".."I9") and returns once the column in current row is empty. I cannot figure out how to do it on the SQL server side. I can only think of IF EXISTS for each column but that is not really good.. Glad for any help! Thanks


Solution:1

Maybe it's better to have table items (user_id, item_id) and limit the count to 10 for each user?

It seems for me to be a good way to normalize your table and solve your problem. Also your table structure becomes more flexible - you can increase or decease the limit dynamically.


Solution:2

It sounds to me like you're trying to find an empty column for a specified user (row). And, an empty column contains NULL. Assuming this is correct and ignoring the concerns about proper design, here's something that will return the number of the first column that contains a NULL value.

SELECT CASE      WHEN I0 IS NULL THEN 0      WHEN I1 IS NULL THEN 1      WHEN I2 IS NULL THEN 2      WHEN I3 IS NULL THEN 3      WHEN I4 IS NULL THEN 4      WHEN I5 IS NULL THEN 5      WHEN I6 IS NULL THEN 6      WHEN I7 IS NULL THEN 7      WHEN I8 IS NULL THEN 8      WHEN I9 IS NULL THEN 9    END AS NextColumn  FROM MyTable  WHERE UserID = 'Me'  ;  


Solution:3

SELECT COALESCE(I0, I1, I2, I3, I4, I5, I6, I7, I8, I9) AS FirstNotNull  FROM MyTable  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »