find columns with null value

Question

find columns with null value

scottichrosaviakosmos

SSCommitted

Points: 1889
More actions
August 26, 2010 at 7:08 am

#223114

i have a table and i am writing a procedure. my table has a unique column eg:u_column .
Now, i want to know on which u_column its which columns are null or no values and to select those blank columns and show in resultset.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

scottichrosaviakosmos (8/26/2010)
i have a table and i am writing a procedure. my table has a unique column eg:u_column .
Now, i want to know on which u_column its which columns are null or no values and to select those blank columns and show in resultset.

does this help?

--nulls

select * from [my table]

where u_column IS NULL

--blanks, which might be different:

select * from [my table]

where LTRIM(RTRIM(u_column)) = ''

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

scottichrosaviakosmos SSCommitted Points: 1889 More actions · Answer 2

no, i think you have not understood my issue.

I have this u_column as a unique column name and i want to find suppose on u_column=5 ie. on this id's row which all columns are blank and to display those columns names.

Abhijit More SSCertifiable Points: 6897 More actions · Answer 3

Ok let me ask you in this way,

Say you have 1 table having 10 columns out of which Col1 is unique and Col 3 to Col9 can be nullable columns.

If you pass RowId= 5 then it shud see the rowid for which Col3 to Col9 have NULL data.

please correct me if misunderstood.

Abhijit - http://abhijitmore.wordpress.com

scottichrosaviakosmos SSCommitted Points: 1889 More actions · Answer 4

yes , exactly. i want to find from col1 to col9 which all columns in that row have null values and display those columns to user.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 5

Try this mate (replace <YourTableName> with your table name <YourIDColumn> with your rowid column )

declare @sql varchar(4000)

set @sql = ''

select @sql = @sql + '+ ISNULL((NULLIF( (ISNULL(CAST( '+ column_name+ ' AS VARCHAR ) , '''+ column_name +''')) , '+column_name+')),'''')'

FROm information_schema.columns

where table_name = '<YourTableName>'

SELECT @sql = STUFF(@sql,1,1,'')

SELECT @sql = 'SELECT' + @sql + ' FROM <YourTableName> WHERE <YourIDColumn> = 2'

--SELECT @sql

EXEC (@SQL)

The above will print the columns that have null value in a given row...

scottichrosaviakosmos SSCommitted Points: 1889 More actions · Answer 6

can you elaborate this query, i am not getting what is column_name .

since i am passing rowid from application so according to application is this query working ?.

thanks

scottichrosaviakosmos SSCommitted Points: 1889 More actions · Answer 7

sorry i got it.

but slight issue, actually i m displaying this value in an application so i want all null valued columns to be displayed in a different columns.

eg: col1 is null then col1 name on seperate column and col2 in seperate column so that i can easyly show this values in a grid on my application.hope u are getting what i am trying to explain.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 8

scottichrosaviakosmos (8/26/2010)
sorry i got it.
but slight issue, actually i m displaying this value in an application so i want all null valued columns to be displayed in a different columns.
eg: col1 is null then col1 name on seperate column and col2 in seperate column so that i can easyly show this values in a grid on my application.hope u are getting what i am trying to explain.

I got what you are saying.. but isnt it pain? Number of "NULL" columns will differ for each row and if RowID is not unique, you will be getting an inconsistent number of columns as a result.. Now, is your ID column unique? If it is, then the query will always result in only one row, then we will be able to do what you say...i will change the code upon your confirmation..

scottichrosaviakosmos SSCommitted Points: 1889 More actions · Answer 9

ya its ok .. i can manage one row at a time but i want all columns names.