Prev: Get column value in delete trigger
Next: Importing nightly from another DB
From: --CELKO-- on 20 Jun 2010 14:55
>> First of all, the data type in question is VARCHAR(n), not CHAR(n). <<

COBOL and FORTRAN did not have varying length character data types;
you had to pad with blanks in your code. That is a reflection of punch
cards which had to be laid out in fixed fields. I am not sure, but C
mght be the first popular langauge with varying length character
data.
From: Tony Rogerson on 20 Jun 2010 15:25
> COBOL and FORTRAN did not have varying length character data types;
> you had to pad with blanks in your code. That is a reflection of punch
> cards which had to be laid out in fixed fields. I am not sure, but C
> mght be the first popular langauge with varying length character
> data.

PL/1 had VARYING length strings - I'm not that surprised you aren't aware
of that given your past posts with regard to practical experience!

--ROGGIE--


"--CELKO--" <jcelko212(a)earthlink.net> wrote in message
news:1e8379b1-e1ec-48a9-938a-2ea01762c1db(a)x21g2000yqa.googlegroups.com...
>>> First of all, the data type in question is VARCHAR(n), not CHAR(n). <<
>
> COBOL and FORTRAN did not have varying length character data types;
> you had to pad with blanks in your code. That is a reflection of punch
> cards which had to be laid out in fixed fields. I am not sure, but C
> mght be the first popular langauge with varying length character
> data.

From: Erland Sommarskog on 20 Jun 2010 16:38
--CELKO-- (jcelko212(a)earthlink.net) writes:
>>> First of all, the data type in question is VARCHAR(n), not CHAR(n). <<
>
> COBOL and FORTRAN did not have varying length character data types;

This is a newsgroup about SQL Server. Try comp.lang.fortran or
comp.lang.cobol if you want to discuss those langauges.




--
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 20 Jun 2010 17:03
>> Just how are you validating the check digit in the zip code? <<

The check digit for a ZIP code is the two-letter state code. That is
54 clause CASE expression of the form:

CHECK (CASE
WHEN zip_code NOT BETWEEN '00501' AND '99950'
THEN 'F'
WHEN state_code = 'TX'
AND zip_code BETWEEN '73301' AND '88595'
THEN 'T'
WHEN ..
ELSE 'F' END = 'T')

The digits and this constraint are a simple quick check you can
generate with a spreadsheet. I assume that out-of-range codes are not
used for special purposes; a lot of mailing systems use '00000' or
'99999' for missing data.

But, yes, a simple look-up table is better in the data base. My point
is that the regular expression for the validation is simple enough
that it can be done in a tiny piece of DDL. The ZIP code does not
require a complex regular expression, in some non-SQL language.

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

And that makes he chuckle :) Did you guys have the “Anti-Digit Dialing
League” in the UK (http://en.wikipedia.org/wiki/All-Number_Calling)?
They were all over the place in their day. The exchanges had names
which were drawn from the neighborhoods they served, so your exchange
name had status value.

The dummy exchange used in American movies was “KLondike-5” (555). Liz
Taylor made a movie entitled “BUtterfield 8”. There were rules about
where 0 and 1 could appear. Etc. It was a really strong part of the US
culture.

The various ADDLs were a source of filler material on evening
newscasts and local radio shows for about a year, then nobody thought
about them any more. We simply learned all-digit phone numbers so that
the phone system could grow. And with cell phones, the exchange digits
have no relationship to geography.

Actually, Postcodes have been supplemented by a newer system of five-
digit codes called Mailsort — but only for bulk mailings of 4,000 or
more letter-sized items. Bulk mailers who use the Mailsort system get
a discount, but bulk delivery by postcode do not.

I do not know if it will replace Postcodes. But if the UK is like the
US these days, the bulk mailers pay most of the postage in the country
(we get ~21 Kg per person per year). They drove the ZIP program over
here.

The Postcode system does not allow for easy workload re-distribution
over regional and local sorting facilities.

Oh, here is the UK postcode Regular Expression:

(GIR 0AA|[A-PR-UWYZ]([0-9]{1,2}|([A-HK-Y][0-9]|[A-HK-Y][0-9]([0-9]|
[ABEHMNPRV-Y]))|[0-9][A-HJKS-UW]) [0-9][ABD-HJLNP-UW-Z]{2})

A good bit harder than a grep for ZIP code and a lot easier to mess
up.

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

Geographical contiguous areas, moving north to south, then east to
west. But you miss the point. The purpose of any postal code is route
mail, allow easy machine processing and to allow for growth & change.
I might ask, why is the heart of New York City Area Code 212? Because
it gets your call there, without you having to worry about the
"MAnhatten" exchange.

I like abbreviation codes for that “human readable” property you
mention. But I know that they have limits and are not good for larger
domains with growth and changes. This why ISO country codes went from
two-letters to three-letters. This is why airport codes went from
three letters to four letters internationally. You might guess that
'FAA' is the Faranah Airport(Faranah, Guinea), but 'GUFH' is certain.

From: Tony Rogerson on 21 Jun 2010 02:13
> Two of Dr Codd's principles are that all columns in a table are
> scalars and that relational operations have closure. That means the
> result of a query is a table. What a lot of sophomore SQL programmers
> miss is that this table can be 1NF; they try for a 3NF or better
> table.

Codd does not specify Scalars at all (please do reference where he does) -
he requires values to be atomic that is all he says, scalars - that is a SQL
concept.

RVA's are atomic and allowed as an attribute, I'm deliberately not writing
RVA full hand so you have to go and research it - do let me know once you
have.....

> A function is deterministic. That means that sin(x)=y -- the sine of x
> is ALWAYS AND FOREVER y. No matter on what machine, etc. This is why
> IDENTITY is not a function (it is measurement of an insertion attempt
> event ), as you said in another posting. Basics, man, BASICS!
> Seriously, where did you learn your basic maths?

Again you try and steer the surrogate key debate by talking nonsense.

Of course the IDENTITY property is a function - I can use it on table
creation as well as in the logical and I mean logical default clause of the
table schema - you entirely miss the point about IDENTITY - you've been
wrong for over a decade on that just accept it and move on.

Research!! Ever done it? Or you just guessing again
(again...again...again...)???

> By working in IT for four decades for a wide range of industries (US
> government down to start-ups). On the SQL Standards Committee for ten
> years. By writing eight books on the SQL language. By writing over
> 900 articles on data and database issues. By teaching SQL and RDBMS in
> industry and colleges for decades. Etc.

A fitting analogy here is you are the Agatha Christie of the SQL space - you
write on the subject, while I'm the actual detective actually doing the
work.

I'm sure Agatha did research her books unlike yourself.

But stating you've been in IT for 4 decades means nothing, its how much time
you spend actually working - lets face it - in the past decade you've hardly
been in full time employment now - have you!

>
> Do not ask questions to which you do not wish to know the answer. My
> SQL resume is much better than yours :)

