Validating Data in a load script

[#1]

Hi,

I'm trying to write a load script that only loads data from a field where it is in the correct format - 'XX## #XX' i.e. UK postcode. I have introduced a validation rule using the LEN function but how would I go about ensuring all match the exact format?

Thanks,

D

[Oleg_Troyansky]

I don't think there is a function that validates format pattern... You might have to parse the string and validate each part separately... Search the Help Text for "String Functions" - there are quite many, and they are all described in one article.

[#3]

thanks for letting me know.

[#4]

Just thinking this one through, even if I parsed the string (e.g. left(postcode, 2)) how would I validate the part?

D

[Oleg_Troyansky]

That's why I mentioned String functions - there is a variety of ways to compare strings and substrings and ensure that vertain part is noly numeric, and certain part is only characters - Index, Match, FindOneOf, and more... You just need to formulate your conditions properly. Worst case - you might have to validate each one of the 8 characters, if it's so important...

[johnw]

I'm honestly not thinking of a better way than pure brute force:

if( index('ABCDEFGHIJKLMNOPQRSTUVWXYZ',mid(postcode,1,1))
and index('ABCDEFGHIJKLMNOPQRSTUVWXYZ',mid(postcode,2,1))
and index('0123456789' ,mid(postcode,3,1))
and index('0123456789' ,mid(postcode,4,1))
and mid(postcode,5,1) = ' '
and index('0123456789' ,mid(postcode,6,1))
and index('ABCDEFGHIJKLMNOPQRSTUVWXYZ',mid(postcode,7,1))
and index('ABCDEFGHIJKLMNOPQRSTUVWXYZ',mid(postcode,8,1)),postcode,'Error') as postcode

I mean, we could create fake functions, but I don't think it's worth the complexity. I think it would look something like this:

SET alpha = index('ABCDEFGHIJKLMNOPQRSTUVWXYZ',mid($1,$2,1));
SET digit = index('0123456789',mid($1,$2,1));
SET space = mid($1,$2,1) = ' ';

if( $(alpha(postcode,1))
and $(alpha(postcode,2))
and $(digit(postcode,3))
and $(digit(postcode,4))
and $(space(postcode,5))
and $(digit(postcode,6))
and $(alpha(postcode,7))
and $(alpha(postcode,8)),postcode,'Error') as postcode

It won't surprise me if there's a clever way to do it. I'm just not thinking of one.