locked
Concatenating 8 address columns RRS feed

  • Question

  • Hello All, I have 8 address columns shown below; what is the best way to concatenate these columns so that I have only 2 columns with the address references (1st 5 columns as 1 column for the Address, and City, State, Zip in one column). Thanks for any assistance given.

          ,[APARTMENT_NUMBER]

          ,[STREET_NUMBER]

          ,[STREET_NAME]

          ,[ADDRESS_DIRECTION_DESC]

          ,[ADDRESS_TYPE_DESC]

          ,[CITY_DESC]

          ,[STATE_CODE]

          ,[ZIP_CODE]

         

    Wednesday, February 25, 2015 2:48 PM

Answers

  • If its SQL 2012 and above safest option is to use CONCAT which will do NULL handling automatically behind the scenes

    SELECT CONCAT([APARTMENT_NUMBER] + ' ' ,[STREET_NUMBER] + ' ', [STREET_NAME] + ' ' ,[ADDRESS_DIRECTION_DESC] + ' ',[ADDRESS_TYPE_DESC]) AS ' Address'
    ,CONCAT([CITY_DESC] + ' ' ,[STATE_CODE] + ' ',[ZIP_CODE]) AS 'Address Details'


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by t'lee Wednesday, February 25, 2015 3:46 PM
    Wednesday, February 25, 2015 3:20 PM

All replies

  • SELECT ISNULL([APARTMENT_NUMBER], '') + ' ' + ISNULL([STREET_NUMBER], '') + ' ' + ISNULL([STREET_NAME], '') + ' ' + ISNULL([ADDRESS_DIRECTION_DESC], '') + ' ' + ISNULL([ADDRESS_TYPE_DESC], '') AS ' Address'
    ,ISNULL([CITY_DESC], '') + ' ' + ISNULL([STATE_CODE], '') + ' ' + ISNULL([ZIP_CODE], '') AS 'Address Details'

    Wednesday, February 25, 2015 3:09 PM
  • If its SQL 2012 and above safest option is to use CONCAT which will do NULL handling automatically behind the scenes

    SELECT CONCAT([APARTMENT_NUMBER] + ' ' ,[STREET_NUMBER] + ' ', [STREET_NAME] + ' ' ,[ADDRESS_DIRECTION_DESC] + ' ',[ADDRESS_TYPE_DESC]) AS ' Address'
    ,CONCAT([CITY_DESC] + ' ' ,[STATE_CODE] + ' ',[ZIP_CODE]) AS 'Address Details'


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by t'lee Wednesday, February 25, 2015 3:46 PM
    Wednesday, February 25, 2015 3:20 PM
  • Thank you very much for your kind assistance, I really appreciate it.  How can I use this in a stored procedure?  Would I write it the same?

    Thank you very much!

    Wednesday, February 25, 2015 3:37 PM
  • Thank you! Thank you! yes, this is what I'm looking for.

    Thank you for kind assistance and time!

    Wednesday, February 25, 2015 3:47 PM