Problem with CASE statement where result_expression is a space

[francisT]

In a SELECT statement, I need to check whether a particular
column contains a NULL and, if so, put four space
characters in the output. I cannot just use ISNULL because I need to check for (and replace) other values in a CASE statement.

The CASE statement replace my sting of spaces with the mpty string.

Here is a simplified version of my code:


SELECT CASE WHEN c IS NULL THEN ' '
WHEN c IS NOT NULL THEN 'X' END AS c2
INTO X
FROM T

When I execute this qeury:

SELECT c2, LEN(c2) FROM X

LEN(c2) evaluates to zero.

If I put any character other than space in my result_expression, I get what I expected. Even
other white-space characters, such a TAB, work OK.

Incidentally, I have tried all sorts of tricks, like casting to CHAR(1), but I have found nothing that works.
Regardless of how many space characters are in the result_expression, they are all thrown away.

I would be very interested to discover why strings of space characters are treated in this way.

I would also be most grateful if anyone can suggest a workaround

Thanks in anticipation!

Francis

 

[vongrunt]

LEN() excludes trailing blanks.

Unless you use nchar/nvarchar, DATALENGTH() should return expected value.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)