To filter alphanumeric combination, is it match pattern?

[Former Member]

Hi

  I am using BODS 14.1.1.210  .

I would like to filter one column which has the pattern AXXXXX (where X can be number or alphabet or combination of both)

I treid to use match pattern but I X means alphabet and 9 means number but how can I filter all the combinations of numbers or alphabets.

for example;

Input

A12345

A1234567

AabcdE

Aabcde

A12asd

Aas12d

Askljfdadf

Output

A12345

AabcdE

Aabcde

A12asd

Aas12d

Cheers!

[venkataramana_paidi · Accepted solution]

Hi Josh,

Instead of match patten use match_regex  for your requirement .  You can use like this .

OUTPUT = decode(match_regex(INPUT,'[A-Z][A-Za-z0-9]{5}',null)<>0,$INPUT,null );

You can filter on this column like OUTPUT is not null means you can get matched values.  You need one additional query transform for filter .

Thanks & Regards,

Ramana.

[Former Member · Accepted solution]

Hi Venkata

  How do I make sure it starts with A and in has in total 6 characters?

Cheers!

[venkataramana_paidi · Accepted solution]

Hi Josh ,

Instead of keeping the alphabetic set just keep only A as shown below .

OUTPUT = decode(match_regex(INPUT,'A[A-Za-z0-9]{5}',null)<>0,$INPUT,null );

Thanks & Regards,

Ramana.

[Former Member · Accepted solution]

And does the {5} imply that A followed by 5 characters?

[venkataramana_paidi · Accepted solution]

{} denotes the occurrences.  In my code I have given A[a-zA-Z0-9]{5}  means  either small or upper or digits should come 5 times followed by A

[Former Member · Accepted solution]

Hi Venkata

How do you think I can use the function to filter the format

SXX-AXXXXXX (S followed by 2 characters followed by a hyphen followed by A followed by 6 characters where X is[a-zA-Z0-9])

Cheers!

[venkataramana_paidi · Accepted solution]

Hi Josh ,

Yes you can use in the where clause like

decode(match_regex(INPUT,'S[a-zA-Z0-9]{2}-A[a-zA-Z0-9]{6}',null)<>0,INPUT,null ) is not null Instead of adding one more query transform and field.

Thanks & Regards,

Ramana.