Prev: Get column value in delete trigger
Next: Importing nightly from another DB
From: Erland Sommarskog on 19 Jun 2010 09:59
--CELKO-- (jcelko212(a)earthlink.net) writes:
>>> 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.

What has this to do with anything?


--
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 20:14
>> What has this to do with anything? <<

I thought that the topic was "Character length as a constraint" at
this point. I also see that you asked "Why is left as an exercise to
the reader" so I answered.

This was an attempt to explain **why** YOUR example used CHAR(36) to
you . that length completely fills a display column on a screen,
assumng the data used the CHAR(35) convention I described. Display
layouts in COBOL were CHAR(36) to allow a space.

Actually, I can get an article out of this.
From: --CELKO-- on 19 Jun 2010 21:19
>> Here in the UK - you know, outside the centre of the universe i.e. Texas, we don't have zip codes and the closest cannot be constrained into 5 fixed length characters. <<

That's nice. But that has nothing to do with the fact that ZIP code is
a US postal code which is made up of five digits (and the ZIP+4 has a
format of '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'). A
universal character set, not language depend.

The US system is now the model for the world. It uses all digits, has
a fixed length, can be computerized, has a "check digit" (i.e. the 2-
letter state code) and maps to a physIcal situation (i.e.gross
politicial geography) that can be verified with a $10 GPS or a cell
phone.

The Canadian system is a middle level system. It has a fixed-length
pattern of numerics and restricted alphas. Great for Western computer
systems that have LATIN-1, but not really universal.

The flaw is that many Canadian Postal codes go to existing physical
address locations (remove a building or expand a development and your
postal code is invalid).

The UK system is the worst in the developed world. Governnent owned
and sold instead of free public data. Variable length. Has Alphas.
Mixed alpha-numerics. A pattern based on the layout of cities 100+
year old. Etc. I have a few thousand words about this in my book.

Hey, Tony, would you post a LIKE predicate to validate a Royal Mail
Postal Code? Not verify, just validate. I Double Dog Dare you! Hey,
if you are going to post childish insults, then I can post childish
dares.

It is insanely, so yiu wnat ot have a SIMILAR TO in Standard SQL.l

I already did it with this for ZIP codes:

zip_code CHAR(5) NOT NULL
CHECK (zip_code LIKE '[0-9][0-9][0-9][0-9][0-9]')

to be complete I need another CHECK() to exclude ZIP codes that are
not valid, like '00000' and '99999' , but forget that extra safety.

When I did my book on Standards, I picked those three as examples of
encoding design for those reasons. They are English language nations
with representative postal encoding schemes.



From: Tony Rogerson on 20 Jun 2010 02:58
> Hey, Tony, would you post a LIKE predicate to validate a Royal Mail
> Postal Code? Not verify, just validate. I Double Dog Dare you! Hey,
> if you are going to post childish insults, then I can post childish
> dares.

Because I wouldn't, like every other professional developer I'd use a
regular expression and to implement that in SQL Server use CLR because we
don't have the feature built into the language.

We've had this conversation before - if you want me to dig out the post
where you get post the usual impractical unworkable garbage I'll be more
than happy.

> zip_code CHAR(5) NOT NULL
> CHECK (zip_code LIKE '[0-9][0-9][0-9][0-9][0-9]')
>

Just how are you validating the check digit in the zip code? You aren't, you
are simply checking that each character in the zip code numeric which is
wrong anyway because you may allow zip codes that don't exist - a table is a
better method for this because of the size of the domain zip codes.

> The UK system is the worst in the developed world. Governnent owned
> and sold instead of free public data. Variable length. Has Alphas.
> Mixed alpha-numerics. A pattern based on the layout of cities 100+
> year old. Etc. I have a few thousand words about this in my book.

This made me chuckle, the UK system is based on the natural data, for
instance AL5 you know that AL is St Albans so that AL5 is near St Albans -
its a neat and practical method of segmenting data.

Compare that to a zip code which is nothing more than an incremental
numbering scheme where the block of 5 is chopped up into blocks, the first
digit depicts region - why is Indiana 4? How does the value 4 related to the
polygon that is Indiana? In the UK system the first two characters indicate
the polygon "region" so is more descriptive - why don't I see your rant
about using descriptive data for keys rather than incremental numbers?
Should we talk about DUNS now and how it does not relate back to the
original issuing authority and is just an incremental number without check
digits?

--ROGGIE--



"--CELKO--" <jcelko212(a)earthlink.net> wrote in message
news:fa11cc6c-a9fd-408c-b576-3a36189a772e(a)c33g2000yqm.googlegroups.com...
>>> Here in the UK - you know, outside the centre of the universe i.e.
>>> Texas, we don't have zip codes and the closest cannot be constrained
>>> into 5 fixed length characters. <<
>
> That's nice. But that has nothing to do with the fact that ZIP code is
> a US postal code which is made up of five digits (and the ZIP+4 has a
> format of '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'). A
> universal character set, not language depend.
>
> The US system is now the model for the world. It uses all digits, has
> a fixed length, can be computerized, has a "check digit" (i.e. the 2-
> letter state code) and maps to a physIcal situation (i.e.gross
> politicial geography) that can be verified with a $10 GPS or a cell
> phone.
>
> The Canadian system is a middle level system. It has a fixed-length
> pattern of numerics and restricted alphas. Great for Western computer
> systems that have LATIN-1, but not really universal.
>
> The flaw is that many Canadian Postal codes go to existing physical
> address locations (remove a building or expand a development and your
> postal code is invalid).
>
> The UK system is the worst in the developed world. Governnent owned
> and sold instead of free public data. Variable length. Has Alphas.
> Mixed alpha-numerics. A pattern based on the layout of cities 100+
> year old. Etc. I have a few thousand words about this in my book.
>
> Hey, Tony, would you post a LIKE predicate to validate a Royal Mail
> Postal Code? Not verify, just validate. I Double Dog Dare you! Hey,
> if you are going to post childish insults, then I can post childish
> dares.
>
> It is insanely, so yiu wnat ot have a SIMILAR TO in Standard SQL.l
>
> I already did it with this for ZIP codes:
>
> zip_code CHAR(5) NOT NULL
> CHECK (zip_code LIKE '[0-9][0-9][0-9][0-9][0-9]')
>
> to be complete I need another CHECK() to exclude ZIP codes that are
> not valid, like '00000' and '99999' , but forget that extra safety.
>
> When I did my book on Standards, I picked those three as examples of
> encoding design for those reasons. They are English language nations
> with representative postal encoding schemes.
>
>
>
From: Erland Sommarskog on 20 Jun 2010 05:48
--CELKO-- (jcelko212(a)earthlink.net) writes:
>>> What has this to do with anything? <<
>
> I thought that the topic was "Character length as a constraint" at
> this point. I also see that you asked "Why is left as an exercise to
> the reader" so I answered.
>
> This was an attempt to explain **why** YOUR example used CHAR(36) to
> you . that length completely fills a display column on a screen,
> assumng the data used the CHAR(35) convention I described. Display
> layouts in COBOL were CHAR(36) to allow a space.

First of all, the data type in question is varchar, not char.

Second, whatever conventions that may be used in the US, is of little
interest to the system I work with. Nor what conventions that are used
with addresses.

Finally, the answer to the little exercise is that while the first name
of natural persons rarely is as many 36 characters, it can still be too
short for juridical persons. No, they are not likely to appear in a system
with spouses, but in the business for this system, they are very likely.

--
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

First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Get column value in delete trigger
Next: Importing nightly from another DB