+ Reply to Thread
Results 1 to 2 of 2

Thread: COALESCE Column

  1. #1

    COALESCE Column

    I have a Data like (Address1, Address2, City, Zip, Country) , i`d like to create a FullAddress as a container for those , but some fields may have incomplete information .
    I have been using this query :
    Code:
    SELECT (((((COALESCE([Address1],'')
        + COALESCE(', '+[Address2],''))
        + COALESCE(', '+[City],''))
        + COALESCE(', '+[State],''))
        + COALESCE(', '+[Country],'')) AS FullAddress
    FROM Locations
    But i had a problem as if Address1 is NULL , the FullAddress will have ', ' as the first two characters . but then i used the Below code :
    Code:
    SELECT CASE WHEN [Address1] IS NOT NULL THEN [Address1] ELSE '' END
            + CASE WHEN [Address2] IS NOT NULL THEN ', ' + [Address2] ELSE '' END
            + CASE WHEN [City] IS NOT NULL THEN ', ' + [City] ELSE '' END
            + CASE WHEN [Zip] IS NOT NULL THEN ', ' + [Zip] ELSE '' END
            + CASE WHEN [Country] IS NOT NULL THEN ', ' + [Country] ELSE '' END
            AS [FullAddress]
    FROM Locations
    and now i am at a Dead end , any suggestions ?

  2. #2
    Junior Member
    Join Date
    Jan 2012
    Posts
    41
    You can use that query , i am pretty sure it will fix your issue :
    Code:
    SELECT
        ISNULL(Address1 + ', ', '')
        + ISNULL(Address2 + ', ', '')
        + ISNULL(City + ', ', '')
        + ISNULL(State + ', ', '')
        + ISNULL(Country + ', ', '')
        AS FullAddress
    The result of concation NULL + ', ' is NULL => Address1 + ', ' will be NULL or valid address => ISNULL(Address1 + ', ', '') will be empty string or valid address.

+ Reply to Thread

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts