#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    6
    Rep Power
    0

    Replace values between brackets


    Good day,

    I am busy trying to port over a BBforum to another system, and everything ported over well, except for the images. The images are in [img] brackets, while the new system use simple html. In the db, the img brackets are encoded with random magic quotes, for example [img:2it3n9tl]. with the magic quotes being random, I will have to find and replace every one of over 3000 posts. I am looking for a SQL query I can run that will replace the values in square brackets with something else. Is there a way one can use a SQL query that will replace everything between the brackets? I will really appreciate all the help.

    Best Regards,

    Jakes
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,179
    Rep Power
    4279
    Originally Posted by Jakes79
    In the db, the img brackets are encoded with random magic quotes, for example [img:2it3n9tl].
    what magic quotes? i don't see no magic quotes

    but to answer your question, no there is no sql that will replace everything between the brackets
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    90
    Rep Power
    3
    Hi,

    I'm also not sure what you mean by magic quotes, but if you are referring to the square brackets you can use find_in_set() or locate() to find the starting bracket and ending bracket and then change the substring in between.

    for that, this may help:
    https://launchpad.net/mysql-udf-regexp

    if you don't want to do that, you'd probably have to do something like:
    update img_nameline
    set img_nameline = concat( left(img_nameline, locate('[',img_nameline)), whatever, right(img_nameline,locate(']',img_nameline)) )
    Last edited by bobert123; January 30th, 2013 at 01:44 AM.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    6
    Rep Power
    0
    Hi all, thanks for replying. Ok, my apologies,
    refering to magic quotes, I am refering to the values following the [img tags. They do not show up when viewed on the forum, but only in the database. My understanding is that it is magic quotes, randomely coded for security reasons? My apologies if I am wrong.

    @bobert, I will test out your suggestions, and give feedback. Thanks a million!
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    6
    Rep Power
    0
    Ok, tested your method, with no luck. It simply places the existing text in brackets (unless I am doing something wrong)

    To start with: My table name is forum_post and the column name is text

    Here is how I've tried it:
    update ow_forum_post set text = concat( left(text, locate('[',text)), text, right(text,locate(']',text)) )
    I also did some research, and tried this:

    update ow_forum_post set columnName = (SELECT REPLACE(STUFF([ColumnName],1,text('[',[columnName]),''),']','')) where [columnName] like '%@%' select * Table"]update ow_forum_post set columnName = (SELECT REPLACE(STUFF([ColumnName],1,text('[',[columnName]),''),']','')) where [columnName] like '%@%' select * Table
    The thing is, I have very limited knowledge on SQL, and by trying and asking,I am learning. If someone can help me I will greatly appreciate it!
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    90
    Rep Power
    3
    Hi Jakes,
    what is it that you'd like there to be between the brackets?
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    6
    Rep Power
    0
    Well, just the word image. so it must be [image] instead of [img- and the various numbers]
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,179
    Rep Power
    4279
    STUFF is microsoft sql server, not mysql

    please confirm which database engine you're using
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    6
    Rep Power
    0
    I am using MySQL
    Thanks!
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    90
    Rep Power
    3
    Originally Posted by Jakes79
    Well, just the word image. so it must be [image] instead of [img- and the various numbers]
    in that case, try running your query with this:
    update ow_forum_post set text = concat( left(text, locate('[',text)), "image", right(text,locate(']',text)) )
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    6
    Rep Power
    0
    Hi there,
    Thanks for the reply. It work, but now I have the issue, when I have other square brackets too, it replaces everything between the first and last bracket.

    for example, I have [img:8787] imagepath1[/img]
    the result is {image} instead of {image} imagepath1{image}

    here is the query I've used:
    update ow_forum_post set text = concat( left(text, locate('[',text)), "{image}", right(text,locate(']',text)) )
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2012
    Posts
    90
    Rep Power
    3
    Originally Posted by Jakes79
    Hi there,
    Thanks for the reply. It work, but now I have the issue, when I have other square brackets too, it replaces everything between the first and last bracket.

    for example, I have [img:8787] imagepath1[/img]
    the result is {image} instead of {image} imagepath1{image}

    here is the query I've used:
    what would you prefer that this become?
    [img:8787] imagepath1[/img]

IMN logo majestic logo threadwatch logo seochat tools logo