phpmyadmin Question posted in
The official documentation can be found here.

SQL Data Update Query About – Phpmyadmin

I have a table named testlink and it has url and newtarget columns.

I would like to take the string expressions https://domain1.com/ here in the url column and change all the data in the newtarget column to https://domain1.com/search/?q= pulled string expression.

So briefly;

url columns from https://domain1.com/topic1
will be changed to https://domain1.com/search/?q=topic1 in the newtarget column

There are about 6 thousand different topics (lines) available.

Database: Mysql / Phpmyadmin.

Tags:

3

Answers


  1. 0

    use REPLACE

    UPDATE testlink 
SET newtarget = REPLACE(url,'https://domain1.com/','https://domain1.com/search/?q=')

    MySQL REPLACE() replaces all the occurrences of a substring within a
    string.

    REPLACE(str, find_string, replace_with)

    Login or Signup to reply.
  2. 0

    If you want to conditionally change the value, you can use string manipulations:

    update t
    set url = concat(left(url, length(url) - length(substring_index(url, '/', -1))), 'q=', substring_index(url, '/', -1))
    where url like 'https://domain1.com/%';

    This uses substring_index() to get the last part of the string (after the last /). It uses left() to get the first part (based on the length of the last part) and then concatenates the values you want.

    Of course, test this logic using a SELECT before implementing an UPDATE.

    Login or Signup to reply.
  3. 0

    If you’re using MySQL 8, then you’d be able to do that with REGEXP_REPLACE.

    For your example, this should work :

    SELECT REGEXP_REPLACE('https://domain1.com/topic1','(https://domain1.com/)(.+)','$1search/?q=$2')
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search