Click here to Skip to main content
12,399,975 members (48,495 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL-Server
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 22-Aug-12 20:00pm
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

try this

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 ,.
  Permalink  
Comments
ujjwal uniyal 23-Aug-12 2:16am
   
Thanks Santhosh for helping again.. It works fine.. :) +5 from me..
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

try below query:-
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 
  Permalink  
v2

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160721.1 | Last Updated 23 Aug 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100