| Author |
Topic |
|
phaze
Starting Member
42 Posts |
 Posted - 2010-03-29 : 17:50:15
|
| is there a simple way to get rid of null values in my table?i know i can do a update table name and set my columns but i have about 10 columns with a null value that i want to turn into blanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2010-03-29 : 17:57:19
|
| you can maybe useselect table_name, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where table = 'yourtable'dump it to a cursor, loop through each column_name, via dynamic query (EXEC sp_executesql @sql) do UPDATE of those columns that have NULL value?There might be a better way though?<><><><><><><><><><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
 |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2010-03-29 : 17:57:59
|
| there you go Tara Kizer's is even much much better<><><><><><><><><><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
phaze
Starting Member
42 Posts |
Posted - 2010-03-29 : 18:08:19
|
| so i would still have to specify each column that has a null value.is there no way to write a simple update statement where it finds nulls in the entire table and replaces them with a blank? what i am trying to avoid is specifying each column |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
phaze
Starting Member
42 Posts |
Posted - 2010-03-29 : 18:18:07
|
| ok fine i'll stop being lazy |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-03-30 : 05:56:13
|
| You sure you want to do this? NULL values normally there for a good reason (i.e. the value of that column is "Unknown" - that's very different to you just setting the column to "blank"If the column should always contain a value then its definition should be changed to not allow NULLs |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-03-30 : 06:05:36
|
| Also -- If you think about it for a second, depending on the column datatype you'd have to choose a value to replace the NULL. If you are happy with the emmpty string to replace a VARCHAR.. that's not such a terrible assumption but what about a DATETIME or a NUMERIC field? Replacing a NULL with a Zero could have serious consequences on aggregate functions on those columns.Why do you want to remove the NULLS? You've obviously set up the column(s) to allow them so how are they causing a problem for you?Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
how to get rid of nulls in entire table