How to eliminate the extra character at the end of line in plsql using regex

Question

When I run this the v_hold data is "AAA", which is the length of my SQL field, however the length(v_hold) is 4. I know I can use substr() to take the length I need. trim() does not handle it either. Why is the length one more than it should be? Is there a solution around making a second function call by correctly notating "everything after the last comma that is ONLY alphanumeric". As always, thank you for the input.

    loop
     UTL_FILE.GET_LINE(utl_fh_r, v_input_string);
     dbms_output.put_line(v_input_string);

     v_emplid   := REGEXP_SUBSTR(v_input_string, '[^,]+', 1, 1);
     n_empl_rcd := REGEXP_SUBSTR(v_input_string, '[^,]+', 1, 2);
     n_goal_amt := REGEXP_SUBSTR(v_input_string, '[^,]+', 1, 3);
     v_dedcd    := REGEXP_SUBSTR(v_input_string, '[^,]+', 1, 4);
     v_hold := REGEXP_SUBSTR(v_input_string, '[^,\\r\\n]*.$'); 


     dbms_output.put_line(v_emplid || ' : ' || n_empl_rcd || ' : ' || n_goal_amt || ' : ' || 
                          v_dedcd || ' : ' || v_hold || 'len ' || length(v_hold));
    end loop;

 exception
  when UTL_FILE.READ_ERROR then
   UTL_FILE.FCLOSE(utl_fh_r);
   --dbms_output.put_line('UTL_FILE read error after line ' || n_lines_read || ' ' || V_IN_FILE);

  when NO_DATA_FOUND then
   dbms_output.put_line('NO DATA');
    end;

score 0 · Answer 1 · answered Jan 15 '20 at 21:07

The regex is wrong in the line that sets v_hold. It should end with .*$. You have *.

In other news, please change your REGEXP_SUBSTR format to this as your form does not handle a NULL element. See here for more info: https://stackoverflow.com/a/31464699/2543416

REGEXP_SUBSTR(v_input_string, '(.*?)(,|$)', 1, 1, NULL, 1);
REGEXP_SUBSTR(v_input_string, '(.*?)(,|$)', 1, 2, NULL, 1);
etc...

How to eliminate the extra character at the end of line in plsql using regex

1 Answers1