Professional Web Applications Themes

Typical length of first name, last name, address field in the US? - MySQL

Are there standards, and what are they? Thanks....

  1. #1

    Default Typical length of first name, last name, address field in the US?

    Are there standards, and what are they? Thanks.
    Matchy Guest

  2. #2

    Default Re: Typical length of first name, last name, address field in the US?

    Matchy wrote:
     


    I have a good sized database which contains names:


    mysql> SELECT COUNT(*),AVG(LENGTH(firstname)) FROM cand WHERE 1;
    +----------+------------------------+
    | COUNT(*) | AVG(LENGTH(firstname)) |
    +----------+------------------------+
    | 439577 | 5.9249 |
    +----------+------------------------+
    1 row in set (0.24 sec)

    mysql> SELECT COUNT(*),AVG(LENGTH(lastname)) FROM cand WHERE 1;
    +----------+-----------------------+
    | COUNT(*) | AVG(LENGTH(lastname)) |
    +----------+-----------------------+
    | 439577 | 6.5452 |
    +----------+-----------------------+
    1 row in set (0.31 sec)


    Both much shorter than I expected.

    Addresses can be addressed (no pun intended) in many different ways.


    --
    Brian Wakem
    Email: http://homepage.ntlworld.com/b.wakem/myemail.png
    Brian Guest

  3. #3

    Default Re: Typical length of first name, last name, address field in the US?

    On Sun, 11 Feb 2007 19:45:30 +0000, Brian Wakem wrote: 
    >
    >
    > I have a good sized database which contains names:
    >
    >
    > mysql> SELECT COUNT(*),AVG(LENGTH(firstname)) FROM cand WHERE 1;
    > +----------+------------------------+
    >| COUNT(*) | AVG(LENGTH(firstname)) |
    > +----------+------------------------+
    >| 439577 | 5.9249 |
    > +----------+------------------------+
    > 1 row in set (0.24 sec)
    >
    > mysql> SELECT COUNT(*),AVG(LENGTH(lastname)) FROM cand WHERE 1;
    > +----------+-----------------------+
    >| COUNT(*) | AVG(LENGTH(lastname)) |
    > +----------+-----------------------+
    >| 439577 | 6.5452 |
    > +----------+-----------------------+
    > 1 row in set (0.31 sec)
    >
    >
    > Both much shorter than I expected.
    >
    > Addresses can be addressed (no pun intended) in many different ways.[/ref]

    You'll probably want MAX() instead of AVG()... Personally, I look at
    the price of disc, then throw even more into the column width. It's
    especially cheap with VARCHAR and the like.

    --
    Either way, it'll remind the clued that there's only one letter
    difference between 'turkey' and 'turnkey'.
    -- Mike Andrews
    Peter Guest

  4. #4

    Default Re: Typical length of first name, last name, address field in the US?

    On Sun, 11 Feb 2007 19:45:30 +0000, Brian Wakem <com> wrote:
     
    >
    >
    >I have a good sized database which contains names:
    >
    >
    >mysql> SELECT COUNT(*),AVG(LENGTH(firstname)) FROM cand WHERE 1;
    >+----------+------------------------+
    >| COUNT(*) | AVG(LENGTH(firstname)) |
    >+----------+------------------------+
    >| 439577 | 5.9249 |
    >+----------+------------------------+
    >1 row in set (0.24 sec)
    >
    >mysql> SELECT COUNT(*),AVG(LENGTH(lastname)) FROM cand WHERE 1;
    >+----------+-----------------------+
    >| COUNT(*) | AVG(LENGTH(lastname)) |
    >+----------+-----------------------+
    >| 439577 | 6.5452 |
    >+----------+-----------------------+
    >1 row in set (0.31 sec)[/ref]

    Good idea! I didn't think of it this way. Are these English names?
    That is, I don't know if you are familiar with names from Thailand
    but I think these are extra-ordinarily long. (Much longer than 6.5452
    characters in their last names).
     

    There are usually 2 address fields (address1 for apartment number for
    example and address 2 for something else) in addition to the city,
    state, zip, and country fields. How long should I design address1
    and address2 for? Thanks again.


    Matchy Guest

  5. #5

    Default Re: Typical length of first name, last name, address field in the US?

     

    I'm playing around with mysql now, but eventually this could end up
    importing 3-4 million names and about 25+ other fields at a time to
    extract reports (we don't like users extracting reports from the
    production box). Typically, it'll run in a Windows XP box using 1.6
    Ghz and about 512MB ram with lots of spare disk space. Thanks.

    Matchy Guest

  6. #6

    Default Re: Typical length of first name, last name, address field in the US?

    Matchy wrote:
     
    >>
    >>
    >>I have a good sized database which contains names:
    >>
    >>
    >>mysql> SELECT COUNT(*),AVG(LENGTH(firstname)) FROM cand WHERE 1;
    >>+----------+------------------------+
    >>| COUNT(*) | AVG(LENGTH(firstname)) |
    >>+----------+------------------------+
    >>| 439577 | 5.9249 |
    >>+----------+------------------------+
    >>1 row in set (0.24 sec)
    >>
    >>mysql> SELECT COUNT(*),AVG(LENGTH(lastname)) FROM cand WHERE 1;
    >>+----------+-----------------------+
    >>| COUNT(*) | AVG(LENGTH(lastname)) |
    >>+----------+-----------------------+
    >>| 439577 | 6.5452 |
    >>+----------+-----------------------+
    >>1 row in set (0.31 sec)[/ref]
    >
    > Good idea! I didn't think of it this way. Are these English names?
    > That is, I don't know if you are familiar with names from Thailand
    > but I think these are extra-ordinarily long. (Much longer than 6.5452
    > characters in their last names).[/ref]


    Sort off. They are all names of people that live in the UK, but of course
    there are many people living here that do not have English names.

     
    >
    > There are usually 2 address fields (address1 for apartment number for
    > example and address 2 for something else) in addition to the city,
    > state, zip, and country fields. How long should I design address1
    > and address2 for? Thanks again.[/ref]


    Not sure, we don't collect address1/2, only town, county and postcode.


    --
    Brian Wakem
    Email: http://homepage.ntlworld.com/b.wakem/myemail.png
    Brian Guest

  7. #7

    Default Re: Typical length of first name, last name, address field in the US?

    On Mon, 12 Feb 2007 08:30:03 -0800, Matchy wrote: 
    >
    > I'm playing around with mysql now, but eventually this could end up
    > importing 3-4 million names and about 25+ other fields at a time to
    > extract reports (we don't like users extracting reports from the
    > production box). Typically, it'll run in a Windows XP box using 1.6
    > Ghz and about 512MB ram with lots of spare disk space. Thanks.[/ref]

    Well, if you're importing, then you've got a source to count widths
    from, yes?

    --
    99. Any data file of crucial importance will be padded to 1.45Mb in size.
    --Peter Anspach's list of things to do as an Evil Overlord
    Peter Guest

  8. #8

    Default Re: Typical length of first name, last name, address field in the US?

    On Feb 11, 2:40 pm, Matchy <com> wrote: 

    If you're only concerned with US names, you can download the data
    (lastnames, firstnames and frequencies) from the website of the US
    Census Bureau

    bstjean Guest

  9. #9

    Default Re: Typical length of first name, last name, address field in the US?

    On Mon, 12 Feb 2007 13:49:23 -0600, "Peter H. Coffin"
    <com> wrote:
     
    >>
    >> I'm playing around with mysql now, but eventually this could end up
    >> importing 3-4 million names and about 25+ other fields at a time to
    >> extract reports (we don't like users extracting reports from the
    >> production box). Typically, it'll run in a Windows XP box using 1.6
    >> Ghz and about 512MB ram with lots of spare disk space. Thanks.[/ref]
    >
    >Well, if you're importing, then you've got a source to count widths
    >from, yes?[/ref]

    Shouldn't I design the field first before importing?

    By the way, if you don't know the answer please don't waste your time
    responding. Thanks.
    Aruthes Guest

  10. #10

    Default Re: Typical length of first name, last name, address field in the US?

    On Sun, 11 Feb 2007 11:40:41 -0800, Matchy
    <com> wrote:
     

    Let me paraphrase the question. Forget standards. How much space do
    you, yes YOU, allocate for address fields? Thanks.

    Aruthes Guest

  11. #11

    Default Re: Typical length of first name, last name, address field in the US?

    >>Well, if you're importing, then you've got a source to count widths 
    >
    >Shouldn't I design the field first before importing?[/ref]

    There's nothing wrong with a test import (with, say, the field
    length set to 255) into a temporary database before the real thing.
    Or you can count the length using the format it's already in without
    importing (if you have tools for that - sometimes awk is handy for
    this). Checking that your data conforms to what you think it does
    is always a good idea. If you need info about the data, you're
    probably interested in YOUR data, not what someone else has.
     

    This will really encourage people who DO know the answer to answer -- NOT!

    Gordon Guest

  12. #12

    Default Re: Typical length of first name, last name, address field in the US?

    On Mon, 12 Feb 2007 18:28:35 -0800, Aruthes wrote: 
    >>
    >>Well, if you're importing, then you've got a source to count widths
    >>from, yes?[/ref]
    >
    > Shouldn't I design the field first before importing?[/ref]

    Yes. You also shouldn't design the field before yzing the data
    you've got in front of you to import. Otherwise, it's a matter of
    picking a value that's larger than you think you're going to need.
    VARCHAR makes the penalty for using excessively wide fields pretty low.
    A 200-wide VARCHAR doesn't take up 200 characters when it's only got
    "John" in it.
     

    You haven't given enough data to give a much better answer than "make
    the field really big and use VARCHAR". If you had run an ysis of
    your input file and said "the longest first name I see is 16
    characters", then we could give you a really precise answer as to how
    wide to make the field. Average widths are useless.

    --
    39. If I absolutely must ride into battle, I will certainly not ride at the
    forefront of my Legions of Terror, nor will I seek out my opposite number
    among his army.
    --Peter Anspach's list of things to do as an Evil Overlord
    Peter Guest

  13. #13

    Default Re: Typical length of first name, last name, address field in the US?

    On 13 Feb, 12:52, org (Gordon Burditt) wrote: [/ref]

    >
    > There's nothing wrong with a test import (with, say, the field
    > length set to 255) into a temporary database before the real thing.
    > Or you can count the length using the format it's already in without
    > importing (if you have tools for that - sometimes awk is handy for
    > this). Checking that your data conforms to what you think it does
    > is always a good idea. If you need info about the data, you're
    > probably interested in YOUR data, not what someone else has.

    >
    > This will really encourage people who DO know the answer to answer -- NOT![/ref]

    I was discouraged too, but FWIW...

    contact_id int(11)
    title varchar(32)
    f_name varchar(128)
    m_name varchar(128)
    l_name varchar(128)
    suffix varchar(32)
    company varchar(64)
    department varchar(64)
    job_title varchar(64)
    b_street varchar(64)
    b_street2 varchar(64)
    b_street3 varchar(64)
    b_city varchar(64)
    b_state varchar(32)
    b_postcode varchar(8)
    b_country varchar(32)
    h_street varchar(64)
    h_street2 varchar(64)
    h_street3 varchar(64)
    h_city varchar(64)
    h_state varchar(32)
    h_postcode varchar(8)
    h_country varchar(32)
    o_street varchar(64)
    o_street2 varchar(64)
    o_street3 varchar(64)
    o_city varchar(64)
    o_state varchar(32)
    o_postcode varchar(8)
    o_country varchar(32)
    assistant_phone varchar(32)
    b_fax varchar(32)
    b_telephone varchar(32)
    b_telephone2 varchar(32)
    callback varchar(32)
    carphone varchar(32)
    company_phone varchar(32)
    h_fax varchar(32)
    h_telephone varchar(32)
    h_telephone2 varchar(32)
    isdn varchar(32)
    mobile varchar(32)
    o_fax varchar(32)
    o_telephone varchar(32)
    pager varchar(50)
    primary_phone varchar(50)
    radio_phone varchar(50)
    ttytdd varchar(50)
    telex varchar(50)
    account varchar(32)
    anniversary varchar(32)
    assistant_name varchar(255)
    billing varchar(32)
    birthday datetime
    b_pobox varchar(16)
    category varchar(64)
    children varchar(128)
    directory_server varchar(128)
    email1address varchar(128)
    email1address_type varchar(64)
    email1address_display_name varchar(64)
    email2address varchar(128)
    email2address_type varchar(64)
    email2address_display_name varchar(64)
    email3address varchar(128)
    email3address_type varchar(64)
    email3address_display_name varchar(64)
    gender char(1)
    government_id varchar(32)
    hobby varchar(64)
    h_pobox varchar(16)
    initials varchar(32)
    inetfreebusy varchar(255)
    keywords varchar(255)
    language varchar(32)
    location varchar(32)
    manager_name varchar(255)
    mileage decimal(16,3)
    notes varchar(255)
    office_location varchar(64)
    organization_id varchar(32)
    o_pobox varchar(16)
    priority tinyint(1)
    private tinyint(1)
    profession varchar(64)
    referred_by varchar(64)
    sensitivity tinyint(1)
    spouse varchar(255)
    user1 varchar(50)
    user2 varchar(50)
    user3 varchar(50)
    user4 varchar(50)
    homepage varchar(255)

    strawberry Guest

Similar Threads

  1. Query exists? Get size / length of field
    By johnmcgary in forum Coldfusion Database Access
    Replies: 2
    Last Post: July 21st, 06:03 AM
  2. Field Length Help
    By canswim in forum Coldfusion Database Access
    Replies: 2
    Last Post: June 8th, 02:18 PM
  3. Getting SQL Text Field Length
    By Jon in forum ASP Database
    Replies: 2
    Last Post: September 15th, 02:37 AM
  4. Replies: 3
    Last Post: May 13th, 12:15 AM
  5. Field length
    By Bebop & Rocksteady in forum FileMaker
    Replies: 9
    Last Post: July 13th, 01:31 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139