?
Solved

Need help with a query

Posted on 2017-04-14
14
Medium Priority
?
71 Views
Last Modified: 2017-04-14
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.
0
Comment
Question by:RadhaKrishnaKiJaya
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 3
  • +1
14 Comments
 
LVL 22

Expert Comment

by:JesterToo
ID: 42093502
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
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 42093507
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
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 42093510
I want it in a SQL query.
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

Expert Comment

by:Rick Deschenes
ID: 42093514
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 42093521
I want it in a SQL query.

See above :)
0
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 42093523
Thanks for all the reply.

Patrick,

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

Like this.

,    , IL
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 42093533
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
 

Expert Comment

by:Rick Deschenes
ID: 42093539
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
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 42093550
Patrick,

There are spaces too.

Rick,

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

Thanks for trying to help me.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 42093557
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
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 42093563
yes.
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 42093583
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
 

Expert Comment

by:Rick Deschenes
ID: 42093606
Replace(Replace(address + ' ,' + city + ' ,' + state, ' ,', '') + '-' + zip, '-','')
replace any "space comma"
replace empty zip "hyphen"
0
 

Author Closing Comment

by:RadhaKrishnaKiJaya
ID: 42093617
Thanks.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question