Results 1 to 4 of 4

Thread: TRIM issue

  1. #1
    Join Date
    May 2004
    Posts
    184

    Unanswered: TRIM issue

    I have an IF statement that is misbehaving or I'm missing something that should be very obvious...

    I'm looping through a temporary table via a cursor to perform a basic validation and update between two systems. The test for user.rehiredate IS NULL works fine. However, trim(user.rehireDate) !='' is returning false.

    What am I missing here?

    Thank you.

    FOR user IN user_cursor
    LOOP
    BEGIN
    ...
    dbms_output.put_line('the user.rehiredate is ' || trim(user.rehiredate));
    IF user.rehiredate IS NOT NULL AND trim(user.rehiredate) != '' THEN
    BEGIN
    v_rehiredate := to_date(user.rehiredate, 'MM/DD/YYYY');
    EXCEPTION
    when OTHERS THEN
    ...
    END; --end exception;
    END IF;

    dbms_output.put_line('the user v_rehiredate is ' || v_rehiredate);
    ...
    END
    end loop;

    dbms output is...
    the user.rehiredate is 07/08/1996
    the user v_rehiredate is

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool



    If user.rehireDate is NOT NULL and contains spaces, then trim(user.rehireDate) converts it to NULL and the '!=' comparison will not work.

    Better use:

    IF TRIM(user.rehiredate) IS NOT NULL THEN


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    May 2004
    Posts
    184
    Thanks, that seems to have solved it. However, I'm not sure why.

    The value of user.rehiredate in the data I was testing with was '07/08/1999'. (See dbms output).

    Even if there was an extra space ie., '07/08/1999 ' I don't think you mean that trim('07/08/1999 ') would return null, but rather that trim (' ') would return null. Correct?

    Thank you.

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool


    True, what I meant is that if the column contains space(s), TRIM() will convert to NULL.


    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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