Replace nulls with blank spaces in float data type

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 Programming

Replace nulls with blank spaces in float data type

Author

Topic

gs
Starting Member

14 Posts

Posted - 2008-05-08 : 15:42:35

Hello,
I have a simple question. Is it at all possible to replace columns which has nulls with blank spaces for a float data type column.
The columns has null values( written)) in it in some rows and has numbers in other rows . I want to remove nulls before copying it to another file.
Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-08 : 15:47:20

A blank space will be treated as 0 if using float:

declare @f float
set @f = ''
print @f

To replace a null value, you can use COALESCE function.

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

gs
Starting Member

14 Posts

Posted - 2008-05-08 : 15:51:00

Yes you are right. I tried using blank spaces in the columns using update statement, the output substituted it for 0.

My question is can I get rid of zeros in a float data type column and make it as a blank space.

quote:
Originally posted by tkizer

A blank space will be treated as 0 if using float:

declare @f float
set @f = ''
print @f

To replace a null value, you can use COALESCE function.

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-08 : 15:53:19

You can not store a blank space in a float data type as a blank space is character data.

Why do you want to do this though?

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

gs
Starting Member

14 Posts

Posted - 2008-05-08 : 15:56:20

I execute a query and I get an output . Before I copy the output into another file, I want to get rid of the 'NULL' in rows.
Those'NULL' are in columns which have float data type.
Will it work if I use real datatype.
The numbers which I have in the output query needs to have 3 points after the decimal . What other datatype would you suggest which can also handle blank spaces in it.

quote:
Originally posted by tkizer

You can not store a blank space in a float data type as a blank space is character data.

Why do you want to do this though?

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-08 : 15:59:48

When I asked why, I meant what valid reason do you have that requires a blank space instead of 0 or NULL?

You can store a blank space if you are using a character data type such as varchar, but then you're going to have all sorts of issues.

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

gs
Starting Member

14 Posts

Posted - 2008-05-08 : 16:03:20

I wa trying to find a way to work with float numbers. If it is too complicated or impossible, I can just find and replace nulls.

Thanks for all your help.

quote:
Originally posted by tkizer

When I asked why, I meant what valid reason do you have that requires a blank space instead of 0 or NULL?

You can store a blank space if you are using a character data type such as varchar, but then you're going to have all sorts of issues.

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

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-08 : 16:22:50

If you doing some sort of export to text file then you can use the CASE function:

DECLARE @F Float

SET @F = 12.12
SET @F = NULL

SELECT
	CASE 
		WHEN @F IS NULL THEN ''
		ELSE CAST(@F AS VARCHAR(20))
	END AS MyFloat

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources