[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Null

Posted on 2007-10-02
12
Medium Priority
?
258 Views
Last Modified: 2012-05-05
--==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]


0
Comment
Question by:WDJ98338
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 17

Expert Comment

by:HuyBD
ID: 20003837
try to use ISNULL(orig_valuge,0) function

HuyBD
0
 

Author Comment

by:WDJ98338
ID: 20003856
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
 
LVL 39

Accepted Solution

by:
appari earned 2000 total points
ID: 20003898
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 14

Expert Comment

by:Jai S
ID: 20003901
SELECT  isnull(replicate('0',13 - len(replace(cast([Dollars] as varchar(13)), '.', '')))  +
replace(cast([Dollars] as varchar(13)), '.', ''),0)

FROM [dbo].[income]
0
 
LVL 14

Expert Comment

by:Jai S
ID: 20003904
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
 
LVL 39

Expert Comment

by:appari
ID: 20003912
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
 
LVL 14

Expert Comment

by:Jai S
ID: 20003916
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
 

Author Comment

by:WDJ98338
ID: 20003930
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
 
LVL 14

Expert Comment

by:Jai S
ID: 20003933
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
 

Author Comment

by:WDJ98338
ID: 20003951
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
 
LVL 39

Expert Comment

by:appari
ID: 20003969
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
 
LVL 39

Expert Comment

by:appari
ID: 20003989
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question