Prev: Get column value in delete trigger
Next: Importing nightly from another DB
From: --CELKO-- on 18 Jun 2010 11:17
I view CHAR(n) as an important constraint. For example:

zip_code CHAR(5) NOT NULL
CHECK (zip_code LIKE '[0-9][0-9][0-9][0-9][0-9]')
From: J.B. Moreno on 18 Jun 2010 11:43
In article
<79235e0c-8c31-4315-9d3e-a8160f2a55fd(a)c10g2000yqi.googlegroups.com>,
--CELKO-- <jcelko212(a)earthlink.net> wrote:

> I view CHAR(n) as an important constraint. For example:
>
> zip_code CHAR(5) NOT NULL
> CHECK (zip_code LIKE '[0-9][0-9][0-9][0-9][0-9]')

And when you decide to allow non-US addresses?

--
J.B. Moreno
From: Erland Sommarskog on 18 Jun 2010 17:28
J.B. Moreno (planB(a)newsreaders.com) writes:
> --CELKO-- <jcelko212(a)earthlink.net> wrote:
>> Can you give me a CHAR(32) first name, even in Greek, Polish or
>> Amerind?
>
> Could be a hoax, but if you google for the longest first name, it's
> quite a bit more than 32 characters.
>
> (Although CHAR anything other than 1 or 2 is questionable, make it
> varchar and be generous).

Well, the column declaration that Celko balked at is varchar(32), not
char(32).

In the database I work with firstname, extraname and lastname are
all varchar(36). There are probably situations where our users find
this too short. Why is left as an exercise to the reader.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: --CELKO-- on 19 Jun 2010 09:23
>> And when you decide to allow non-US addresses? <<

You missed the point of the example, which was that length is a
constraint. I can pull up thousands of examples of fixed-length
standards

ZIP and ZIP+4 codes are for the US and only the US. The generic term
is "postal code" and they are national (unfortunately). Most are now
numeric to avoid alphabet and language problems. I cover this in my
book on Standards, where I compare the US, Canada and the UK as
representives of the major types of code design in the UPU.
From: --CELKO-- on 19 Jun 2010 09:33
>> In the database I work with firstname, extraname and lastname are all varchar(36). There are probably situations where our users find this too short. Why is left as an exercise to the reader. <<

There is a history about why 35 shows up as an address length. In the
1950's the mailing labels used for magazines and junk mail were 3.5
inches wide and 5 lines long. Printers were 10 or 12 pitch (pitch =
characters per inch pitch; is the term "pitch" still used?). So the
postal standards were set for safety, and pretty much never changed.
All the abbreviation codes, special names (i.e. "Palos Verdes Estates"
=> "PVE"), etc were designed with that limit in mind.

I just read an article on problems with names this morning --
http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Get column value in delete trigger
Next: Importing nightly from another DB