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

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?

CodePudding user response：

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://...... |
 ----------------