Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2009
    Posts
    23

    Unanswered: automatically generate value of the property ID in database

    I am trying to create a table which have "id" as its primary key. the property "id" is a string with fixed length and consists of characters and numbers, e.g. ABC45125. very easy to automatically generate its value in database if it is an integer but I have no idea when it is a string. any idea about it? I will appreciate.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you can't use the inbuilt autonumber column.....

    I suppose it depends on what the alpha component refers to, how that is made up.

    it also depends on whether the numbers have to be contiguous.. nothing upsets an accountant more that seeing a list of numbers which are non contiguous.

    its actually fairly simple to do, once you have sorted out your true requirements.

    I've used a composite alphanumeric on numerous occasions (often the alpha component adds more information tot he numeric (eg where an acconting system needed to identify the period as part of the invoice number). they wanted a quick and simple method of identifying what year and month a transaction was raised and they wanted there invoice numbers masked so that their customers and competition couldn't easily identify how much trade they were doing.


    so over to you...
    what does the ABC but represent, how does that change?
    do you need contiguous numbers?
    what causes the number to change in what sequence?

    bear in mind that to most computers the letter A is 65, a is 97
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this is the Database Concepts & Design forum

    the concept you've described is pretty clear, but i'm afraid that if you want a working solution, you will have to tell us which database system you're using -- then we can move this thread to the appropriate forum and get to work...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2009
    Posts
    23
    I use mysql

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thread moved to mysql

    if you want automatically assigned numbers, you must use an INTEGER AUTO_INCREMENT column

    you can "fake" the alphabetic/numeric codes with concatenation whenever you need to retrieve the code from the table

    SELECT CONCAT('ABC',RIGHT(CONCAT('0000',id),5) AS my_id ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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