Code replacement query help needed, bbcode removal

[indie]

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!

[Jacques1]

And your doing this why, exactly?

 

MySQL does't have a search-and-replace function based on regular expressions. But PHP does.

[indie]

And your doing this why, exactly?

 

I need to remove the url wrapper code.

[Jacques1]

Yes, I understand that. But for what purpose are you removing the BBCodes? Why should users not be able to click on Youtube links?

[indie]

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.

[indie]

MySQL does't have a search-and-replace function based on regular expressions. But PHP does.

 

 

Do you mean PHP can be used to change it in MYSQL? Not familiar with coding. Found this not sure it will help me: http://stackoverflow.com/questions/24261555/removing-bbcode-url-tag-from-string

[Jacques1]

If you don't like coding:

• Create a backup in case something goes wrong.
• Export the data as a CSV file (SELECT ... INTO OUTFILE ...)
• Do the searching and replacing with a good editor like Notepad++,
• Reimport the data (LOAD DATA INFILE ...).

[indie]

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

[Barand]

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                                   |

+------------+--------------------------------------------------------------------------+

[Jacques1]

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.

[indie]

Thanks I'll try those.

 

1st one is a mysql query, second is a regex search/replace?

[Jacques1]

1st one is a mysql query, second is a regex search/replace?

 

Yes, but I think the query is based on a misunderstanding. It assumes that the links are stored as individual VARCHARs (rather than being embedded in posts).

[indie]

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.