0

How to limit the special characters from being accepted by specific column.? can this be done with REGEXP_LIKE ? Apart from alphanumerics below are the special characters to be allowed from the text field and constraints cant be added in definition. Have to handle this case statement when not matched then raise error else populate the same value.

    !
    #
    $
    %
    &
    space
    (
    )
    +
    ,
    -
    .
    /
    :
    ;
    <
    =
    >
    ?
    @
    [
    \
    ]
    _


   DECLARE
   A VARCHAR2(100);
   B VARCHAR2(100);
   BEGIN
   A :='_  _\@?=><?:;:>./.-+(%$#!;:aA10b$a9#%-.(@B=Aa1+z0,!#$% ),(+,-./;_  <\_:<>=?@\_  ';

    SELECT CASE WHEN regexp_like(A,'^[A-Za-z0-9!#$%()+,-./;:<>=?@\_&[:space:]]+$')
    THEN 'TRUE' ELSE 'FALSE' END CASE INTO B
    FROM DUAL;

    DBMS_OUTPUT.PUT_LINE(B);
  END;

Tried below and got the required output. Thanks.

    DECLARE
    A VARCHAR2(100);
    B VARCHAR2(100);
    BEGIN
    A :='_  _\@?=><?:;:>./.-+(%$#!][;:aA10b$a9#%-.(@B=Aa1+z0,!#$% ),(+,-./;_  <\_:<>=?@\_ [ ';

    SELECT CASE WHEN regexp_like(A,'^[][[:alnum:][:space:]!#$%()+,-./;:<>=?@\\_&]+$')
    THEN 'TRUE' ELSE 'FALSE' END CASE INTO B
    FROM DUAL;

    DBMS_OUTPUT.PUT_LINE(B);
    END;
Devoloper250
  • 753
  • 2
  • 8
  • 12
  • @Aleksej Have tried similar to the updated expression in question but it isn't working. can you help with regexpression to be used. – Devoloper250 May 23 '17 at 11:00
  • If you need to know how to add a constraint based on a regexp, [this question](https://stackoverflow.com/questions/7621568/oracle-11g-check-constraint-with-regex) should have the answer. If your issue is in writing the regexp to match your need, please post the code you tried, explaining why it's not working and what it should do – Aleksej May 23 '17 at 11:12
  • Thanks for the reply @Aleksej . I have updated the solution that I tried. But when I tried including special characters like [ ] solution is failing. Is there any way to escape these special characters. – Devoloper250 May 26 '17 at 09:48
  • One way could be by using the ` for all the characters you need except `[` and `]` and use them in an `OR`; something like `^(\[|\]|[...])+$` – Aleksej May 26 '17 at 10:04
  • @Aleksej I have tried solution updated in question , solution is working for all special characters but I coundn't include **&[]** characters . – Devoloper250 May 30 '17 at 08:27
  • @Aleksej have tried updated sql in question and got the required output. Thank you for the help. – Devoloper250 May 30 '17 at 10:50

0 Answers0