Check for letters/numbers pattern

[Asiolinka]

Hi All,

I need to write two patterns check:

LLLNNNNNN - 3 letters + 6 numbers

AND

LLNNNNNNN -2 letters + 7 numbers. 

I should have results as below  (need to obtain 'flag' field):

field1	flag
ABC123456	0
*BC123456	1
AB/123456	1
AB1234567	0
A*123456	1
AB12CD56	1

I tried to use:

(-(len(FIELD)=9 and istext(Left(FIELD,2)) and isnum(right(FIELD,7)))) BUT I saw special characters in results which is not correct. 

Could you please help? 

[N30fyte]

'A*123456' has 8 characters, 'AB12CD56' does not follow either pattern. Are they valid results?

[Asiolinka]

sorry, every single time it should have 9 char, please see below:

field1	flag
ABC123456	0
*BC123456	1
AB/123456	1
AB1234567	0
A*1234567	1
AB12CD567	1

[N30fyte]

The patterns in your original post are

LLLNNNNNN

and

LLNNNNNNN

The last row in your data is AB12CD567

which is

LLNNLLNNN

In the fourth row of your table, AB1234567 follows the second pattern but is flagged 0.

In the fifth row of your table , A*1234567 also follows the second pattern but is flagged 1.

Can you be more clear about your requirements?

[Chanty4u · Accepted solution]

Try this 

if(  match(FIELD, '^[A-Z]{3}\d{6}$') and len(FIELD)=9,    0,    1) asflag

[Kushal_Chawda · Accepted solution]

@Asiolinka  try below

Data:
Load *,
if(len(Field)=9,
if((len(KeepChar(Left(lower(Field),3),'abcdefghijklmnopqrstuvwxyz'))=3 and 
 isnum(right(Field,6))) or
(len(KeepChar(Left(lower(Field),2),'abcdefghijklmnopqrstuvwxyz'))=2 
and isnum(right(Field,7))),0,1),1) as Flag 
Inline [
Field
ABC123456
*BC123456
AB/123456
AB1234567
A*123456
AB12CD56 ];
 

[Asiolinka]

Thank you! Everything is now totaly clear.