0

How to check the column value zero or not

I want to insert 50 column values, each column i want to check whethere the value is 0 or not. If the value is 0 then it should be null

Query

insert into table1 values (Case when column1 = '0' then null else column1 end, .....
Case when column50 = '0' then null else column50 end)

The above query is working, but query length is too long because i am using the above query of 50 column's

there is any alternative option is there for check the column value is 0 or not.

like this if(column1, 0) then null

Need Query Help

Gopal
  • 11,712
  • 52
  • 154
  • 229

1 Answers1

4

Try using NULLIF:

insert into table1 values (NULLIF(column1, '0'), .....
NULLIF(column50, '0'))
  • Very very interesting, I didn't know about NULLIF – Alex Jan 09 '12 at 10:43
  • i cannot able to save the string values, for example Nullif('" & column1 & "', '0') then result is 0 only, i am passing column1, column2 as a string. ifnull will not work for string column values.....? – Gopal Jan 09 '12 at 11:53
  • @Gopal: It should do - see the MSDN article linked in the answer. –  Jan 09 '12 at 11:58
  • if i am giving nullif(column1, '0') then it is working, if i am giving a as string, a = column1, nullif('" & a & "', '0') then it is not working, it is saving as 0 instead of null, can you check please.... – Gopal Jan 09 '12 at 12:09