1
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?

Dror Cohen
  • 125
  • 7

2 Answers2

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.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • editted the coluns in. i do have them in my code but forgot to write them in here. still deosn't work though. how do i work with a list of chars to disallow? what pattern should i use? – Dror Cohen Jan 13 '16 at 11:22
  • @DrorCohen - your code runs as expected with the columns (with the variable name declaration fixed,anyway). So no idea what you are doing differently. – Alex Poole Jan 13 '16 at 11:24
  • i tested the pattern you're suggesting " ^[[:alnum:]]([^[:alnum:]]|\"+|\.+)' " when changing the char from [ to " i'm still getting result: 0 but it should be 1 because " is allowed – Dror Cohen Jan 13 '16 at 11:28
  • @DrorCohen - well, I've shown your code and sample value working with that pattern and Kevin's. Maybe the string you're checking doesn't break the rules. – Alex Poole Jan 13 '16 at 11:30
  • :( 'd[av inder 1234' definately shouldn't work. but 'd"av inder 1234' or 'd.av inder 1234' do need to work – Dror Cohen Jan 13 '16 at 11:32
  • @DrorCohen - right, and your pattern is broken later, as I suggested. That wasn't what you originaly asked about. [Here's an SQL fiddle](http://sqlfiddle.com/#!4/9eecb7d/15834) with more sample values, and you can add more of your own, which shows that your pattern wasn't right anyway - though (with the colons) it did work for the example you gave. As you can see, Kevin's approach does work for all those values. – Alex Poole Jan 13 '16 at 11:38
1

You could also simply just use it like this.

Use a [] to define a group of character that should be matched

select case when regexp_like('d''v inder 1234' ,'^.[][!@#$%^&*()_+=`~{}''\|?/><,;:].*') then 0 else 1 end from dual;
SomeJavaGuy
  • 7,307
  • 2
  • 21
  • 33