SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Jun 2008
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question replace or delete specific text in a database

    Hello, i am a total newbie in MySQL and I am struggling with a problem for several days, I tried to find the answer by myself but since I couldn't i need help. This is the situation:

    I have a column named fulltext it contains the fulltext of my articles. Somewhere in the text is located the following:
    Code:
    {mospagebreak title=something}
    Now I need to get rid of the
    Code:
     title=something
    And need to end up like this:
    Code:
    {mospagebreak}
    I tried like this:
    Code:
    UPDATE `jbg_content` SET `fulltext` = REPLACE( `fulltext` , '{mospagebreak title=', '{mospagebreak}' ) WHERE `fulltext` LIKE '%mospagebreak title=%'
    Unfortunately this replaces only
    Code:
    title=
    I couldn't figure out how to replace the varying text. Thanx for the help in advance!

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    don't hurt me rudy....
    Code:
    update jbg_content set `fulltext` = concat(left(`fulltext`, locate('{mospagebreak title=', `fulltext`) + 12), substring(`fulltext` from locate('}', `fulltext`, locate('{mospagebreak title=', `fulltext`)))) WHERE `fulltext` LIKE '%mospagebreak title=%'
    caution: before executing any query that modifies data, make sure you have a backup and that you know how to use it!
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by longneck View Post
    don't hurt me rudy....


    looks like your solution should work, but i did not examine it very closely

    why would you say that?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Jun 2008
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking

    Thank you very much longneck! You helped me a lot and the solution you provided worked perfetcly! You are great


Tags for this Thread

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
  •