To case or not case

  • I have a sql statement which needs to be modified. It combines several fields which may or may not be null. I need one space between each value when it exists. I was wondering if using case for this might be the best approach.

    select action,quantity,

    Ltrim((isnull(ACTION,'')+ ' '+ isnull(QUANTITY,'') + ' '+ isnull(UNITOFMEASURE,'') +

    ' ' + isnull(ROUTE,'') + ' ' + isnull(FREQUENCY,'') + ' ' + isnull(SYMPTOM,'') + ' ' + isnull(ADDITIONALSIGNETUR,''))) as sig

    from medicationorder

  • Instead of replacing the NULL values with an empty string, replace with some character string that does not occur in the data, then use REPLACE() to remove that character string plus the extra space following each null replacement

    Using "@@@" as an example:

    select action,quantity,

    Ltrim(

    Replace( (isnull(ACTION,'@@@')+ ' '+ isnull(QUANTITY,'@@@') + ' '+ isnull(UNITOFMEASURE,'@@@') +

    ' ' + isnull(ROUTE,'@@@') + ' ' + isnull(FREQUENCY,'@@@') + ' ' + isnull(SYMPTOM,'@@@') + ' ' +

    isnull(ADDITIONALSIGNETUR,'@@@') + ' ')

    , '@@@ '

    , '' )

    ) as sig

    from medicationorder

  • Duh!!!!!

    Thanks for showing my lack of ability to find the easy solution.

  • Why can't you just use a space as your replacement instead of an emty string?

    [font="Courier New"]DECLARE @A VARCHAR(10),

       @B VARCHAR(10),

       @C VARCHAR(10),

       @D VARCHAR(10),

       @E VARCHAR(10),

       @F VARCHAR(10),

       @Combined VARCHAR(20)

    SET @A = 'A'

    SET @C = 'C'

    SET @D = 'D'

    SET @F = 'F'

    SET @Combined = COALESCE(@A,' ') + ' ' + COALESCE(@B,' ') + ' ' + COALESCE(@C,' ') + ' ' +

           COALESCE(@D,' ') + ' ' + COALESCE(@E,' ') + ' ' + COALESCE(@F,' ')

    SELECT @Combined[/font]

    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]

  • Because the requirement is:

    >>I need one space between each value when it exists.

    If a value is NULL and you replace with a space, you then have 3 spaces between successive non-null values.

  • If the REPLACE method has performance issues, another option may be to put the added space inside the ISNULL:

    Ltrim((isnull(ACTION + ' ','') + isnull(QUANTITY + ' ','') + isnull(UNITOFMEASURE + ' ','') + isnull(ROUTE + ' ','') + isnull(FREQUENCY + ' ','') + isnull(SYMPTOM + ' ','') + isnull(ADDITIONALSIGNETUR,''))) as sig

  • Oops, I was misreading it, I kept thinking he wanted the extra space to show the missing value. In that case:

    [font="Courier New"]

    DECLARE @A VARCHAR(10),

       @B VARCHAR(10),

       @C VARCHAR(10),

       @D VARCHAR(10),

       @E VARCHAR(10),

       @F VARCHAR(10),

       @Combined VARCHAR(20)

    SET @A = 'A'

    SET @C = 'C'

    --SET @D = 'D'

    SET @F = 'F'

    SET @Combined = COALESCE(@A + ' ','') + COALESCE(@B + ' ','') + COALESCE(@C + ' ','') +

           COALESCE(@D + ' ','') + COALESCE(@E + ' ','') + COALESCE(@F + ' ','')

    SELECT @Combined

    [/font]

    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]

  • Yep, both definitely better than my solution, avoiding the Replace() and any performance penalty.

Viewing 8 posts - 1 through 7 (of 7 total)

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