how to avoid null or ' ' value in a select query and replace that value as 0 in a select query not in the table?

• Sivaganesh Tamilvendhan

  Hall of Fame

  Points: 3498

  More actions

  August 1, 2012 at 6:34 am

  #265062

  here i am having table

  declare @test1 table(

  id int,

  val varchar(20),

  ser int,

  sa int

  )

  insert into @test1

  select 1,'rer',1,Null union all

  select 2,'rer1',Null,Null union all

  select 3,'',Null,Null

  select * from @test1

  --select ID,val,ISNULL(ser,0) AS ser,ISNULL(sa,0) AS sa from @test1

  select ID,ISNULL(val,0) AS val,ISNULL(ser,0) AS ser,ISNULL(sa,0) AS sa from @test1

  to avoid null i just used

  select ID,ISNULL(val,0) AS val,ISNULL(ser,0) AS ser,ISNULL(sa,0) AS sa from @test1

  but when coulmn is empty means the value was not changing to zero

  the out put of the select query

  IDvalsersa

  1rer10

  2rer100

  300

  this there any way to show th empty column value as 0

• BrainDonor

  SSCoach

  Points: 19262

  More actions

  August 1, 2012 at 6:48 am

  #1520220

  select ID,

  case val

  when null then '0'

  when '' then '0'

  else val

  end as val,

  ISNULL(ser,0) AS ser,ISNULL(sa,0) AS sa from @test1

  Use CASE to specifiy various conditions.

  Note that it has to use '0' (with quotes) instead of 0, because the column 'val' is varchar (try removing the quotes and see what it does). There are other ways to accomodate this, but that was the easiest.

  Steve Hall
  Linkedin
  Blog Site

• Gazareth

  One Orange Chip

  Points: 27737

  More actions

  August 1, 2012 at 6:48 am

  #1520221

  Something like this: ISNULL(NULLIF(RTRIM(val),''),0)

• calvo

  SSChampion

  Points: 12930

  More actions

  August 1, 2012 at 6:51 am

  #1520222

  First way I think of is using a combination of NULLIF and ISNULL

  SELECT

  ID,

  ISNULL(NULLIF(val,''),0) AS val,

  ISNULL(NULLIF(ser,''),0) AS ser,

  ISNULL(NULLIF(sa,''),0) AS sa

  FROM @test1

  ______________________________________________________________________________________________
  Forum posting etiquette.[/url] Get your answers faster.

• Sivaganesh Tamilvendhan

  Hall of Fame

  Points: 3498

  More actions

  August 1, 2012 at 6:59 am

  #1520231

  thanks

  BrainDonor,

  Gazareth

  it was working fine

• DBA328

  Hall of Fame

  Points: 3952

  More actions

  August 1, 2012 at 7:00 am

  #1520233

  how about this?

  declare @test1 table(

  id int,

  val varchar(20),

  ser int,

  sa int

  )

  insert into @test1

  select 1,'rer',1,Null union all

  select 2,'rer1',Null,Null union all

  select 3,'',Null,Null

  select * from @test1

  --select ID,val,ISNULL(ser,0) AS ser,ISNULL(sa,0) AS sa from @test1

  select ID,case when val ='' then '0' else ISNULL(val,0) end as val,ISNULL(ser,0) AS ser,ISNULL(sa,0) AS sa from @test1