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

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

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:

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!

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:


I also did some research, and tried this:



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!

Hi Jakes,
what is it that you'd like there to be between the brackets?

Well, just the word image. so it must be [image] instead of [img- and the various numbers]

STUFF is microsoft sql server, not mysql

please confirm which database engine you're using

I am using MySQL
Thanks!

in that case, try running your query with this:

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]