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

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

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

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

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

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!! ).

Thanks!

• ###### 5. Re: Need to check whether first  two characters of a string is Alphabets or not
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