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

Question

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?

Answer

Hi @Bobby P

To achieve this, you can use COALESCE and CONCAT in combination with a conditional CASE or IIF statement to determine whether Address Line 2 exists and then prepend the comma if needed.

This approach ensures that the second part (Address Line 2) is only included if it has a value, and it properly formats the comma when necessary.

Query:

CONCAT(
    -- First, handle Address Line 1
    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])),
        ''
    ),

    -- Then, handle Address Line 2 with a conditional comma
    CASE
        WHEN COALESCE(
            LTRIM(RTRIM([Temp_Table_State_Funded_NonMedicaid_EDW_Member_Subset].[EDW_Address_AddressLine2])),
            LTRIM(RTRIM([#Temp_Table_State_Funded_NonMedicaid_EDW_Eligibility_Span].[EDW_MemberEligibility_CardAddress2]))
        ) <> '' THEN
            CONCAT(
                ', ',
                COALESCE(
                    LTRIM(RTRIM([Temp_Table_State_Funded_NonMedicaid_EDW_Member_Subset].[EDW_Address_AddressLine2])),
                    LTRIM(RTRIM([#Temp_Table_State_Funded_NonMedicaid_EDW_Eligibility_Span].[EDW_MemberEligibility_CardAddress2]))
                )
            )
        ELSE
            '' -- If no Address Line 2, don't add anything     END )

Share via

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

1 answer

Your answer