Enhanced SQL Replace function - able to handle a variable part

Question

I would like to update thousands records in my SQL dB.

my post_content from my_table is like:

blablabla htt*://vimeo.com/12345678 blablabla

or

blablabla htt*://vimeo.com/12345678 blablabla htt*://vimeo.com/12345678 (blablabla)

and I would like to replace the

htt*://vimeo.com/12345678

by

<iframe src="htt*://player.vimeo.com/video/12345678?title=0&byline=0&portrait=0" width="400" height="300" frameborder="0" webkitAllowFullScreen allowFullScreen></iframe>

The problem is that 12345678 is different on each post...and I have to replace code before this number and add code after this number.

Replace code before the number is easy, but after, I don't know how to do it? How to say to the replace function: find htt*://vimeo/12345678 and point after the numbers and add "?title=0&a...< /iframe>.

Any idea?

What SQL version? mySQL? Microsoft SQL 2005, MS SQL 2008, Oracle? — Sparky
– Sparky, Commented Oct 26, 2011 at 10:14
Sounds like a bad idea. An insult to normalisation. Next step is probably putting VB-script into a database row. — wildplasser
– wildplasser, Commented Oct 26, 2011 at 10:16
The video embedding methods change every now and then. Why not just store the ID and leave the rendering to your PHP/ASP script? — Salman Arshad
– Salman Arshad, Commented Oct 26, 2011 at 10:35

Goran · Accepted Answer · 2011-10-26 20:20:36Z

1

If I understood it good, I suppose you can create your own function that takes some variables and inside it do some replacing etc.

CREATE FUNCTION MyReplace (Link nvarchar(4000), myVar nvarchar(500))
RETURNS nvarchar
DECLARE Result nvarchar(4000)
--create some replacing logic here, call REPLACE function etc: 
--Result=...
RETURN Result

Later, call this function in your query, passing two parameters - original link and your variable.

However, I'm not sure is it a good practice, as was mentioned above.

answered Oct 26, 2011 at 20:20

Goran

3935 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Enhanced SQL Replace function - able to handle a variable part

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related