Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Location
    Jersey, UK
    Posts
    108

    Unanswered: How do I exclude carriage returns as part of a IS NULL exclusion clause...

    Hi

    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]


    Cheers - Andy
    Last edited by randycarpet; 01-06-05 at 08:52.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is a really slippery slope, but if you are dealing with 8 bit ASCII, you could use:
    Code:
    LIKE '%[!-~]%'
    to see if there are any printable characters (this expression ignores whitespace).

    -PatP

  3. #3
    Join Date
    Jan 2004
    Location
    Jersey, UK
    Posts
    108
    Very interesting and very cool (speaking purely as a geek!).

    I got it to work by doing this:

    and field like '%[a-z0-9]%'

    This expression only shows fields that contain letters and/or numbers and excludes stupid entries like periods, comma's, carraige returns...

    Thanks very much for your help!


    Andy

  4. #4
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    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...


    just askin'

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Ruprect
    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...


    just askin'
    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!

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •