Unanswered: How do I exclude carriage returns as part of a IS NULL exclusion clause...
I have stupid users... who doesn't?! They have entered carriage returns as a whole value in some fields, that is, the field contains nothing more than a carriage return.
I really need to treat these cases as nulls and have successfully removed whole fields of nothing but spaces by using the LTRIM(RTRIM()) construct. Unfortunately, this doesn't deal with carraige returns. Even CASTing and CONVERTing to varchar and then using LTRIM(RTRIM()) doesn't work.
Does anyone know how I can elegantly get around this problem other than my best guess below:
Best guess pseudo code:
IF count of field is greater than 1 THEN probably a full sentence so ignore ELSE SUBSTRING first character and if CHAR(10, etc) then treat as NULL.
Here's some code that reconstructs the problem:
select datalength(char(13)) CarriageReturnVisible
, datalength(ltrim(rtrim(cast(char(13) as varchar)))) [This Don't Work]
i think that an enter is not just an carriage return but is also a line feed
so isnt is char(10) or Char(13) at the same time
i seem to remember something in 3g programming that looks like vbCRLF etc...
You are quite correct, systems that are derived from MS-DOS see 0x0d0a as a line end. The code that I proposed dodges that bullet altogether though, along with several others, and seems to have solved the problem better than I'd expected!