Postcode field validation / Masks 2

[CR85user]

Hi there,

Crystal XI or 8.5

I am looking to apply a similar principal to the edit masks available for parameter validation to check if a given field mataches that expected of a UK postcode.

Essentially, my selection criteria would select all postcodes which DO NOT match the given masks.

The following formats ARE considered to be genuine postcode formats:

A# #AA
A## #AA
A#A #AA
AA# #AA
AA## #AA
AA#A #AA

(Where A = alpha and # = numeric)

I'm aware that I can probably use isnumber() as well as mid() to preform checks but would be interested in a more elgant solution.

as you can see - the last four characters are the same format but the 1st 4 can vary.

Any ideas?

'J

 

[CR85user]

There is a regular expression available which can be used to check the validity:

(GIR 0AA|[A-PR-UWYZ]([0-9]{1,2}|([A-HK-Y][0-9]|[A-HK-Y][0-9]([0-9]|[ABEHMNPRV-Y]))|[0-9][A-HJKS-UW]) [0-9][ABD-HJLNP-UW-Z]{2})


Could this be converted to some format which crystal could use as the basis of a selection criteria?

'J

 

[Madawc]

It's bound to be a complex test - check first if someone has already created one.

I think you need to use Split({pcode}, " ")[1] and Split({pcode}, " ")[2]to separate the two elements, and also UBOUND to confirm there are exactly two.

Use LENGTH to confirm each element is OK. Also LEFT and MID and check that the correct characters are alpha, or are numeric.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[CR85user]

It's bound to be a complex test - check first if someone has already created one.

The same thought occured to me I am ever hopeful.

I will have a go later at running up a formula to check most of the basic length/split/format checks.

I will be surprised though if nobody has as yet done something similar and has it floating about in their repository waiting to be posted here.

'J

 

[CR85user]

For anybody interested this is the temporary formula I am using for validation:

//{@Postcvalidation}
//Check null or blank
if
(isnull({table.postcodestring}) or trim({table.postcodestring}) like '')
then 'Blank' else
//Check all lengths for 1st digit
if
isnumeric({table.postcodestring} [1])
then '1st Digit numeric' else
//Check if single space in str
if
not({table.postcodestring} like '* *')
then 'No Space in postcode' else
//Check 6 digit len
if
len({table.postcodestring}) = 6 and
(
not(isnumeric({table.postcodestring} [2])) or
not(InStr ({table.postcodestring},' ') = 3) or
not(isnumeric({table.postcodestring} [4])) or
isnumeric({table.postcodestring} [5]) or
isnumeric({table.postcodestring} [6])
)
then '6 Digit Postcode Error' else
//Check 7 digit len
if
len({table.postcodestring}) = 7 and
(
not(InStr ({table.postcodestring},' ') = 4) or
(
not(isnumeric({table.postcodestring} [2])) and
not(isnumeric({table.postcodestring} [3]))
) or
not(isnumeric({table.postcodestring} [5])) or
isnumeric({table.postcodestring} [6]) or
isnumeric({table.postcodestring} [7])
) then '7 Digit Postcode Error' else
//Check 8 digit len
if
len({table.postcodestring}) = 8 and
(
not(InStr ({table.postcodestring},' ') = 5) or
isnumeric({table.postcodestring} [1]) or
isnumeric({table.postcodestring} [2]) or
not(isnumeric({table.postcodestring} [3])) or
not(isnumeric({table.postcodestring} [6])) or
isnumeric({table.postcodestring} [7]) or
isnumeric({table.postcodestring} [8])
) then '8 Digit Postcode Error' else
'OK'


If needed I can then do a record selection of;

{@postcvalidation} <> 'OK'

Any suggestions on cleaning up the formula to make it slightly more simplistic or improve it in any fashion?

Maybe I would be better using case to state positional info just once and then call the case for each potential length of field?

I am aware that I am still not fully complying with the regex as I am simply comparing to non-numeric for alpha. Is there any way to alter this to not include any ascii chars such as &£!> ?

Thanks in advance.

'J