Need help with a query

Hi Experts,

I want to concat Address, city ,state and  zip to one string. Some times address is null, sometimes city, state and zip are null.

I want in this format

Address, City, State-Zip

if Address is missing then I want

If City, State and Zip are missing then I want

Address

Right now I am getting it. But do not know how to get rid of the  ','  after address when there is no city state and zip.
Thanks in advance.
RadhaKrishnaKiJayaAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
This is working for me :)

DECLARE @address varchar(100) = '100 Main St',
	@city varchar(100) = 'Townsville',
	@state varchar(2) = 'CT',
	@zip varchar(10) = '01234'

SELECT @address + COALESCE(', ' + @city + ', ' + @state + ' ' + @zip, '')

SELECT @address = '100 Main St',
	@city = null,
	@state = null,
	@zip = null

SELECT @address + COALESCE(', ' + @city + ', ' + @state + ' ' + @zip, '')

Open in new window

0
 
JesterTooCommented:
There are several ways this "query" might be constructed... you need to show us the code you have in order to propose a good solution.
0
 
RadhaKrishnaKiJayaAuthor Commented:
I want it in a SQL query.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Rick DeschenesSoftware DevelopmentCommented:
with out writing it for you a set of subqueries, one with null address one with out is elegant enough
optionally, a nested CASE would also handle this
not sure of your presentation but a replace(" ,", "" might work as well.
0
 
Patrick MatthewsCommented:
I want it in a SQL query.

See above :)
0
 
RadhaKrishnaKiJayaAuthor Commented:
Thanks for all the reply.

Patrick,

Still I am getting the , when some data is not there.

Like this.

,    , IL
0
 
Patrick MatthewsCommented:
Two things:
1) Are you sure the values are truly null, and not just zero-length strings (which are not the same)
2) What is your CONCAT_NULL_YIELDS_NULL setting? If this is OFF, then concatenating nulls treats them as zero-length strings

In my testing, as long as any of city, state, or zip are null, they all get dropped.
0
 
Rick DeschenesSoftware DevelopmentCommented:
first the Coalesce TSQL will not require the IFF, put in one Coalesce for each item
table tempUser
address      city      state      zip
address1      city1      state1      zip1
address2            state2      zip2
address3                  
address4      city4            zip4
SQL
SELECT [address]+IIF(IsNull([city]), '', ', '+[city])+IIF(IsNull([state]), '', ', '+[state])+IIF(IsNull([zip]), '', ', '+[zip]) as 'Full Address'
FROM tempUser;

Open in new window

Results
'Full Address'
address1, city1, state1, zip1
address2, state2, zip2
address3
address4, city4, zip4
0
 
RadhaKrishnaKiJayaAuthor Commented:
Patrick,

There are spaces too.

Rick,

I am getting error 'The isnull function requires 2 argument(s).'

Thanks for trying to help me.
0
 
Patrick MatthewsCommented:
Patrick,

There are spaces too.

So you're saying that instead of being nulls, these city, state, and/or zip values may simply be spaces?
0
 
RadhaKrishnaKiJayaAuthor Commented:
yes.
0
 
Patrick MatthewsCommented:
It would have been nice for you to have said that up front. In your original question, all you said was that they would have values, or they would be null.
0
 
Rick DeschenesSoftware DevelopmentCommented:
Replace(Replace(address + ' ,' + city + ' ,' + state, ' ,', '') + '-' + zip, '-','')
replace any "space comma"
replace empty zip "hyphen"
0
 
RadhaKrishnaKiJayaAuthor Commented:
Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.