11 Replies Latest reply: Jan 18, 2013 3:05 PM by Raivis RSS

    checking letters in string PL/SQL

    Raivis
      Hi everyone. I have problem.
      I have varchar2 field in my form. In this form user write car numer.

      Example: 'AB1234'

      I have specific rules, that first 2 symbols must be leters 'AA' and other symbols must be numbers....

      Can someone tell me is it possible to use statement not between 'A' and 'Z' .....

      for example i have code...
      declare
      letters varchar2(2);
      letter1 varchar2(1);
      letter2 varchar2(1);
      numbers varchar2(4);
      leng_numb number;
      symb varchar2(1);
      begin ... 
      .....
      letters := substr('AB1234', 1,2);
      letter1 := substr(letters,1,1);
      letter2 := substr(letters, 2,1);
      numbers := substr('AB1234',3);
      if letter1 between '0' and '9' then
      raise error.... 
      elsif letter2 between  '0' and '9' then
      raise error...
      else
      leng_numb :=length(numbers);
      for i in 1..leng loop
      symb := substr(numbers,i,1);
      if symb between 'A' and 'Z' then
      raise error....
      end if;
      end loop;
      end;
      Help me if i can do this kind of logic.... i need to check if that numbers part in string dosn't contain any leters.... because later i need to do arithmetic functions with this string....
        • 1. Re: checking letters in string PL/SQL
          Etbin
          Maybe
          select varchar2_field,
                 case when rtrim(varchar2_field,'0123456789') != 'AA'
                      then 'fail'
                 end the_check
            from your_table
          Regards

          Etbin
          • 2. Re: checking letters in string PL/SQL
            Solomon Yakobson
            If all you need is to check if field conforms to your rules use:
            IF NOT REGEXP_LIKE(field,'^AA\d+$')
              THEN RAISE ERROR ...
            END IF;
            SY.
            • 3. Re: checking letters in string PL/SQL
              Frank Kulash
              Hi,

              Give a string str, this condition
              ...     TRANSLATE ( RTRIM ( str
                                   , '0123456789'
                               )
                          , 'BCDEFGHIJKLMNOPQRSTUVWXYZ'
                          , 'AAAAAAAAAAAAAAAAAAAAAAAAA'
                          ) = 'AA'
              will be TRUE if (and only if) str consists of exactly 2 capital letters (any letters, 'A' through 'Z' will do) followed by any number of digits ('0', '1', ..., '9').

              You can get the same results with regular expressions, but that would be less efficient.
              • 4. Re: checking letters in string PL/SQL
                Raivis
                it could mby work...
                but i need this check before inserting data...
                count_letters :=length(rtrim (numbers, 'abcdefghijklmnoprstuvz'));
                
                if count_letters > 0 then raise error 
                Edited by: Raivis on Jan 18, 2013 12:11 PM
                • 5. Re: checking letters in string PL/SQL
                  Etbin
                  Did you check [url http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions160.htm#SQLRF06104]rtrim ?
                  <tt> rtrim(varchar2_field,'0123456789') </tt> will trim all digits on the right side of your varchar2_field (column value actually) leaving left side letters (and maybe other special characters). Now, if I got it right, if you're left with 'AA' everything's fine else an error must be raised (see Frank's solution if the first two characters can be any two upercase letters.

                  Regards

                  Etbin
                  • 6. Re: checking letters in string PL/SQL
                    Raivis
                    no you didn't understand me right...

                    i need to check 2 part of input value ...


                    for example if user writes SA1234 that is correct , but if user writes SA12B2 than he should get error about putting letter in place of number...
                    • 7. Re: checking letters in string PL/SQL
                      Etbin
                      Sorry !
                      Look at Frank's solution then: it will <strike>transform</strike> translate your SA12B2 into AA12A which is not equal to AA so it's wrong.

                      Regards

                      Etbin

                      Edited by: Etbin on 18.1.2013 21:39

                      Edited by: Etbin on 18.1.2013 21:41
                      using <tt> upper(str) </tt> might be a bit better
                      • 8. Re: checking letters in string PL/SQL
                        Frank Kulash
                        Hi,
                        Raivis wrote:
                        no you didn't understand me right...

                        i need to check 2 part of input value ...


                        for example if user writes SA1234 that is correct , but if user writes SA12B2 than he should get error about putting letter in place of number...
                        That's what I though you requested earlier. What's wrong with the solution I posted earlier?
                        ...     TRANSLATE ( RTRIM ( str
                                             , '0123456789'
                                         )
                                    , 'BCDEFGHIJKLMNOPQRSTUVWXYZ'
                                    , 'AAAAAAAAAAAAAAAAAAAAAAAAA'
                                    ) = 'AA'
                        If str = 'SA1234' then the condition above returns TRUE.
                        If str = 'SA12B2' then the condition above returns FALSE.

                        The expression above checks for 2 things:
                        (1) The first 2 characters must be letters ('A', 'B', ..., 'Z')
                        (2) The rest of the string must be digits ('0', '1', ..., '9')
                        If you don't want to test part (1), but only test part (2) and ignore the first 2 characters, then
                        RTRIM ( SUBSTR (str, 3)
                              , '0123456789'
                              ) IS NULL
                        will be TRUE when str consists of only digits (except for the first 2 characters) and it
                        will be FALSE when str contains any letters, punctuation, or other characters besides digits (after the first 2 characters).
                        • 9. Re: checking letters in string PL/SQL
                          Raivis
                          Ok i will try this one...
                          but if my memory dosn't lie i once tried translate function in oracle forms 6i ..... but when i compiled when-valid-record trigger it said that translate function need's to be declared.... could it be possible that in oracle forms 6i i can't use translate and trim functions ?? ?
                          • 10. Re: checking letters in string PL/SQL
                            Frank Kulash
                            Hi,

                            If you have a problem that only exists in Forms, then the Forms is the right place to ask about it.

                            Even if your immediate need is to validate the string in Forms, you probably want a databse function to do that. Later on, when you want to use the same validation in a query, or in a database trigger, or anywhere else, you can call the exact same function. When you call database functions, even if you call them from Forms, limitations that exist in Forms don't apply.
                            • 11. Re: checking letters in string PL/SQL
                              Raivis
                              OK tnx man :)