| Computers Forum Index » Computer - Databases » What data standards should a database designer know... |
|
Page 2 of 2 Goto page Previous 1, 2 |
|
| Author |
Message |
| --CELKO--... |
 Posted: Fri Apr 10, 2009 2:55 pm |
|
|
|
Guest
|
Quote: Finance: FIX, BSB, SWIFT, CHIPS, ACH. These are all protocols or file formats, they imply bank ID codes like the SWIFT code There are also hundreds of lesser known standards like APCA with their own codes, I'd start with SWIFT and FIX.
I have SWIFT, IBAN and some of the stuff we use in the States.
Quote: A database designer also has to see the protocols, because generically you need to capture enough data to do the transaction.
While I agree with you, the idea of the book is to present just the
encodings -- what a DB designer would need to know to write some DDL
with a CHECK() constraint.
Quote: For example, you've got ISO 3 character country codes in your database, now you realize you need the ISO 2 character country code to do a transaction.
ISO country codes was one of the first things I got. I also got two
surprises: (1) It is more general and historical than I knew (2) it is
embedded in more other ISO standards. |
|
|
| Back to top |
|
|
|
| Philipp Post... |
| Posted: Sat Apr 11, 2009 3:17 pm |
|
|
|
Guest
|
Quote: Various national postal codes
Just found a CHECK constraint I wrote as an exercise for myself some
years ago. It validates the postal codes for 58 countries from which I
had the pattern and which would fit into a LIKE predicate:
CREATE TABLE International_Addresses
(address_id INT NOT NULL PRIMARY KEY,
-- other columns
country_code CHAR(2) NOT NULL, -- ISO 3166
postal_code VARCHAR(10) NOT NULL,
CONSTRAINT validate_postal_code
CHECK(postal_code LIKE
CASE country_code
WHEN 'AR' THEN '[0-9][0-9][0-9][0-9]'
WHEN 'AT' THEN '[0-9][0-9][0-9][0-9]'
WHEN 'BE' THEN '[0-9][0-9][0-9][0-9]'
WHEN 'BR' THEN '_________'
WHEN 'CA' THEN '[0-9][A-Z][0-9] [A-Z][0-9][A-Z]'
WHEN 'CH' THEN '[0-9][0-9][0-9][0-9]'
WHEN 'CN' THEN '[0-9][0-9][0-9][0-9][0-9][0-9]'
WHEN 'CR' THEN '[0-9][0-9][0-9][0-9]'
WHEN 'CY' THEN '[0-9][0-9][0-9][0-9]'
WHEN 'CZ' THEN '[0-9][0-9][0-9] [0-9][0-9]'
WHEN 'DE' THEN '[0-9][0-9][0-9][0-9][0-9]'
WHEN 'DK' THEN '[0-9][0-9][0-9][0-9]'
WHEN 'DZ' THEN '[0-9][0-9][0-9][0-9][0-9]'
WHEN 'ES' THEN '[0-9][0-9][0-9][0-9][0-9]'
WHEN 'FI' THEN '[0-9][0-9][0-9][0-9][0-9]'
WHEN 'FO' THEN '[0-9][0-9][0-9]'
WHEN 'FR' THEN '[0-9][0-9][0-9][0-9][0-9]'
WHEN 'GR' THEN '[0-9][0-9][0-9][0-9][0-9]'
WHEN 'HR' THEN '[0-9][0-9][0-9][0-9][0-9]'
WHEN 'HU' THEN '[0-9][0-9][0-9][0-9]'
WHEN 'ID' THEN '[0-9][0-9][0-9][0-9][0-9]'
WHEN 'IL' THEN '[0-9][0-9][0-9][0-9][0-9]'
WHEN 'IN' THEN '[0-9][0-9][0-9][0-9][0-9][0-9]'
WHEN 'IR' THEN '[0-9][0-9][0-9][0-9][0-9]'
WHEN 'IS' THEN '[0-9][0-9][0-9]'
WHEN 'IT' THEN '[0-9][0-9][0-9][0-9][0-9]'
WHEN 'JP' THEN '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
WHEN 'KR' THEN '[0-9][0-9][0-9]-[0-9][0-9][0-9]'
WHEN 'KW' THEN '[0-9][0-9][0-9][0-9][0-9]'
WHEN 'KZ' THEN '[0-9][0-9][0-9][0-9][0-9][0-9]'
WHEN 'LI' THEN '[0-9][0-9][0-9][0-9]'
WHEN 'LS' THEN '[0-9][0-9][0-9]'
WHEN 'LU' THEN '[0-9][0-9][0-9][0-9]'
WHEN 'MX' THEN '[0-9][0-9][0-9][0-9][0-9]'
WHEN 'MY' THEN '[0-9][0-9][0-9][0-9][0-9]'
WHEN 'NO' THEN '[0-9][0-9][0-9][0-9]'
WHEN 'NP' THEN '[0-9][0-9][0-9][0-9][0-9][0-9]'
WHEN 'NZ' THEN '[0-9][0-9][0-9][0-9]'
WHEN 'PH' THEN '[0-9][0-9][0-9][0-9]'
WHEN 'PL' THEN '[0-9][0-9]-[0-9][0-9][0-9]'
WHEN 'PT' THEN '[0-9][0-9][0-9][0-9]'
WHEN 'RO' THEN '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
WHEN 'RU' THEN '[0-9][0-9][0-9][0-9][0-9][0-9]'
WHEN 'SA' THEN '[0-9][0-9][0-9][0-9][0-9]'
WHEN 'SE' THEN '[0-9][0-9][0-9] [0-9][0-9]'
WHEN 'SG' THEN '[0-9][0-9][0-9][0-9][0-9][0-9]'
WHEN 'SI' THEN '[0-9][0-9][0-9][0-9]'
WHEN 'SK' THEN '[0-9][0-9][0-9] [0-9][0-9]'
WHEN 'TH' THEN '[0-9][0-9][0-9][0-9][0-9]'
WHEN 'TN' THEN '[0-9][0-9][0-9][0-9]'
WHEN 'TR' THEN '[0-9][0-9][0-9][0-9][0-9]'
WHEN 'TW' THEN '[0-9][0-9][0-9]'
WHEN 'UA' THEN '[0-9][0-9][0-9][0-9][0-9]'
WHEN 'US' THEN '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
WHEN 'VE' THEN '[0-9][0-9][0-9][0-9]'
WHEN 'VN' THEN '[0-9][0-9][0-9][0-9][0-9]'
WHEN 'YU' THEN '[0-9][0-9][0-9][0-9][0-9]'
WHEN 'ZA' THEN '[0-9][0-9][0-9][0-9]'
ELSE '%' END)
);
brgds
Philipp Post |
|
|
| Back to top |
|
|
|
| --CELKO--... |
| Posted: Sun Apr 12, 2009 6:50 pm |
|
|
|
Guest
|
Thanks! I will add it and give you credit. Not exactly fame and
glory but good for beer if you catch me at a conference. |
|
|
| Back to top |
|
|
|
| Ronnie... |
| Posted: Mon Apr 13, 2009 1:52 am |
|
|
|
Guest
|
"Philipp Post" <Post.Philipp at (no spam) googlemail.com> wrote in message
news:c1621447-bd5a-4565-9885-93e126dcb561 at (no spam) z19g2000vbz.googlegroups.com...
Quote: Various national postal codes
snip
WHEN 'CA' THEN '[0-9][A-Z][0-9] [A-Z][0-9][A-Z]'
snip
Philipp Post
Assuming the clause refers to Canada, it should be
WHEN 'CA' THEN '[A-Z][0-9][A-Z] [0-9][A-Z][0-9]'
For example 'T2P 1X1'.
Ronnie |
|
|
| Back to top |
|
|
|
| Gene Wirchenko... |
| Posted: Mon Apr 13, 2009 9:38 pm |
|
|
|
Guest
|
Philipp Post <Post.Philipp at (no spam) googlemail.com> wrote:
Quote: Various national postal codes
Just found a CHECK constraint I wrote as an exercise for myself some
years ago. It validates the postal codes for 58 countries from which I
had the pattern and which would fit into a LIKE predicate:
[snip]
Quote: WHEN 'CA' THEN '[0-9][A-Z][0-9] [A-Z][0-9][A-Z]'
This is wrong for Canada. The Postal Code format is
letter-digit-letter-space-digit-letter-digit, so the line should be:
WHEN 'CA' THEN '[A-Z][0-9][A-Z] [0-9][A-Z][0-9]'
[snip]
Sincerely,
Gene Wirchenko
Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices. |
|
|
| Back to top |
|
|
|
| Philipp Post... |
| Posted: Tue Apr 14, 2009 8:07 am |
|
|
|
Guest
|
Gene,
Quote: WHEN 'CA' THEN '[0-9][A-Z][0-9] [A-Z][0-9][A-Z]'
This is wrong for Canada. The Postal Code format is letter-digit-
letter-space-digit-letter-digit, so the line should be: WHEN 'CA'
THEN '[A-Z][0-9][A-Z] [0-9][A-Z][0-9]' <
Many thanks for pointing this out. This was indeed a mix-up. As this
version was already some years old and I did not look at these things
for quite a while, some entries were unfortunately outdated, others
were missing. I have already sent over an updated version to Joe by e-
mail in order to avoid messing up the list with it. If somebody is
interested, I can however put a copy in here as well.
We could do it for Canada even better: WHEN 'CA' THEN
'[ABCEGHJKLMNPRSTVXY][0-9][ABCEGHJKLMNPRSTVWXYZ] [0-9]
[ABCEGHJKLMNPRSTVWXYZ][0-9]'
Citation from http://en.wikipedia.org/wiki/Canadian_postal_code
"No postal code includes the letters D, F, I, O, Q, or U, as the OCR
equipment used in automated sorting could easily confuse them with
other letters and digits, especially when they are rendered as cursive
handwriting. The letters W and Z are used, but are not currently used
as the first letter."
A good listing of postal code formats can be found here:
http://en.wikipedia.org/wiki/List_of_postal_codes
brgds
Philipp Post |
|
|
| Back to top |
|
|
|
| Joćo Loureiro... |
| Posted: Sat May 16, 2009 4:36 pm |
|
|
|
Guest
|
On Apr 11, 4:17 pm, Philipp Post <Post.Phil... at (no spam) googlemail.com> wrote:
Quote: WHEN 'PT' THEN '[0-9][0-9][0-9][0-9]'
This has changed, and now it should be:
WHEN 'PT' THEN '[0-9][0-9][0-9][0-9]-[0-9][0-9][0-9]' |
|
|
| Back to top |
|
|
|
|
