[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Query Replace Help

Posted on 2011-03-10
7
Medium Priority
?
251 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:Nathan Riley
  • 4
  • 2
7 Comments
 
LVL 32

Expert Comment

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

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

Author Comment

by:Nathan Riley
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:Ephraim Wangoya
ID: 35097426
Here
update table
set fieldname = REPLACE(fieldname, '')

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

Open in new window

0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 32

Expert Comment

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

Expert Comment

by:Ephraim Wangoya
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 12

Author Comment

by:Nathan Riley
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 75

Accepted Solution

by:
käµfm³d   👽 earned 2000 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

873 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