Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Operators

Author  Topic 

gupta
Starting Member

25 Posts

Posted - 2011-03-08 : 14:09:48
Hi Folks,

May I know the command to get 'NULL' values from different columns.

I Will be greatful if anybody help me to get solution for my query.

Thanks you and Will look forward for your reply.

Thanks,
M.Harinath Gupta.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-08 : 14:29:23
WHERE Column1 IS NULL

Is that what you mean?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

gupta
Starting Member

25 Posts

Posted - 2011-03-08 : 14:43:18
OK Will explain you more clear.

Here is my Table name and column name's

Class

Custname custsize cid

MCA 25 100
MTECH 10 101
BETCH 20 102
BCA NULL NULL

My doubt is that. I have tow NULL values in two different columns, so I need a command to get those NULL values as a output.

Thanks,
M.Harinath GUpta.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-08 : 14:59:37
What problem are you having with "IS NULL"?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

gupta
Starting Member

25 Posts

Posted - 2011-03-09 : 04:38:26
I explained you clearly with a columns in a Table but still you are asking the same question.


Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-03-09 : 04:47:08
No need to be rude about it. Your question was unclear (and it still is!) so you leave it up to us to guess what it is you want. Now, is this it? ->

select * from class where custsize is null and cid is null

- Lumbago
My blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/
Go to Top of Page

gupta
Starting Member

25 Posts

Posted - 2011-03-09 : 14:37:36
Hey you hurted me, I am not rude, I just that I explained clearly, how you can think yourself that I am rude?, pl so not say this next time, anyway you catched my question,

hey you are really great the command you provided is correct and working out for me.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-09 : 14:38:42
You didn't explain yourself clearly, that's the problem. It may be clear to you, however it is not clear to us.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

gupta
Starting Member

25 Posts

Posted - 2011-03-09 : 14:40:22
Same question, but little bit change in question.

If I have NULL values in more than multiple columns(10columns), what command I have to use?.

Thanks,
M.Harinath Gupta.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-09 : 14:44:09
You have to specify each you want to check.

WHERE Column1 IS NULL AND Column2 IS NULL AND... (or maybe you want OR, not sure as your problem isn't clear yet)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

gupta
Starting Member

25 Posts

Posted - 2011-03-09 : 15:05:53
hmmm, ok

will explain again with more detailed.

For Example: I created one table in that same created table I have 10columns, here is the table diagram and data in the columns.

Custname custsize cid custmarks custper CustNo

MCA 25 100 10 5 NULL
MTECH 10 101 NULL 2 7
BETCH 20 102 30 NULL 5
BCA NULL NULL 40 10 9

like this so on..... different columns have NULL values.

so here what I need is, I need to get output of the columns who are have only NULL values.

It does not mean if we give this command "WHERE Column1 IS NULL AND Column2 IS NULL AND...
every time we need to write colname NULL and colname NULL we cannot write this for 10times.

So, I think you got my Question.


Go to Top of Page

gupta
Starting Member

25 Posts

Posted - 2011-03-09 : 15:10:31
For Example: we have 25columns, in 25columns in each column we have one NULL value,it does not make any sense if we go on write the command like this.

WHERE Column1 IS NULL AND Column2 IS NULL AND Column3 IS NULL AND Column4 IS NULL AND Column5 IS NULL AND Column6 IS NULL AND Column7 IS NULL AND Column8 IS NULL AND..........

so, is there any command to get only NULL values instead of writing 25times.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-09 : 15:16:17
Yes you have to write it for each of the columns. You can script this via the INFORMATION_SCHEMA.COLUMNS view, however it still has to be specified for each column.

Perhaps you need to reconsider your table design. Consider moving your nullable columns to an entire attribute design.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

gupta
Starting Member

25 Posts

Posted - 2011-03-09 : 15:28:43
Sorry, actually I did not understand INFORMATION_SCHEMA.COLUMNS view what this means?.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-09 : 15:45:45
You would use that view to generate your code for you. I'm pressed for time, so I can't help you further, but do a select on that view to see what I mean.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

gupta
Starting Member

25 Posts

Posted - 2011-03-10 : 04:08:42
Thanks Tkizer, thanks for giving solution to my question, the command you provided for me is working out

Thanks,
M.Harinath Gupta.
Go to Top of Page

gupta
Starting Member

25 Posts

Posted - 2011-03-12 : 04:54:06
Guys do anybody know, how to keep "WITH CHECK OPTION" when creating a table.
Go to Top of Page
   

- Advertisement -