plsql oracle prevent the use of special characters in the 2nd char of string

Question

create or replace function MyFunction(v_FileName in varchar2)

I want to prevent the use of special characters in the 2nd char in v_FileName.

these characters are:

!@#$%^&*()_+=`~[]{}'\|?/><,;:

(i want to allow the use of . and ")

i've tried several regexp_like expressions but no luck. example:

DECLARE
  vResult NUMBER DEFAULT NULL;
BEGIN    
  select case when regexp_like('d[av inder 1234' ,'^[[:alnum:]]([^[:alnum:]]|\"+|\.+)') then 0 else 1 end into vintResult from dual;
  dbms_output.put_line('result: '||vintResult);
END;

output:

result: 1

result should be 0 becaue the char [ is supposed to be disallowed.

what am i doing wrong?

Answer 1

You're missing the colons around the first character class, [[alnum]]:

'^[[:alnum:]]([^[:alnum:]]|\"+|\.+)'

e.g.

select case when regexp_like('d[av inder 1234',
  '^[[:alnum:]]([^[:alnum:]]|\"+|\.+)')
    then 0 else 1 end as vintResult from dual;

VINTRESULT
----------
         0

Or:

DECLARE
  vintResult NUMBER DEFAULT NULL;
BEGIN    
  select case when regexp_like('d[av inder 1234' ,'^[[:alnum:]]([^[:alnum:]]|\"+|\.+)') then 0 else 1 end into vintResult from dual;
  dbms_output.put_line('result: '||vintResult);
END;
/

PL/SQL procedure successfully completed.

result: 0

If you're allowing any character in the first position you could just use a dot:

'^.([^[:alnum:]]|\"+|\.+)'

... but I suspect you do only want alphanumerics in the first position. And if you have a list of specific characters to exclude then it might be clearer, particularly for someone maintaining this in the future, to list those out instead of using the second character class - as in @KevinEsche's answer. (I'm not sure the pattern you have quite matches what you said you wanted to ban anyway).

DECLARE
  vintResult NUMBER DEFAULT NULL;
BEGIN    
  select case when regexp_like('d[av inder 1234' ,'^.[][!@#$%^&*()_+=`~{}''\|?/><,;:].*')
    then 0 else 1 end into vintResult from dual;
  dbms_output.put_line('result: '||vintResult);
END;
/

PL/SQL procedure successfully completed.

result: 0

Notice the placement of the square brackets within the group of characters, which have moved from your original string; that's to prevent them being misinterpreted as ending the group early.