SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    Quake 1 Addict CreedFeed's Avatar
    Join Date
    Feb 2002
    Location
    Milwaukee, WI
    Posts
    296
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Incrementing A Field - [TEXT][NUMBER]

    Lets say I have a database field that stores a string (varchar 25 for example). The format of the data in this field is [TEXT][NUMBER] so for example the data there might be:

    CreedFeed1
    CreedFeed2
    ...
    CreedFeed9
    CreedFeed10
    CreedFeed11
    ...

    What I want to do is get the highest number used, incremented it by one and then insert a new record... so in the example above, I'd want to pull out CreedFeed11, increment 11 to 12, and then insert CreedFeed12.

    Is there a way to query the table to pull that highest used number? Assume in this case that there's no correlation between the highest number used in the string and an auto-incremented id field (the highest number may not always be the most recently added data in the table).

    I first thought I could use MAX() and a regular expression but once the numbers went past 9 it does not work:

    Code MySQL:
    SELECT MAX(field) AS maxfield FROM table WHERE maxfield REGEXP '^CreedFeed[0-9]*'

    Ideas?
    -- Steve Caponetto
    Quake 1 Resurrection :: CreedFeed

  2. #2
    Quake 1 Addict CreedFeed's Avatar
    Join Date
    Feb 2002
    Location
    Milwaukee, WI
    Posts
    296
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well I came up with the following solution, but I'm not sure if it's the most efficient or not. Anyone?

    Code MySQL:
    SELECT MAX( CAST( REPLACE(field, 'CreedFeed', '') AS UNSIGNED) ) as maxnum
    FROM table
    WHERE field LIKE 'CreedFeed%'
    -- Steve Caponetto
    Quake 1 Resurrection :: CreedFeed

  3. #3
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,061
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    I would use SUBSTRING(field, 10) instead of REPLACE(FIELD, 'CreedFeed', ''), but the rest looks good (or at least probably as good as it gets with this setup).
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i would use an auto_increment number, and append the string prefix when retrieving rows

    ~so~ much simpler...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •