storing numbers with seperators

[lution]

I'm trying to decide on the best way to store a string that represents a person's driver's license number. The format for each state is different so I can't apply a single format to all the values and when I send the data to other agencies they only want alpha-numeric characters in the data. Unfortunately the people typing in the values want to include any "-"'s so it is easier for them to read on the screen.

I can strip the "-" out when I send the field to other agencies but if I get data back, I'm not sure how I can find the string since it doesn't contain the "-"'s. Was thinking of having a hidden field that get's updated whenever the visible one changes.

Example:
DL #: A01-0002-220-01 (this is the field visible to the user)
DL # Hidden: A01000222001 (this is the field sent and recieved from other agencies)

The hidden field will not get changes from other agencies but it gives me a key to use other than a person's name and is more likely to be unique.

I hate putting calculated values in fields since it opens things to synchronization errors down the road but I haven't been able to think of any other way to do it. Any other ideas?

 

[georgedwilkinson]

It's only a calculated value when you send it out. It is an alias when you receive it back and thus, you need to keep it in an aliases table so you can compare it to the "desired" value when you receive it back.

 

[lution]

thanks George.

 

[Atomic Shrimp]

Why not store the unbroken string, then a foreign key to a table/list of specific formats - so for each record, you enter the string, then pick the format - when printing it out, you use the format choice to format the string.

So you might store:
licensenumber, formatID
A01000222001, 1

and you might have a table:
formatID, Formatexpr
1,###-####-###-##
2,#-###-#-##-##-#-#-#

So when you want to display the license number in proper format, you just need to look up its appropriate format expression and apply it, using the format function.