This discussion is archived
5 Replies Latest reply: Jul 8, 2013 10:10 AM by Chris Hunt RSS

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

Siva.V Newbie
Currently Being Moderated

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 Journeyer
    Currently Being Moderated

    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 Guru
    Currently Being Moderated


    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 Pro
    Currently Being Moderated

    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 Pro
    Currently Being Moderated

    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 Pro
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points