identify NULL OR '' - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

identify NULL OR ''

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2006-12-05 : 06:53:16

i want to find in one shot what all the column.value is null or empty along with the table name in one database..

for example
gworker(table) oworker(Table)
idno jobcode idno city
111 null 111 c001
222 jo001 254

final report should be:
for idno=222 in gworker table jobcode is null..
for idno254 in oworker table city is empty..

please give ur opinion how to do this???????

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 07:02:13

select * from yourtablenamehere
where isnull(jobcode, '') = '' and isnull(city, '') = ''

select * from yourtablenamehere
where jobcode is null or jobcode = '' or city is null or city = ''

Peter Larsson
Helsingborg, Sweden

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2006-12-05 : 07:15:19

hi Mr.Peter Larsson
i need to find for all columns.value in 'N' no of table under database[hrd]..but u have given for one table..whether with the help of syscolumns it is possible to find all the null values in database along with the table name

quote:
Originally posted by Peso

select * from yourtablenamehere
where isnull(jobcode, '') = '' and isnull(city, '') = ''

select * from yourtablenamehere
where jobcode is null or jobcode = '' or city is null or city = ''

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 07:18:19

You can do this with the help of dynamic SQL.
Read more about this feature at http://www.sommarskog.se/dynamic_sql.html
Also you have already posted this question here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=75771

Peter Larsson
Helsingborg, Sweden

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2006-12-05 : 07:18:28

my prj.manager informed me to find all the null values through the table we have created.if u find the null or empty u should inform along with the tablename in the report??how to overcome this one??????

quote:
Originally posted by Peso

select * from yourtablenamehere
where isnull(jobcode, '') = '' and isnull(city, '') = ''

select * from yourtablenamehere
where jobcode is null or jobcode = '' or city is null or city = ''

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 07:24:44

First you write about this ONE table you created and that your project manager wants you to find all null records in that table.
And second your write about "along the tablename".

select 'yourtablenamehere', * from yourtablenamehere
where isnull(jobcode, '') = '' and isnull(city, '') = ''

sp_msforeachtable 'select ''?'', * from ? where isnull(jobcode, '''') = '''' and isnull(city, '''') = '''''

Peter Larsson
Helsingborg, Sweden

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-12-05 : 08:34:45

Another option:
(psuedocode)


For each Col in All Columns
Begin Try
    If Col is nullable
        Begin Transaction
        Update Table Set Col = null where Col = ''
        Alter Table Set Col is not null
        Rollback Transaction
    End If
End Try
Begin Catch
    RollBack Transaction
    Print 'Col contains nulls'
End Catch

Jay
to here knows when

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources