How check if the field has the same characters combination?

[Asiolinka]

Hi,

Hope it will be my last question today. 

I need to find uncorrect combinations of number in one field. 

The correct format should be:  9 characters and first 2 should be letters (for example: AB 1234567).

Thank you for any ideas. I started with len 9 chr but I also need to see all the records with special characters, with more than two letters etc.

[Asiolinka]

Flag is my expected output. 

Correct every single time if record= 2 first letters + 5 numbers

FIELD	Flag
AA1234567	1
AB1334567	1
GH1232342	1
ABC1234567	0
AF12F4567	0
AB12*4567	0

[DS395]

Hi Asiolinka,

i hope this will help you:

LOAD
[FIELD],
IF(LEN([FIELD])<>9,0,
IF(LEN(KEEPCHAR(UPPER(LEFT([FIELD],2)),'ABCDEFGHIJKLMNOPQRSTUVWXYZ'))<>2,0,
IF(LEN(KEEPCHAR(UPPER(RIGHT([FIELD],7)),'0123456789'))<>7,0,1))) AS FLAG
INLINE
[
FIELD
AA1234567
AB1334567
GH1232342
ABC1234567
AF12F4567
AB12*4567
];

IF you only accept uppercase letters, then u have to remove the UPPER()-Function in the IF-Statement.

Best regards

dash

[Kushal_Chawda · Accepted solution]

@Asiolinka  try below

LOAD FIELD,
-(len(FIELD)=9 and istext(Left(FIELD,2)) and isnum(right(FIELD,7))) as Flag 
FROM table

[Asiolinka]

Thank you very, very, very much!