November 20, 2008 at 12:21 pm
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
November 20, 2008 at 1:02 pm
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
November 20, 2008 at 1:12 pm
Duh!!!!!
Thanks for showing my lack of ability to find the easy solution.
November 20, 2008 at 1:53 pm
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]
November 20, 2008 at 1:56 pm
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.
November 20, 2008 at 2:04 pm
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
November 20, 2008 at 2:05 pm
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]
November 20, 2008 at 2:09 pm
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