On the other hand - I've had the solid foundations, I get continuous
training from world leaders and academically I'm also active - currently
doing a masters topical for my job as consultant.

-- ROGGIE --

"--CELKO--" <jcelko212(a)earthlink.net> wrote in message
news:ced50ff4-7a4c-43d3-8d1d-3ab0f3957433(a)y11g2000yqm.googlegroups.com...
>>> Just how are you validating the check digit in the zip code? <<
>
> The check digit for a ZIP code is the two-letter state code. That is
> 54 clause CASE expression of the form:
>
> CHECK (CASE
> WHEN zip_code NOT BETWEEN '00501' AND '99950'
> THEN 'F'
> WHEN state_code = 'TX'
> AND zip_code BETWEEN '73301' AND '88595'
> THEN 'T'
> WHEN ..
> ELSE 'F' END = 'T')
>
> The digits and this constraint are a simple quick check you can
> generate with a spreadsheet. I assume that out-of-range codes are not
> used for special purposes; a lot of mailing systems use '00000' or
> '99999' for missing data.
>
> But, yes, a simple look-up table is better in the data base. My point
> is that the regular expression for the validation is simple enough
> that it can be done in a tiny piece of DDL. The ZIP code does not
> require a complex regular expression, in some non-SQL language.
>
>>> 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. <<
>
> And that makes he chuckle :) Did you guys have the �Anti-Digit Dialing
> League� in the UK (http://en.wikipedia.org/wiki/All-Number_Calling)?
> They were all over the place in their day. The exchanges had names
> which were drawn from the neighborhoods they served, so your exchange
> name had status value.
>
> The dummy exchange used in American movies was �KLondike-5� (555). Liz
> Taylor made a movie entitled �BUtterfield 8�. There were rules about
> where 0 and 1 could appear. Etc. It was a really strong part of the US
> culture.
>
> The various ADDLs were a source of filler material on evening
> newscasts and local radio shows for about a year, then nobody thought
> about them any more. We simply learned all-digit phone numbers so that
> the phone system could grow. And with cell phones, the exchange digits
> have no relationship to geography.
>
> Actually, Postcodes have been supplemented by a newer system of five-
> digit codes called Mailsort � but only for bulk mailings of 4,000 or
> more letter-sized items. Bulk mailers who use the Mailsort system get
> a discount, but bulk delivery by postcode do not.
>
> I do not know if it will replace Postcodes. But if the UK is like the
> US these days, the bulk mailers pay most of the postage in the country
> (we get ~21 Kg per person per year). They drove the ZIP program over
> here.
>
> The Postcode system does not allow for easy workload re-distribution
> over regional and local sorting facilities.
>
> Oh, here is the UK postcode Regular Expression:
>
> (GIR 0AA|[A-PR-UWYZ]([0-9]{1,2}|([A-HK-Y][0-9]|[A-HK-Y][0-9]([0-9]|
> [ABEHMNPRV-Y]))|[0-9][A-HJKS-UW]) [0-9][ABD-HJLNP-UW-Z]{2})
>
> A good bit harder than a grep for ZIP code and a lot easier to mess
> up.
>
>>> 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? <<
>
> Geographical contiguous areas, moving north to south, then east to
> west. But you miss the point. The purpose of any postal code is route
> mail, allow easy machine processing and to allow for growth & change.
> I might ask, why is the heart of New York City Area Code 212? Because
> it gets your call there, without you having to worry about the
> "MAnhatten" exchange.
>
> I like abbreviation codes for that �human readable� property you
> mention. But I know that they have limits and are not good for larger
> domains with growth and changes. This why ISO country codes went from
> two-letters to three-letters. This is why airport codes went from
> three letters to four letters internationally. You might guess that
> 'FAA' is the Faranah Airport(Faranah, Guinea), but 'GUFH' is certain.
>
First  |  Prev  | 
Pages: 1 2 3 4
Prev: Get column value in delete trigger
Next: Importing nightly from another DB