Question 284717 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL – How to replace random 8 byte number to null

I have following style url in the text body in mysql DB

  • Type A: https://www.old_example1.com/12345678/myname
  • Type B: https://www.old_example2.com/87654321/yourname

I would like to change the url to below format

  • Type A: https://www.new_example1.com/myname
  • Type B: http://www.new_example2.com/yourname

For the domain part, it is a simple replacement query, so no big deal. But the 8 byte random number part after the domain is kinda tricky.

I’ve tried to count text bytes and use substring, but the ‘old_example1’ and ‘old_example2’ part is not always consistent bytes. Sometimes it is 1~2 bytes longer or shorter. But the occurence of ‘/’ is consistent, so I have tried to find a way to count ‘/’ and replace numbers values btwn 3rd and 4th ‘/’ to ” (empty value), but can’t figure out how to apply such conditions.

Tags:

2

Answers


  1. 0

    As Shadow mentioned in the comments that’s an 8 digit number, not 8-byte number.

    If your string has always the following format , you could use substring_index function

    https://www.old_example1.com/12345678/myname
    https://www.old_example2.com/87654321/yourname

    I am using .com/ as a word to get the latest part of the string including the replaced one. I’m guessing .com/ is repeated only once in your string, otherwise this answer will fail.

    SELECT SUBSTRING_INDEX(col,'.com/',-1) AS extracted
FROM test;

    Result

    extracted
12345678/myname
87654321/yourname

    Same thing as above to get the digits and to not include twice /

    select concat('/',SUBSTRING_INDEX(SUBSTRING_INDEX(col,'.com/',-1),'/',1)) as value_to_replace
FROM test;

    Result

    value_to_replace
/12345678
/87654321

    Finally

    select col,replace(col,concat('/',SUBSTRING_INDEX(SUBSTRING_INDEX(col,'.com/',-1),'/',1)),'') as new_col
from test;

    Result

    col new_col
https://www.old_example1.com/12345678/myname    https://www.old_example1.com/myname
https://www.old_example2.com/87654321/yourname  https://www.old_example2.com/yourname

    See example

    Login or Signup to reply.
  2. 0

    You could use REGEXP_REPLACE().

    Here’s a crude example:

    SET @string = 'Some text https://www.old_example1.com/12345678/myname blah blah blah https://www.old_example2.com/87654321/yourname some more blah';

SELECT
    @string AS old_string,
    REGEXP_REPLACE(
        @string,
        'https://www.old_example([1|2]).com(/[0-9]{8}/)',
        'https://www.new_example$1.com/'
    ) AS new_string;

    Outputs:

    old_string new_string
    Some text https://www.old_example1.com/12345678/myname blah blah blah https://www.old_example2.com/87654321/yourname some more blah Some text https://www.new_example1.com/myname blah blah blah https://www.new_example2.com/yourname some more blah

    Here’s a db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search