SQL Null

--==I want to turn the null's into '0' also.


USE [Test-upload]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[income](
      [Dollars] [money] NULL
) ON [PRIMARY]


INSERT INTO [Test-upload].[dbo].[income]
           ([Money])
     VALUES(0)
INSERT INTO [Test-upload].[dbo].[income]
           ([Money])
     VALUES(33)
INSERT INTO [Test-upload].[dbo].[income]
           ([Money])
     VALUES(null)
INSERT INTO [Test-upload].[dbo].[income]
           ([Money])
     VALUES(null)
INSERT INTO [Test-upload].[dbo].[income]
           ([Money])
     VALUES(84.00)
INSERT INTO [Test-upload].[dbo].[income]
           ([Money])
     VALUES(877)
INSERT INTO [Test-upload].[dbo].[income]
           ([Money])
     VALUES(23)
INSERT INTO [Test-upload].[dbo].[income]
           ([Money])
     VALUES(234.00)      
INSERT INTO [Test-upload].[dbo].[income]
           ([Money])
     VALUES(null)
INSERT INTO [Test-upload].[dbo].[income]
           ([Money])
     VALUES(null)


--=======================================================
SELECT

replicate('0',13 - len(replace(cast([Dollars] as varchar(13)), '.', '')))  +
replace(cast([Dollars] as varchar(13)), '.', '')

FROM [Test-upload].[dbo].[income]


WDJ98338Asked:
Who is Participating?
 
appariConnect With a Mentor Commented:
you need to use isnull on all occurances as follows

SELECT

replicate('0',13 - len(replace(cast(isnull([Dollars],0) as varchar(13)), '.', '')))  +
replace(cast(isnull([Dollars],0) as varchar(13)), '.', '')

FROM [Test-upload].[dbo].[income]

back to top
0
 
HuyBDCommented:
try to use ISNULL(orig_valuge,0) function

HuyBD
0
 
WDJ98338Author Commented:
It did not work

SELECT

replicate('0',13 - len(replace(cast(isnull([Dollars],0) as varchar(13)), '.', '')))  +
replace(cast([Dollars] as varchar(13)), '.', '')

FROM [Test-upload].[dbo].[income]
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Jai STech ArchCommented:
SELECT  isnull(replicate('0',13 - len(replace(cast([Dollars] as varchar(13)), '.', '')))  +
replace(cast([Dollars] as varchar(13)), '.', ''),0)

FROM [dbo].[income]
0
 
Jai STech ArchCommented:
you dont need ISNULL for all occurances...just put ISNULL for your entire column as given above in my post

SELECT  isnull(replicate('0',13 - len(replace(cast([Dollars] as varchar(13)), '.', '')))  + replace(cast([Dollars] as varchar(13)), '.', ''),0)
FROM [dbo].[income]
0
 
appariCommented:
if you see the sql expression, i think he is formatting the value. in that case using isnull outside the expression returns 0 where as using isnull on all occurances returns 0000000000000
0
 
Jai STech ArchCommented:
0 is always 0...i think the formatting is wrong then..its upto the author on WHY "0" has to be formatted to "000000000000"
0
 
WDJ98338Author Commented:
This did not work, the data should look like this.

0000000000000
0000000000000
0000000000000
0000000000000
0000000000000
0000000003300
0000000000000
0000000000000
0000000008400
0000000087700
0000000002300
0000000023400
0000000000000
0000000000000

Not like this below:

0000000000000
0000000000000
0000000000000
0000000000000
0000000000000
0000000003300
0
0
0000000008400
0000000087700
0000000002300
0000000023400
0
0
0
 
Jai STech ArchCommented:
ok ...since you question was "want to turn the null's into '0' also" - i was giving that solution...
either you can use this...

SELECT  isnull(replicate('0',13 - len(replace(cast([Dollars] as varchar(13)), '.', '')))  +
replace(cast([Dollars] as varchar(13)), '.', ''),0000000000000)
or the one given before by someother expert where you place ISNULL on all checkings...
0
 
WDJ98338Author Commented:
When you are dealing with the mainfram it is not the same.
I will replace the zero with spaces, now I am running in to another problem.

SELECT  isnull(replicate(' ',13 - len(replace(cast([Dollars] as varchar(13)), '.', '')))  + replace(cast([Dollars] as varchar(13)), '.', ''),0)
FROM [dbo].[income]back

when I remove the zero I get this below:

          000
          000
          000
          000
          000
         3300
0
0
         8400
        87700
         2300
        23400
0
0

0
 
appariCommented:
in your case you have to use isnull on all occurances of dollors field.


SELECT replicate(' ',13 - len(replace(cast( isnull([Dollars],0) as varchar(13)), '.', '')))  + replace(cast( isnull([Dollars] ,0) as varchar(13)), '.', '')
FROM [dbo].[income]

0
 
appariCommented:
I think its not fair to award all the points to me,
HuyBD was the first to suggest using isnull function, i just solved your other part of your question,
i think awarding half the points to HuyBD is the proper way. if you wish you can post a question in CS area to take help from mods.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.