How to separate string and number combination from any field?

[Anonymous]

Hi Team,

I have filename in my data which has some values and here I have to separate all the file-name  with following structure

ABCXXXXX (EG. PTN012221, TRA012221), and have to highlight the filename if the structure is not in same format.

Requesting for your advice.

Thanks is advance,

[Miguel_Angel_Baeyens]

Hello Pooja,

Use Len() and PurgeChar() functions on the field so

If(Len(KeepChar(filename, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')) <> 3, 'Wrong filename', 'Filename OK') AS Filename_Right

And elaborate on that idea.

Miguel

[Anonymous]

Data:

FileName

PTN012221

TRA012221

QWERTY012221

Script to load data:

LOAD FileName,

  If(Len(KeepChar(FileName, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')) <> 3, 'Wrong FileName', 'FileName OK') AS Filename_Right

FROM

(ooxml, embedded labels, table is FileName);

Result:

[rupamjyotidas]

Should be , If I am not wrong.

If(Len(PurgeChar(filename, '1234567890')) <> 3, 'Wrong filename', 'Filename OK') AS Filename_Right

[Miguel_Angel_Baeyens]

Changed the if, else statements, thanks!

I'm correcting my post above.

[Anonymous]

Hi All,

Thanks for your reply, my field name should be in combination of 3-character and 5-numbers and others will be as wrong file name.

Thanks,