Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2008
    Posts
    24

    Unanswered: How to have nulls from an INT column be "converted" to an empty string on SELECT

    Hi, (NOTE: Title should read "How to sum columns in a single row")

    I have a temp table in a stored proc:

    CREATE TABLE #tempMETRICS
    (
    metric VARCHAR(100),
    domicileA int NULL,
    domicileB int NULL,
    domicileC int NULL
    )

    which I fill with data from various sources.

    At the end of the s.p., I do a simple select:

    SELECT Metric = metric,
    A = domicileA,
    B = domicileB,
    C = domicileC
    FROM #tempMETRICS

    What I would like to do is have the values for columns
    domicileA, domicileB, and domicileC be blank, rather than NULL,
    (during the SELECT) when they are in fact NULL.

    Thanks in Advance,
    chasse

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by chassemerrill
    How to have nulls from an INT column be "converted" to an empty string on SELECT
    I think you just need to decide whether the columns are either int or varchar and whether they allow nulls or not. When you've met those small challenges then I'm sure the solution will present itself.

  3. #3
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    The first problem to understand is that Blank, as you request, is not a numeric datatype and thus you are creating a problem, trying to display Blank in an integer column.

    There are many ways to get what you want. You can get quite elaborate using a CASE statement. Here's a simple method that treats the column as character, so that Blank is an acceptable value. Read up on the functions used, there are options.
    Code:
    SELECT  Metric = metric,
            A = ISNULL( STR(domicileA), CHAR(0) ),
            B = ISNULL( STR(domicileB), CHAR(0) ),
            C = ISNULL( STR(domicileC), CHAR(0) )
        FROM #tempMETRICS
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  4. #4
    Join Date
    Aug 2008
    Posts
    24
    Derek,

    Very nice!!! Thanks. Will add that to my "toolset".

    Chasse

Posting Permissions

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