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

mysql bulk rename, move a part of the string values from the beginning to the end of the string

example
I have the following string values

'[123] X'
'[123] Y'
'[123] Z'
...

I want to bulk change them to

'X [123]'
'Y [123]'
'Z [123]'
...

X, Y, Z are completely random not sequential
[123] is constant

I think the example above explains it all

Tags:

2

Answers


  1. 0

    I would use the REPLACE() function.

    REPLACE(str, find_string, replace_with)

    First remove the [123] from the string. Then add it again at the end of the string:

    UPDATE table_name SET column_name = CONCAT(REPLACE(column_name, '[123] ', ''), ' [123]');
    Login or Signup to reply.
  2. 0

    MySQL regex query:

    select st,
  concat(
      REGEXP_SUBSTR(st, '\w+$', 1), ' ', 
      REGEXP_SUBSTR(st, '^\[\d+\]', 1)
  ) as result
from T;

    Alternative:

    SELECT st,
  concat(
     SUBSTRING_INDEX(st,' ',-1), ' ',
     SUBSTRING_INDEX(st,' ',1)
  ) as result
FROM T

    Little bit complex:

    set @r1 = '^\[\d+\]';
set @r2 = '\w+$';

select st,
   REGEXP_REPLACE(
        REGEXP_REPLACE(st, @r1, REGEXP_SUBSTR(st, @r2, 1), 1),
                @r2, REGEXP_SUBSTR(st, @r1, 1)
   ) as result
from T;

    Sample Output: db<>fiddle

    enter image description here

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

Back To Top
Search