MySQL: Convert Field with StackOverflow-style Coded URLs to HTML

Question

We have a legacy database table full of StackOverflow-like coded URLs.

=====================================================================================
| TABLE_COMMENTS                                                                    |
=====================================================================================
| f_ID | f_comment                                                                 |
-------------------------------------------------------------------------------------
|   1  | To buy coffee [click here](https://google.com) or [here](https://bing.com) |
|   2  | Check [this out](https://stackoverflow.com)                                |
|   3  | [Cat Photos](https://google.com/images/?cats)                              |
=====================================================================================

I need to SELECT these as standard HTML so they look like the below table.

| f_comment                                                                                        |
----------------------------------------------------------------------------------------------------
| To buy coffee <a href="https://google.com">click here</a> or <a href="https://bing.com">here</a> |
| Check <a href="https://stackoverflow.com">this out</a>                                           |
| <a href="https://google.com/images/?cats">Cat Photos</a>                                         |
====================================================================================================

I have a method that will work if there is just one URL in the f_comment field, but I can't work out how to make it work for, like row 1 where there are two URLs.

SELECT 
    CONCAT(
        substring_index(substring_index(f_comment, '[', 1),')', 1),
        "<a href=", 
        substring_index(substring_index(f_comment, '(', -1),')', 1),
        ">",
        substring_index(substring_index(f_comment, '[', -1),']', 1),
        "</a>"
    )
    AS f_replacementtext
FROM TABLE_COMMENTS;

SQL Fiddle

Results of my incomplete query, where it misses the first URL of f_ID 1

 ==========================================================
| f_comment                                                |
 ----------------------------------------------------------
| To buy coffee <a href="https://bing.com">here</a>        |
| Check <a href="https://stackoverflow.com">this out</a>   |
| <a href="https://google.com/images/?cats">Cat Photos</a> |
 ==========================================================

Select regexp_replace(f_comment, '\[([^]]+)\]$([^$]+)\)', '<a href="$2">$1</a>'). Default is to replace all occurrences. Hard to parse the regular expression but it finds square brackets — Chris Maurer, Commented Aug 17, 2021 at 4:07
...followed by round brackets and rearranges what's inside them. — Chris Maurer, Commented Aug 17, 2021 at 4:13
What version of MySQL? (Some things are better done in application code than in SQL.) — Rick James, Commented Aug 17, 2021 at 21:58

Rick James · Accepted Answer · 2021-08-23 02:44:13Z

set @col =
'To buy coffee <a src=https://google.com>click here</a> or <a src=https://bing.com>here</a>';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT REGEXP_REPLACE( @col, '<a src=(http.*?)>(.*?)</a>', '[$2]($1)');
+----------------------------------------------------------------------------+
| REGEXP_REPLACE( @col, '<a src=(http.*?)>(.*?)</a>', '[$2]($1)')            |
+----------------------------------------------------------------------------+
| To buy coffee [click here](https://google.com) or [here](https://bing.com) |
+----------------------------------------------------------------------------+

Going the other way...

SET @col =
'To buy coffee [click here](https://google.com) or [here](https://bing.com)';
SELECT
REGEXP_REPLACE( @col, '\\[(.*?)\\][(](http.*?)[)]',
                      '<a src=\$2>\$1</a>' ); 

+------------------------------------------------------------------------------------------------------+
| REGEXP_REPLACE( @col, '\\[(.*?)\\][(](http.*?)[)]',
                          '<a src=\$2>\$1</a>' ) |
+------------------------------------------------------------------------------------------------------+
| To buy coffee <a src=https://google.com>click here</a> or <a src=https://bing.com>here</a>           |
+------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

This seems to be the opposite of what I'm looking to do, but is definitely the right idea. I have the StackOverflow-style comment code, but need to convert to HTML. I'll see if I can play with this to make it do the opposite, but I think my biggest problem will be when I try to move the HREF text after the URL. — Beems, Commented Aug 22, 2021 at 17:33
@Beems - I started over, flipped them, showed how to use $1 and tricky characters. — Rick James, Commented Aug 23, 2021 at 2:44
I appreciate the help - I 'kind of' see how the variables work, despite regex being a big bugaboo for me. This works great. — Beems, Commented Aug 25, 2021 at 0:26

Collectives™ on Stack Overflow

MySQL: Convert Field with StackOverflow-style Coded URLs to HTML

1 Answer 1

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Related