COALESCE and CONCAT Address Line 1 and Address Line 2 separated by a comma

Bobby P 221

We are trying to COALESCE and CONCAT Address Line 1 and Address Line 2 separated by a comma "," from potentially two data sources.

So like...123 Main St., Apt. 101

The first part is easy enough...

CONCAT (

COALESCE (

LTRIM (RTRIM ([Temp_Table_State_Funded_NonMedicaid_EDW_Member_Subset].[EDW_Address_AddressLine1])),

LTRIM (RTRIM ([#Temp_Table_State_Funded_NonMedicaid_EDW_Eligibility_Span].[EDW_MemberEligibility_CardAddress1])),

So how do we do the second part and COALESE and CONCAT based on the Address Line 2 being valued and preceded by a comma "," if Address Line 2 is from two different sources?

Erland Sommarskog 102.2K Reputation points

2024-05-09T19:19:58.5033333+00:00
For this type of question you do yourself a big service if you post CREATE TABLE statement for your table(s) and INSERT statement with sample data, and the expected result given the example.

I don't what those "different sources" might be, so it is difficult to write a query.

But one tip is that you may be interested in concat_ws. Concat_ws is like concat but the first parameter is a separator (ws = with separator) to combine the values. However, if you have a NULL value in the list, you don't get any extra separator. For instance

SELECT concat_ws(',', 1, 2, NULL, 3)
CosmogHong-MSFT 23,556 Reputation points Microsoft Vendor

2024-05-10T01:28:42.8933333+00:00

Hi @Bobby P

if Address Line 2 is from two different sources?

Do you mean from two different tables?

Share via

COALESCE and CONCAT Address Line 1 and Address Line 2 separated by a comma