String Comparison in ORACLE PL/SQL

Question

I read data from an excel sheet and extracted column values in ORACLE PL/SQL. But comparison in if condition is not working as excepted.

v_value5 value is display proper value. But when I compare, the condition is not matching. When I tried to display the v_value5 variable length, it always display actual length +1. For example, if the v_value5 = 'E', then the length always shows 2.

DBMS_OUTPUT.PUT_LINE('Length -> '|| LENGTH(TRIM(v_value5)));

Result is 2

Could anyone has any idea what is happening in string comparison here and why length shows actual length+1?

I suspect the REPLACE function returns some additional characters.

Note v_value5 is of type VARCHAR(255).

v_file := UTL_FILE.FOPEN('TEST', 'Test.csv', 'R',30000);
  LOOP
    BEGIN
      UTL_FILE.GET_LINE(v_file, v_line);
            v_value1 := REGEXP_SUBSTR(v_line, '[^,]+', 1, 1);
            v_value2 := REGEXP_SUBSTR(v_line, ',.+', 1, 1);
            v_value3 := REPLACE(v_value2, ',', '');
            v_value4 := REPLACE(v_value3, ' ', '');
            v_value5 := REPLACE(v_value4, '"', '');
            IF LENGTH(v_value5) > 1 THEN
                    IF v_value5 = 'E'
                        THEN  v_Id := '1';
                    ELSIF  v_value5 = 'D' 
                        THEN v_Id := '2';
                    ELSIF  v_value5 = 'E' 
                        THEN v_Id := '3';
                    ELSIF  v_value5 = 'DS' 
                        THEN v_Id := '4';    
                    ELSIF  v_value5 = 'EDS' 
                        THEN v_Id := '5';
                    ELSE 
                        DBMS_OUTPUT.PUT_LINE('No such Data found'); 
                END IF;
            END IF;      
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
      EXIT;
    END;
  END LOOP;
  UTL_FILE.FCLOSE(v_file); 

Answer 1

UTL_FILE.GET_LINE(v_file, v_line); is returning the line, including the line feed (ASCII 13) character at the end of the line. You need to remove it.

v_file := UTL_FILE.FOPEN('TEST', 'Test.csv', 'R',30000);
LOOP
  DECLARE
    v_line   VARCHAR2(4000);
    v_first  VARCHAR2(4000);
    v_second VARCHAR2(4000);
    c_pattern CONSTANT VARCHAR2(4000) := '('
                                         || '[^,"' || CHR(13) || CHR(10) || ']*'
                                         || '|"(""|[^"])*"'
                                         || ')'
                                         || '('
                                         || ','
                                         || '|'
                                         || CHR(13) || '?' || CHR(10) || '?$'
                                         || ')';
  BEGIN
    UTL_FILE.GET_LINE(v_file, v_line);
    v_first  := REGEXP_SUBSTR(v_line, c_pattern, 1, 1, NULL, 1);
    v_second := CASE TRANSLATE(
                       REGEXP_SUBSTR(v_line, c_pattern, 1, 2, NULL, 1),
                       'A, "',
                       'A'
                     )
                WHEN 'E' THEN '1'
                WHEN 'D' THEN '2'
                WHEN 'E' THEN '3'   -- This will never match as you have already matched E
                WHEN 'DS' THEN '4'
                WHEN 'EDS' THEN '5'
                END;
    IF v_second IS NULL THEN       
      DBMS_OUTPUT.PUT_LINE('No such Data found'); 
    END IF;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      EXIT;
  END;
END LOOP;
UTL_FILE.FCLOSE(v_file);