Solved

Query Replace Help

Posted on 2011-03-10
7
233 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:N R
  • 4
  • 2
7 Comments
 
LVL 32

Expert Comment

by:ewangoya
ID: 35097401
update table
set fieldname = REPLACE(fieldname, '')

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

Author Comment

by:N R
ID: 35097418
can you put all that in code tags so I can see it,  the url tags disappeared haha.
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 35097426
Here
update table
set fieldname = REPLACE(fieldname, '')

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

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 32

Expert Comment

by:ewangoya
ID: 35097430
Not that, let me redo
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 35097446
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
 
LVL 11

Author Comment

by:N R
ID: 35097469
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
 
LVL 74

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
ID: 35097472
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

762 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

21 Experts available now in Live!

Get 1:1 Help Now