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.

A database designer also has to see the protocols, because generically you
need to
capture enough data to do the transaction.

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.

(david)



"--CELKO--" <jcelko212 (AT) earthlink (DOT) net> wrote

I have SWIFT, IBAN and some of the stuff we use in the States.
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.

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.

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

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.

"Philipp Post" <Post.Philipp (AT) googlemail (DOT) com> wrote

WHEN 'CA' THEN '[A-Z][0-9][A-Z] [0-9][A-Z][0-9]'

For example 'T2P 1X1'.

Ronnie

Philipp Post <Post.Philipp (AT) googlemail (DOT) com> wrote:

[snip]

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.

Gene,

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

On Tue, 24 Mar 2009 17:28:11 -0700 (PDT), --CELKO--
<jcelko212 (AT) earthlink (DOT) net> wrote in
<67d72be0-1362-4bc2-add7-3015da77cd52 (AT) e18g2000yqo (DOT) googlegroups.com>:

NAICS codes. You need these if you're doing anything with government
contracting or insurance, among others:
<http://www.census.gov/eos/www/naics/>.

Someone mentioned routing numbers for U.S. banks. I found a nice list
provided by the federal reserve here:
<https://www.fededirectory.frb.org/download.cfm>.

--
Charles Calvert | Web-site Design/Development
Celtic Wolf, Inc. | Software Design/Development
http://www.celticwolf.com/ | Data Conversion
(703) 580-0210 | Project Management

On Apr 11, 4:17*pm, Philipp Post <Post.Phil... (AT) googlemail (DOT) com> wrote:
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]'