I need to allow only set of characters i.e., a to z A to Z 0 to 9 . !@#$% *()_=+|[]{}"'';:?/.,-
but When I add dash(-) character to below query it is not working please help me at earliest.
SELECT :p_string FROM dual
WHERE NOT REGEXP_LIKE (translate(:p_string,chr(10)||chr(11)||chr(13), ' '),'[^]^A-Z^a-z^0-9^[^.^{^}^!^@^#^$^%^*^(^)^_^=^+^|^\^{^}^"^''^;^:^?^/^,^-^ ]' );
The extra ^ symbols inside the bracket expression in your pattern are not, as I think you expect, negations; only the first ^ inside the brackets does that.
The main issue that is causing, apart from allowing that actual circumflex symbol to be matched when you didn't seem to want it, is that you end up with ^-^ being treated as a range.
To include a literal - it has to be the first or last thing in the brackets; from the docs:
To specify a right bracket (]) in the bracket expression, place it first in the list (after the initial circumflex (^), if any).
To specify a hyphen in the bracket expression, place it first in the list (after the initial circumflex (^), if any), last in the list, or as an ending range point in a range expression.
So as you need to do both, make the hyphen last; you can change your pattern to:
'[^]A-Za-z0-9[.{}!@#$%*()_=+|\{}"'';:?/, -]'
You could also skip the tralsnate step by including those special characters in the pattern too:
'[^]A-Za-z0-9[.{}!@#$%*()_=+|\{}"'';:?/, '||chr(10)||chr(11)||chr(13)||'-]'
Looks like you need to permit only (7-bit) ASCII characters with exception of ~ and ^
In this case I would try it like this:
WHERE CONVERT(p_string, 'US7ASCII') = p_string
AND NOT REGEXP_LIKE(p_string, '~|\^')
Instead of CONVERT(p_string, 'US7ASCII') = p_string you can also use ASCIISTR(REPLACE(p_string, '\', '/')) = REPLACE(p_string, '\', '/')
