5 Replies Latest reply on Jul 8, 2013 10:10 AM by Chris Hunt

    Need to check whether first  two characters of a string is Alphabets or not

    Siva.V

      Hi,

       

      Need to check whether first two characters of a string is alphabet or not.

       

      Lets say for Ex,

       

      String as 'DE123456' i need to check whether first  character is non-numeric and then second character as non numeric.

       

      kindly help me on this.

       

                      IF length(trim(p_parserec(31))) = 22 AND p_parserec(31) LIKE 'DE%'  THEN

                          AUFTRAGGEBERKONTONR := trim(p_parserec(31)) ;

                       ELSIF  (length(trim(p_parserec(31))) > 22 AND length(trim(p_parserec(31))) < 35)  AND p_parserec(31) NOT LIKE 'DE%'  THEN

                         AUFTRAGGEBERKONTONR := lpad(trim(p_parserec(31)), 34, 0) ;
                       ELSIF length(trim(p_parserec(31))) > 10 AND ascii(substr(p_parserec(31), 1, 2)) between 48 and 57 THEN

                         AUFTRAGGEBERKONTONR := lpad(trim(p_parserec(31)), 10, 0) ;

                       ELSE
                          p_errorcd   := sqlcode ;
                          p_errordata := sqlerrm ;
                     END IF ;

       

      Note : In the third else if condition the character should be greater than 10 and first 2 characters should not be alphabets.

        • 1. Re: Need to check whether first  two characters of a string is Alphabets or not
          S10390

          WITH t AS

               (SELECT 'AB123456' AS KEY

                  FROM DUAL

                UNION ALL

                SELECT 'CD234567'

                  FROM DUAL

                UNION ALL

                SELECT 'A1234567'

                  FROM DUAL

                UNION ALL

                SELECT 'DE234556'

                  FROM DUAL)

          -- END OF TEST DATA

          SELECT *

            FROM t

          WHERE REGEXP_LIKE (SUBSTR (KEY, 1, 2), '([[:alpha:]]$)')

           

          • 2. Re: Need to check whether first  two characters of a string is Alphabets or not
            Purvesh K


            One simple way:

             

            with data as
            (
              select 'DE123456' col from dual union all
              select '123456ER' col from dual union all
              select ',23463' col from dual
            )
            select col, case when ascii(substr(col, 1, 2)) between 48 and 57 then 1 else 0 end is_numeric
              from data
             where case when ascii(substr(col, 1, 2)) between 48 and 57 then 1 else 0 end = 1;
            

            Using Regular Expressions:-

            with data as
            (
              select 'DE123456' col from dual union all
              select '123456ER' col from dual union all
              select ',23463' col from dual
            )
            select col
              from data
             where regexp_like(substr(col, 1, 2), '[^[:digit:]|[:punct:]]');
            

             

            You did not mention about special characters viz. Comma, Semi Colon, Period etc. If you wish to remove those, use the above query else remove the |[:punct:] from the example.

            • 3. Re: Need to check whether first  two characters of a string is Alphabets or not
              Ashu_Neo

              Siva.V wrote:

              Need to check whether first two characters of a string is alphabet or not.

              To this requirement only regexp_like will work too! No need of some other string function!

              Like:-

                      -- in regexp_like last parameter shows to ignore case (optional).

              SQL> with t as
                2  (select 'AB123456' as key from dual union all
                3  select 'CD234567' from dual union all
                4  select 'A1234567' from dual union all
                5  select 'A52H4341' from dual union all
                6  select 'Dk274341' from dual union all
                7  select 'DE234556' from dual)
                8  select key
                9  from t
              10  where regexp_like(key,'^[A-Z]{2}','i') -- even '^[[:alpha:]]{2}' or '^\D{2}' pattern can be replaced for same result..

                11  /

              KEY
              --------
              AB123456
              CD234567
              Dk274341
              DE234556

               

              Thanks!

              • 4. Re: Need to check whether first  two characters of a string is Alphabets or not
                Ashu_Neo

                Siva.V wrote:

                 

                                IF length(trim(p_parserec(31))) = 22 AND p_parserec(31) LIKE 'DE%'  THEN

                                    AUFTRAGGEBERKONTONR := trim(p_parserec(31)) ;

                                 ELSIF  (length(trim(p_parserec(31))) > 22 AND length(trim(p_parserec(31))) < 35)  AND p_parserec(31) NOT LIKE 'DE%'  THEN

                                   AUFTRAGGEBERKONTONR := lpad(trim(p_parserec(31)), 34, 0) ;
                                 ELSIF length(trim(p_parserec(31))) > 10 AND ascii(substr(p_parserec(31), 1, 2)) between 48 and 57 THEN

                                   AUFTRAGGEBERKONTONR := lpad(trim(p_parserec(31)), 10, 0) ;

                                 ELSE
                                    p_errorcd   := sqlcode ;
                                    p_errordata := sqlerrm ;
                               END IF ;

                 

                Note : In the third else if condition the character should be greater than 10 and first 2 characters should not be alphabets.

                Instead of writting this, you could have put 3/4 plain lines more for narrating your original requirement in details with some sample data! By the way, what kind of requirement you have; that needs so may string functions( like length, trim, substr, ascii along with a user defined program p_parserec(31) - what it does!! ).

                Your information looks unreadable and insufficient to me. Please make it clean and follow Re: 2. How do I ask a question on the forums?

                 

                Thanks!

                • 5. Re: Need to check whether first  two characters of a string is Alphabets or not
                  Chris Hunt
                  Siva.V wrote:

                   

                  Need to check whether first two characters of a string is alphabet or not.

                   

                  [...]

                   

                  i need to check whether first  character is non-numeric and then second character as non numeric.

                  So which is it? Alphabetic or non-numeric? They're different things, you know. Alphabetic means only the characters A-Z and a-z (probably plus any umlauted variations, since you appear to be working in German), non-mumeric is any character other than 0-9.

                   

                  A sneaky way to check for the presence or absence of certain characters is to use the TRANSLATE() function:

                  IF TRANSLATE(my_string,'A1234567890','A') IS NULL THEN -- my_string is numeric (it only includes digits) if this is true

                   

                  IF TRANSLATE(my_string,'1ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz','1') IS NULL THEN -- my_string only contains letters if this is true

                  Obviuosly, you can reverse the sense of the above tests by checking to see if the result of the TRANSLATE() is not null