Jump to content

Code replacement query help needed, bbcode removal


indie

Recommended Posts

Any help appreciated.
 
So let's say in the db I have:
 

[url]https://www.youtube.com/watch?v=RandomCharactersHere[/url]

And I want it to be:
 

https://www.youtube.com/watch?v=RandomCharactersHere

I want to remove the URL code but only from youtube links so a replace all will not work unless it knows it is a YT link.
 
Would something like this work?
http://stackoverflow.com/a/35584060
 
So you could append [a-zA-Z0-9] to https://www.youtube.com/watch?v=
 
So code that would essentially say:
 

To trim tail:
Find [url]https://www.youtube.com/watch?v=[a-zA-Z0-9][/url] and remove [/url] after string.
To trim head:
Find [url]https//www.youtube.com/watch...(not sure if I need entire string in this one) remove [url] before string.

Would it need to know how many characters the video number was?
[a-zA-Z0-9]{8}

If so, I could go through however many it could possibly be to change them all.

 

Of course I would also need to do this with url and URL to cover all cases but I could just do it twice, and also the share code https://youtu.be/, but I can figure the share code out once I get this.

 

Thanks for any help!

Link to comment
Share on other sites

The purpose is one forum software treats this as an embed not a link (great). Moving to the new software they will be hyperlinks (not great) but if they are plain text with no bbcode they will be embedded after the import and the post task has run.

Link to comment
Share on other sites

Okay I can use EditPad Lite text editor which has regex. I just don't know what to do in search and replace.

 

([A-Za-z0-9\-\_]+)

 

I know something like this can be added to the end of the URL to find the video code, but then what? When I click to replace it just adds that regex code to the URL, and it won't highlight the bbcode tag after it for removal.

 

I just want to do this:

 

 

So let's say in the db I have:
 

[url]https://www.youtube.com/watch?v=RandomCharactersHere[/url]

And I want it to be:
 

https://www.youtube.com/watch?v=RandomCharactersHere

 

Thanks

Link to comment
Share on other sites


SELECT * FROM mytable;
+------------+--------------------------------------------------------------------------+
| mytable_id | link |
+------------+--------------------------------------------------------------------------+
| 1 | [url]https://www.youtube.com/watch?v=ABC123[/url] |
| 2 | [url]http://www.google.com/watch?q=SQL REPLACE[/url] |
| 3 | [url]http://dev.mysql.com/doc/refman/5.6/en/functions.html[/url] |
| 4 | [url]https://www.youtube.com/watch?v=XYZ789[/url] |
+------------+--------------------------------------------------------------------------+

UPDATE mytable
SET link = REPLACE(REPLACE(link, '[/url]', ''), '[url]', '')
WHERE INSTR(link, 'www.youtube.com');

SELECT * FROM mytable;
+------------+--------------------------------------------------------------------------+
| mytable_id | link |
+------------+--------------------------------------------------------------------------+
| 1 | https://www.youtube.com/watch?v=ABC123 |
| 2 | [url]http://www.google.com/watch?q=SQL REPLACE[/url] |
| 3 | [url]http://dev.mysql.com/doc/refman/5.6/en/functions.html[/url] |
| 4 | https://www.youtube.com/watch?v=XYZ789 |
+------------+--------------------------------------------------------------------------+
  • Like 2
Link to comment
Share on other sites

This is about forum posts with embedded BBCodes. As in:

foo [url]https://www.youtube.com/watch?v=ABC123[/url] bar [url]http://www.google.com/watch?q=SQL REPLACE[/url] baz

Your query would replace every single tag if only the post happens to contain a www.youtube.com somewhere (even outside of tags).

 

 

 

@indie:

/\[url\](https:\/\/www.youtube.com\/watch\?[^\[]+)\[\/url\]/i

Meta characters like square brackets or question marks must be escaped when they're supposed to be literal characters.

The actual URL is then available in the first capturing group and can be reinserted into the replace pattern.

  • Like 1
Link to comment
Share on other sites

  • 2 weeks later...
  • 2 months later...
SELECT * FROM mytable;
+------------+--------------------------------------------------------------------------+
| mytable_id | link                                                                     |
+------------+--------------------------------------------------------------------------+
|          1 | [url]https://www.youtube.com/watch?v=ABC123[/url]                        |
|          2 | [url]http://www.google.com/watch?q=SQL REPLACE[/url]                     |
|          3 | [url]http://dev.mysql.com/doc/refman/5.6/en/functions.html[/url]         |
|          4 | [url]https://www.youtube.com/watch?v=XYZ789[/url]                        |
+------------+--------------------------------------------------------------------------+

UPDATE mytable 
SET link = REPLACE(REPLACE(link, '[/url]', ''), '[url]', '')
WHERE INSTR(link, 'www.youtube.com');

SELECT * FROM mytable;
+------------+--------------------------------------------------------------------------+
| mytable_id | link                                                                     |
+------------+--------------------------------------------------------------------------+
|          1 | https://www.youtube.com/watch?v=ABC123                                   |
|          2 | [url]http://www.google.com/watch?q=SQL REPLACE[/url]                     |
|          3 | [url]http://dev.mysql.com/doc/refman/5.6/en/functions.html[/url]         |
|          4 | https://www.youtube.com/watch?v=XYZ789                                   |
+------------+--------------------------------------------------------------------------+

 

Thanks! I missed that one.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.