Replace '' with a 0 ......... replace nothing with a 0

Hi guys
Im sure this is easy but I have a query to calculate spaces on a
booking system and need to replace a blank ie no bookings yet with a 0
so that i can add the numbers up to get a total.
Trouble is its not a NULL value and when i try
REPLACE([fieldname],'','0') it just doesnt work.
I can get round it but rather do it properly
MattieHi
When you say "it does not work" , what does that mean? Is an error message,
is it not given you a right result ?
SELECT REPLACE(column ,' ' ,'0')
"MattieG" <mattiegriffin@.hotmail.co.uk> wrote in message
news:1156689093.555748.280040@.75g2000cwc.googlegroups.com...
> Hi guys
> Im sure this is easy but I have a query to calculate spaces on a
> booking system and need to replace a blank ie no bookings yet with a 0
> so that i can add the numbers up to get a total.
> Trouble is its not a NULL value and when i try
> REPLACE([fieldname],'','0') it just doesnt work.
> I can get round it but rather do it properly
> Mattie
>|||Hi
Basically the query still have blanks in that particular field, no
error message.
SELECT REPLACE(column ,' ' ,'0') ->only works if there is actually a
space in the field.
Im thinkin i might have to use ISNUM or somet like that.
Uri Dimant wrote:[vbcol=seagreen]
> Hi
> When you say "it does not work" , what does that mean? Is an error messag
e,
> is it not given you a right result ?
> SELECT REPLACE(column ,' ' ,'0')
>
> "MattieG" <mattiegriffin@.hotmail.co.uk> wrote in message
> news:1156689093.555748.280040@.75g2000cwc.googlegroups.com...|||> SELECT REPLACE(column ,' ' ,'0') ->only works if there is actually a
> space in the field.
One method to use COALESCE to return ' ' or '0' instead of NULL:
SELECT REPLACE(COALESCE(column, '0'), ' ', '0')

It seems to me that this is a flaw in your table design. The column should
be defined with the appropriate data type so that spaces can't be inserted
into a numeric column in the first place. You can still use SELECT
COALESCE(column, 0) to treat NULLs as 0 or treat NULLs as zero in your
application code.
Hope this helps.
Dan Guzman
SQL Server MVP
"MattieG" <mattiegriffin@.hotmail.co.uk> wrote in message
news:1156694244.396948.101820@.p79g2000cwp.googlegroups.com...[vbcol=seagreen]
> Hi
> Basically the query still have blanks in that particular field, no
> error message.
> SELECT REPLACE(column ,' ' ,'0') ->only works if there is actually a
> space in the field.
> Im thinkin i might have to use ISNUM or somet like that.
>
> Uri Dimant wrote:
>|||Hi Dan, thanks for that will look at it now.
To describe in more detail, i have a qurey to calculate sales sold in a
particular session and a maximum for that session. If there are no
sales then "maximum - nothing = nothing as apposed to 0 which is what i
need.
Ill go have a play about now and let you know how it goes.
Mattie
Dan Guzman wrote:[vbcol=seagreen]
> One method to use COALESCE to return ' ' or '0' instead of NULL:
> SELECT REPLACE(COALESCE(column, '0'), ' ', '0')
>
> It seems to me that this is a flaw in your table design. The column shoul
d
> be defined with the appropriate data type so that spaces can't be inserted
> into a numeric column in the first place. You can still use SELECT
> COALESCE(column, 0) to treat NULLs as 0 or treat NULLs as zero in your
> application code.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "MattieG" <mattiegriffin@.hotmail.co.uk> wrote in message
> news:1156694244.396948.101820@.p79g2000cwp.googlegroups.com...|||On 27 Aug 2006 07:31:33 -0700, MattieG wrote:

>Hi guys
>Im sure this is easy but I have a query to calculate spaces on a
>booking system and need to replace a blank ie no bookings yet with a 0
>so that i can add the numbers up to get a total.
>Trouble is its not a NULL value and when i try
>REPLACE([fieldname],'','0') it just doesnt work.
>I can get round it but rather do it properly
Hi Mattie,
Two ways:
SELECT CASE WHEN fieldname = '' THEN '0' ELSE fieldname END
FROM ...
SELECT COALESCE(NULLIF(fieldname, ''), '0')
FROM ...
Hugo Kornelis, SQL Server MVP|||On 27 Aug 2006 15:12:24 -0700, MattieG wrote:

>Hi Dan, thanks for that will look at it now.
>To describe in more detail, i have a qurey to calculate sales sold in a
>particular session and a maximum for that session. If there are no
>sales then "maximum - nothing = nothing as apposed to 0 which is what i
>need.
Hi Mattie,
I don't understand. How can maximum - nothing be equal to nothing
instead of maximum?
But more important: how can the result of a substraction ever be an
empty string? Can yoou please post some repro code? (I.e. table
definitions as CREATE TABLE statements, sample data as INSERT statements
and the statement that produces the incorrect output)
Hugo Kornelis, SQL Server MVP|||I'm guessing here, but...
The problem isn't that maximum minus nothing = nothing.
The problem is that (implicit conversion of conforming string) minus (empty
string) isn't math.
You can fight this forever, or you can alter your table structure so that
you have a nullable integer column. Then you can use select
max(column)-ISNULL(min(column),0).
good luck!
"MattieG" wrote:

> Hi Dan, thanks for that will look at it now.
> To describe in more detail, i have a qurey to calculate sales sold in a
> particular session and a maximum for that session. If there are no
> sales then "maximum - nothing = nothing as apposed to 0 which is what i
> need.
> Ill go have a play about now and let you know how it goes.
> Mattie
>
> Dan Guzman wrote:
>