Click here to Skip to main content
15,861,168 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
Hello!!!

I am concatenating four columns as one in my sql query. The query is working fine but the problem that i am facing is that for the null values i have replaced them with ' ' (blank space). so if we have a null value the result looks like this

Ankit , ,Delhi ,India


what what i want it to be in case of null is like this

Ankit ,Delhi ,India


the space here is because i have used isnull(columnname,' ')

If i don't use is null then in concatenation takes all the values as null.Here is the query that i am using

select (a.app_name +', '+ isnull(a.gar_name,'')+ ', ' + isnull(a.source_vill,'')+ ', ' + isnull(a.source_place,'') + ', ' + isnull(a.source_Dist,'')) as Address ,  CONVERT(nvarchar,f.Cheque_Draft_dt,103) as sac_dt ,f.amount,  bal= (Select ((select SUM(amount) from viv_fund where financialYear='2010-2011' and type='in' ) - isnull(SUM(amount),0))-f.amount from viv_fund where uniqueid < f.uniqueid and financialYear='2010-2011' and type='out' )  from viv_fund f inner join viv_app a on a.app_Id=f.app_Id where f.financialYear ='2010-2011'  and a.financialYear='2010-2011' and type='out' order by uniqueid desc 


Hope the question is clear...
Posted

try this

SQL
select Replace((a.app_name +', '+ isnull(a.gar_name,'')+ ', ' + isnull(a.source_vill,'')+ ', ' + isnull(a.source_place,'') + ', ' + isnull(a.source_Dist,'')),', ,',',') as Address ,  CONVERT(nvarchar,f.Cheque_Draft_dt,103) as sac_dt ,f.amount,  bal= (Select ((select SUM(amount) from viv_fund where financialYear='2010-2011' and type='in' ) - isnull(SUM(amount),0))-f.amount from viv_fund where uniqueid < f.uniqueid and financialYear='2010-2011' and type='out' )  from viv_fund f inner join viv_app a on a.app_Id=f.app_Id where f.financialYear ='2010-2011'  and a.financialYear='2010-2011' and type='out' order by uniqueid desc 


In the above query i am using replace method. Whenever i find string as , , i am replacing it with ,.
 
Share this answer
 
Comments
ujjwal uniyal 23-Aug-12 2:16am    
Thanks Santhosh for helping again.. It works fine.. :) +5 from me..
try below query:-
SQL
select case when a.app_name IS null then '' else a.app_name END +
	case when a.gar_name IS null then '' else ', '+ a.gar_name END +
	case when a.source_vill IS null then '' else ', '+ a.source_vill END +
	case when a.source_place IS null then '' else ', '+ a.source_place  END +
	case when a.source_Dist IS null then '' else ', '+ a.source_Dist END Address,
 CONVERT(nvarchar,f.Cheque_Draft_dt,103) as sac_dt ,f.amount,
  bal= (Select ((select SUM(amount) from viv_fund where financialYear='2010-2011' and type='in' ) 
- isnull(SUM(amount),0))-f.amount from viv_fund 
where uniqueid < f.uniqueid and financialYear='2010-2011' and type='out' )  
from viv_fund f inner join viv_app a on a.app_Id=f.app_Id where f.financialYear ='2010-2011'  
and a.financialYear='2010-2011' and type='out' order by uniqueid desc 
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900