locked
Joining fields together RRS feed

  • Question

  • I've written the following script to join the various address fields together

    SELECT RTRIM(a.AddressLine1)+ ', ' + RTRIM(a.AddressLine2)+ ', ' + RTRIM(a.AddressLine3)+ ', ' + RTRIM(a.AddressLine4) + ', ' + RTRIM(a.AddressLine5)
    FROM MyTable

    but for those rows that don't have address in the fields, there is a ",,,,"

    how do i get rid of this?

    plus if addressline4 is blank, i still get  the following

    "21 Pop Lane Road, Pope, , , "

    i would like "21 Pop Lane Road, Pope"

    Thursday, January 21, 2010 12:52 PM

Answers

  • Here is one way

    SELECT RTRIM(a.AddressLine1) + 
    CASE 
    	WHEN a.AddressLine2 IS NULL OR a.AddressLine2 = '' THEN ''
    	ELSE ', ' + RTRIM(a.AddressLine2) END + 
    CASE 
    	WHEN a.AddressLine3 IS NULL OR a.AddressLine3 = '' THEN ''
    	ELSE ', ' + RTRIM(a.AddressLine3) END + 
    CASE 
    	WHEN a.AddressLine4 IS NULL OR a.AddressLine4 = '' THEN ''
    	ELSE ', ' + RTRIM(a.AddressLine4) END +
    CASE
    	WHEN a.AddressLine5 IS NULL OR a.AddressLine5 = '' THEN ''
    	ELSE + ', ' + RTRIM(a.AddressLine5) END
    FROM MyTable


    Abdallah El-Chal, PMP, ITIL, MCTS
    • Marked as answer by Sam233 Thursday, January 21, 2010 1:34 PM
    Thursday, January 21, 2010 1:30 PM

All replies

  • Hi Sam use this
    Query if its help then plz  mark in left side Green Traiangle


    CREATE

    TABLE TestString(ID INT IDENTITY(1,1),

    AddressLine1

    VARCHAR(100),

    AddressLine2

    VARCHAR(100),

    AddressLine3

    VARCHAR(100),

    AddressLine4

    VARCHAR(100),

    AddressLine5

    VARCHAR(100)

    )

    --TRUNCATE TABLE testString

    INSERT

    INTO TestString

    SELECT

    'A1','A2','A3','A4','A5' UNION ALL

    SELECT

    'A1','A2','','A4','A5'UNION ALL

    SELECT

    'A1','','A3','A4','A5'UNION ALL

    SELECT

    'A1','A2','A3','','A5'UNION ALL

    SELECT

    'A1','A2','A3','A4',''UNION ALL

    SELECT

    'B1','B2','B3','B4','B5'

     

    SELECT

    * FROM TestString

    CREATE

    FUNCTION GetAddress

    (

    @ID INT)

    Returns

    Varchar(1000)

    AS

    BEGIN

     

    Declare @Str Varchar(1000)

     

    SET @STR=''

     

    SELECT @Str= Case When RTRIM(LTRIM(AddressLine1))!='' THEN @Str +Convert(Varchar,AddressLine1)+',' ELSE @Str END FROM TestString WHERE ID = @ID

     

    SELECT @Str= Case When RTRIM(LTRIM(AddressLine2))!='' THEN @Str +Convert(Varchar,AddressLine2)+',' ELSE @Str END FROM TestString WHERE ID = @ID

     

    SELECT @Str= Case When RTRIM(LTRIM(AddressLine3))!='' THEN @Str +Convert(Varchar,AddressLine3)+',' ELSE @Str END FROM TestString WHERE ID = @ID

     

    SELECT @Str= Case When RTRIM(LTRIM(AddressLine4))!='' THEN @Str +Convert(Varchar,AddressLine4)+',' ELSE @Str END FROM TestString WHERE ID = @ID

     

    SELECT @Str= Case When RTRIM(LTRIM(AddressLine5))!='' THEN @Str +Convert(Varchar,AddressLine5)+',' ELSE @Str END FROM TestString WHERE ID = @ID

     

    RETURN SUBSTRINg(@Str,1,LEN(@str)-1)

    END

     

    SELECT

    dbo.GETADDRESS(ID) FROM TestString

    -- OUTPUT
    A1,A2,A3,A4,A5
    A1,A2,A4,A5
    A1,A3,A4,A5
    A1,A2,A3,A5
    A1,A2,A3,A4
    B1,B2,B3,B4,B5

    Manigandan-DBA , Mark as Answer if it helps!

    Thursday, January 21, 2010 1:26 PM
  • Here is one way

    SELECT RTRIM(a.AddressLine1) + 
    CASE 
    	WHEN a.AddressLine2 IS NULL OR a.AddressLine2 = '' THEN ''
    	ELSE ', ' + RTRIM(a.AddressLine2) END + 
    CASE 
    	WHEN a.AddressLine3 IS NULL OR a.AddressLine3 = '' THEN ''
    	ELSE ', ' + RTRIM(a.AddressLine3) END + 
    CASE 
    	WHEN a.AddressLine4 IS NULL OR a.AddressLine4 = '' THEN ''
    	ELSE ', ' + RTRIM(a.AddressLine4) END +
    CASE
    	WHEN a.AddressLine5 IS NULL OR a.AddressLine5 = '' THEN ''
    	ELSE + ', ' + RTRIM(a.AddressLine5) END
    FROM MyTable


    Abdallah El-Chal, PMP, ITIL, MCTS
    • Marked as answer by Sam233 Thursday, January 21, 2010 1:34 PM
    Thursday, January 21, 2010 1:30 PM
  • this should work,

    but seems very long winded, is there a quick case statement for this problem
    Thursday, January 21, 2010 1:32 PM
  • thanks ABDSHALL :-)
    Thursday, January 21, 2010 1:34 PM
  • --Another Logic


    SELECT
    @Str= Case When RTRIM(AddressLine4)='' THEN @Str +Convert(Varchar,AddressLine1)+',' +Convert(Varchar,AddressLine2)+',' +Convert(Varchar,AddressLine3)

    ELSE
    @Str +Convert(Varchar,AddressLine1)+',' +Convert(Varchar,AddressLine2)+',' +Convert(Varchar,AddressLine3)+',' +(Varchar,AddressLine4)


    SELECT @Str= Case When RTRIM(AddressLine3)='' THEN @Str +Convert(Varchar,AddressLine1)+','
    +Convert(Varchar,AddressLine2)

    ELSE
    @Str

    SELECT @Str= Case When RTRIM(AddressLine2)='' THEN @Str +Convert(Varchar,AddressLine1
    )

    ELSE
    @Str

    SELECT @Str= Case When RTRIM((AddressLine1))='' THEN
    @Str=' '
    ELSE
    @Str

    Thursday, January 21, 2010 1:39 PM