String replace a string pattern?

Posted on 2007-10-11
Last Modified: 2013-12-13

OK I have a list of ID's stored in a field that is formatted as follows:
[id(date)],[id(date)],[id(date)], and so on...

So if data was entered into the field is would look something like:
[22(2007-09-30 00:00:00)],[500(2007-09-30 12:00:00)],[1(2007-09-30 05:00:00)], and so on...

What I want to be able to do is to remove an ID from the sequence through one query. The only data I will know for finding and removing the ID is the ID value but not the date.

I have managed to do this, but my method is not as quick as I'd like. So how do I redo my example below so that is will work with any ID from 1 to 1000000+. The only thing that changes the length of each ID in the sequence is the ID as the length of the date is constant.

Working example for ID = 1:
UPDATE table SET id_list=REPLACE(id_list, SUBSTRING(id_list, LOCATE('[1(', id_list), 25), '')

To make it work for any ID I need to replace the '25' in the example above so that it is correct for any ID.

There must be a better way to do this? Any ideas?
Question by:nacker2000
    LVL 17

    Expert Comment

    Post your code so I may modify.
    LVL 17

    Expert Comment

    Actually, using only MySQL - no... :( IN PHP, you can use the `preg_replace` function (or `ereg_replace`).

    That's why it's not a good idea to store multiple data items in a single field :(
    LVL 17

    Expert Comment

    can you post a few lines from your database eg.

    ID           |    Date           |     stuff    
    1            |   01-01-2007 |      The content stuff
    2           | 01-02-2007    |      Stuff Rocks
    LVL 5

    Author Comment


    I know storing multiple data items in a single field is not always the best idea, however there will never be more than 3 or 4 in the field so its not too bad, plus having a table to store each id and date for all the ids could possibly end up with thousands and thousands of records so for what I am using the data for this is the simplest way and potentially quickest way.

    I can get the length using php, I was just hoping someone could suggest a beter way? As for posting my code and lines from my databse, I don't think there is really any need as the structure is explained above.

    LVL 17

    Accepted Solution

    As I said, there's no (better) way to do it using MySQL only, as REPLACE() is the only function in there and is not very powerful. It might be possible to use the LOCATE() & SUBSTRING() functions, but it's definitely not easier than doing it in PHP... :(
    LVL 17

    Assisted Solution

    They are right, you should never mix data in sql columns, you loose all the powerful feature of sql.

    However, doing it in PHP is infinitely faster then in SQL.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
    Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit ( and similar technologies have enjoyed wide adoption, making it possib…
    Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now