MySql, how to replace a certain format of string from the coulmn

yyiws 0
Viewed 17

I have a MySQL table , a column contains records like this format: 'https://mlnyhdtvoz4t.i.optimole.com/Y0KrmFg-uFcVqWSa/w:auto/h:auto/q:82/hppt://......'.

I need to remove the first https part 'https://mlnyhdtvoz4t.i.optimole.com/Y0KrmFg-uFcVqWSa/w:auto/h:auto/q:82' from the record and only keep the rest of the string. The problem is position of Y0KrmFg-uFcVqWSa could be different values in each record. What is the appropriate regular expression I should use?

mysql
1 Answers
mysql> set @url = 'https://mlnyhdtvoz4t.i.optimole.com/Y0KrmFg-uFcVqWSa/w:auto/h:auto/q:82/hppt://......';
Query OK, 0 rows affected (0.00 sec)

mysql> select regexp_replace(@url, 'https://mlnyhdtvoz4t.i.optimole.com/[^/]+/w:auto/h:auto/q:82', '') as new_url;
+----------------+
| new_url        |
+----------------+
| /hppt://...... |
+----------------+

Alternative solution without using a regular expression:

mysql> select replace(@url, substring_index(@url, '/', 7), '') as new_url;
+----------------+
| new_url        |
+----------------+
| /hppt://...... |
+----------------+
Bill Karwin0
Related