Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    6

    Unanswered: Sorting out blanks and nulls

    Hi,

    I'm trying to create a view from a table in which I want to concatenate to columns to a new column. The concatenation part works fine when data "supports" calculations, i.e. no nulls. The data however is "infected" in two ways I have Null-values and I have blanks alongside with some real information. I concatenate via a CASE statement but I can't figure out how to take the 3 situations into account (nulls, blanks and data) and create one meaningfull column in my view.

    The result should be
    column1 + "-" if column2 is null or blank
    column1 + "-" + column2 if column2 has meaningfull data

    Both the involved columns are text (varchar).

    If anyone has been in this situation before could you please provide som code that handles the situation.

    Regards
    Kim Hansen

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Use one of these functions:
    nullif('-'+[YourField]), '-')
    coalesce('-'+[YourField], '-')
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Code:
    DECLARE @Col1 varchar(10), @Col2 varchar(10)
    
    SELECT @Col1 = 'Brett'
    SELECT COALESCE(@Col1,'') + ' - ' + COALESCE(@Col2,'')
    
    SELECT @Col1 = null, @Col2 = 'Kaiser'
    SELECT COALESCE(@Col1,'') + ' - ' + COALESCE(@Col2,'')
    
    SELECT @Col1 = 'Brett', @Col2 = 'Kaiser'
    SELECT COALESCE(@Col1,'') + ' - ' + COALESCE(@Col2,'')
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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