Query Replace Help

I'm trying to do a replace on a filed that has many different options.

filedname = post, table = posts

[url=http://domain.com/blah.flv]blah video[/url]
[url=http://domain.com/video/name.mp4]name of him[/url]
[url=http://domain.com/video/2001/funny.flv]funny clip[/url]

Open in new window


I want to update these to make them look like:

[media]http://domain.com/blah.flv[/media]
[media]http://domain.com/video/name.mp4[/media]
[media]http://domain.com/video/2001/funny.flv[/media]

Open in new window

LVL 12
Nathan RileyFounderAsked:
Who is Participating?
 
käµfm³d 👽Connect With a Mentor Commented:
How about this?
update posts set post = '[media]' + SUBSTRING(post, charindex('=', post) + 1, charindex(']', post, 2) - charindex('=', post) - 1) + '[/media]'

Open in new window

0
 
Ephraim WangoyaCommented:
update table
set fieldname = REPLACE(fieldname, '')

update table
set fieldname =REPLACE(fieldname, '
', '[/media]')
0
 
Nathan RileyFounderAuthor Commented:
can you put all that in code tags so I can see it,  the url tags disappeared haha.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Ephraim WangoyaCommented:
Here
update table
set fieldname = REPLACE(fieldname, '')

update table
set fieldname =REPLACE(fieldname, '', '[/media]')

Open in new window

0
 
Ephraim WangoyaCommented:
Not that, let me redo
0
 
Ephraim WangoyaCommented:
Here is the correct one
update table
set fieldname = REPLACE(fieldname, '[url=', '[media]')

update table
set fieldname =REPLACE(fieldname, '[/url]', '[/media]')

Open in new window

0
 
Nathan RileyFounderAuthor Commented:
Well, that still doesn't get me there that will make the result set be:
[media]http://domain.com/blah.flv]blah video[/media]
[media]http://domain.com/video/name.mp4]name of him[/media]
[media]http://domain.com/video/2001/funny.flv]funny clip[/media]

Open in new window


but what I need is:
[media]http://domain.com/blah.flv[/media]
[media]http://domain.com/video/name.mp4[/media]
[media]http://domain.com/video/2001/funny.flv[/media]

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.