Post reply

Add text to query results

  • August 31, 2009 at 9:00 am

    #135707

    Maybe I'm just sleepy on a Monday morning, but I'm drawing a blank on this one. If I write a query that says SELECT * FROM table., lets say I have a field named Section. Is there a way that if Section is not null, my query results for the Section field, will add the word Section in front of the number that is in that field. For example, in the Section field, I might have 1, 2, 3, 4, etc....; however, in my results I want it to just display as Section 1, Section 2, Section 3, Section 4. Am I making any sense?

    Thanks,

    Jordon

  • August 31, 2009 at 9:13 am

    #1046909

    Nevermind, I woke up and got it. For anybody looking to do this, you can write it like this:

    Select 'Section ' + Section FROM Table

  • August 31, 2009 at 9:33 am

    #1046928

    Nice going, Jordan! 😀

    Have another cup of coffee and carry on. It's Monday and we all need all the help we can get.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • August 31, 2009 at 10:24 am

    #1046960

    Ok, I didn't get a lot of sleep last night, so I'm going blank this morning. Here is my code:

    SELECT

    IMSV7.TBL308.DESCRIPT, IMSV7.PARCEL.SECTION, IMSV7.PARCEL.BLOCK,

    IMSV7.TBL308.DESCRIPT + ' SUB ' + ISNULL(IMSV7.PARCEL.SECTION, '') + ' REV ' + ISNULL(IMSV7.PARCEL.BLOCK, '') AS FULLSUBDIVISION

    FROM IMSV7.PARCEL LEFT OUTER JOIN

    IMSV7.TBL308 ON IMSV7.PARCEL.SUBDIVCODE = IMSV7.TBL308.CODE

    This is working perfectly, where it add the three fields together and adds the word SUB in front of the Section, as well as adds the word REV in front of the block field. The issue is, when Sub or Rev is null, it is still putting the word SUB and the word REV in the results, where I want it to say if its null, don't display the word SUB or REV at all. I've tried ISNOTNULL, but that don't work. Is there a built in function that would do what I'm wanting to do?

    Thanks,

    Jordon

  • August 31, 2009 at 10:27 am

    #1046965

    You're very close, just switch it around slightly to only add the sections when it's not null, like the following:

    SELECT

    IMSV7.TBL308.DESCRIPT, IMSV7.PARCEL.SECTION, IMSV7.PARCEL.BLOCK,

    IMSV7.TBL308.DESCRIPT + ISNULL(' SUB ' + IMSV7.PARCEL.SECTION, '') + ISNULL(' REV ' + IMSV7.PARCEL.BLOCK, '') AS FULLSUBDIVISION

    FROM IMSV7.PARCEL LEFT OUTER JOIN

    IMSV7.TBL308 ON IMSV7.PARCEL.SUBDIVCODE = IMSV7.TBL308.CODE

    The reason this works, is that NULL + anything = NULL, so adding the code during the evaluation still leaves it null and just supplies a ' ' coming out of the ISNULL.

    Seth Phelabaum

    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • August 31, 2009 at 10:30 am

    #1046970

    COFFEE, I NEED COFFEE!!!!

    Thank you very much, you're awesome!!!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply