To case or not case

Question

To case or not case

timscronin

SSCertifiable

Points: 6851
More actions
November 20, 2008 at 12:21 pm

#201645

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

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

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

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

timscronin SSCertifiable Points: 6851 More actions · Answer 2

Duh!!!!!

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

Garadin One Orange Chip Points: 29613 More actions · Answer 3

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]

PW-201837 SSC-Insane Points: 20805 More actions · Answer 4

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.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 5

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

Garadin One Orange Chip Points: 29613 More actions · Answer 6

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]

PW-201837 SSC-Insane Points: 20805 More actions · Answer 7

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