Changing Null Values to Blanks

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

Changing Null Values to Blanks

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-08-15 : 04:19:01

Hey guys
Hope your well

Aim- the following columns “Cancel_Date”, “First_Post_Date”,“Last_Post_Date” can either be populated with figures or have a null value.
I want all Null values to be removed and be replaced with a blank
Final table is

Select *
from #test
left join #SF_AccountBuild on #test.FDMSAccountNo = #SF_AccountBuild.[External FDMSaccountno]

Results

[FDMSAccountNo],
External_ID ,
Parentsfid,
[DBA Name],
[Legal Name],
Street,
[MM3-DBA-ADDR2],
[MM3-DBA-ADDR4],
City,
County,
Postalcode,
Country,
Phone,
Open_Date,
Cancel_Date,
First_Post_Date,
Last_Post_Date,
Account_Id,
External Fdmsaccountno,
[External FDMSaccountno]

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2013-08-15 : 06:22:50

COALESCE(First_Post_Date,' ') AS First_Post_Date, etc

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-15 : 06:36:16

That will set the column to 1900-01-01 if the column is DATE, DATETIME, SMALLDATETIME, DATETIME2 OR DATETIMEOFFSET.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-08-15 : 06:51:43

DonAtWork

Thank you very much :)

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2013-08-16 : 07:56:17

Peter is correct tho, it will return the 1900-01-01 if you actually use a date type column.


COALESCE(CAST(First_Post_Date AS VARCHAR),' ') AS First_Post_Date, etc

Should work if you have actual datetime columns...

HOWEVER... you now have a string instead of a nice datetime type to work with...
perhaps change the data in your front end application instead?

Thanks for the heads up Peter!

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-08-18 : 11:41:29

quote:
Originally posted by SwePeso

That will set the column to 1900-01-01 if the column is DATE, DATETIME, SMALLDATETIME, DATETIME2 OR DATETIMEOFFSET.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Note only to date and time related datatypes, the default value may affect to other datatypes as well. http://beyondrelational.com/modules/2/blogs/70/posts/10841/empty-string-and-default-values.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources