How to prevent NULL values

Question

How to prevent NULL values

Golden_Eye

SSC-Addicted

Points: 463
More actions
November 14, 2013 at 9:05 am

#280202

Hello,
Any suggestions how I can prevent the NULL values in a column when the datatype is float?
Thanks
P.S. ISNULL(mycolumn,'0') is not working

Viewing 6 posts - 1 through 5 (of 5 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Golden_eye (11/14/2013)
Hello,
Any suggestions how I can prevent the NULL values in a column when the datatype is float?
Thanks
P.S. ISNULL(mycolumn,'0') is not working

Pretty sparse on details here. Not sure what you mean that ISNULL(mycolumn,'0') is not working.

Can you make the column NOT NULL?

Do you want to have a default value if a CRUD operation does not provide a value.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Define the column as not nullable and specify a default or use ISNULL(<column name>,0) when querying.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Golden_Eye SSC-Addicted Points: 463 More actions · Answer 3

Sean Lange (11/14/2013)
Golden_eye (11/14/2013)
Hello,
Any suggestions how I can prevent the NULL values in a column when the datatype is float?
Thanks
P.S. ISNULL(mycolumn,'0') is not working
Pretty sparse on details here. Not sure what you mean that ISNULL(mycolumn,'0') is not working.
Can you make the column NOT NULL?
Do you want to have a default value if a CRUD operation does not provide a value.

Hello,

Sorry, let me try to explain better.

I have one column named "mycolumn". Datatype in this column is float (only numbers). I have also NULL values in the column.

How can I remove the NULL values and replace with something else i.e. N/A or 0 or etc.

Thanks!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

If you want to remove the nulls in the table

UPDATE <table name> SET <column name> = 0 WHERE <column name> IS NULL -- (a float cannot contain the string value 'N/A')

If you want to replace any nulls with 0 when you query the table

SELECT ISNULL(<column name>,0) FROM <table name>

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jogesh bisoyi Newbie Points: 5 More actions · Answer 5

Hi ,

Yue can check below query it will work fine

1 . Case When mycolumn IS Null Then 0 Else mycolumn END As mycolumn

OR

2.IsNull(mycolumn,0) As mycolumn