asked on

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]

HuyBD

try to use ISNULL(orig_valuge,0) function

HuyBD

WDJ98338

ASKER

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]

ASKER CERTIFIED SOLUTION

appari

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jai S

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

FROM [dbo].[income]

Jai S

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]

appari

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

Jai S

0 is always 0...i think the formatting is wrong then..its upto the author on WHY "0" has to be formatted to "000000000000"

WDJ98338

ASKER

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

Jai S

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...

WDJ98338

ASKER

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

appari

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]

appari

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.