SQL / Change link with value-CodePudding

I want to update all links in my MySQL table from

"http://cdn.steamcommunity.com/economy/image/ABCDEFG/"

"https://steamcommunity-a.akamaihd.net/economy/image/ABCDEFG/".

What is an UPDATE SQL command for this with remaining the ABCDEFG value?

Thank you in advance

CodePudding user response：

It kind of depends on database you use; they all have similar capabilities in string manipulations, but flavours might (and do) differ.

For example, in Oracle, you could replace the first part of the string with another (result column) or concatenate it with the rest of the string (result_2).

SQL> select col,
  2         --
  3         replace(col, 'http://cdn.steamcommunity.com',
  4                      'https://steamcommunity-a.akamaihd.net'
  5                ) result,
  6         --
  7         'https://steamcommunity-a.akamaihd.net' ||
  8          substr(col, instr(col, '/', 1, 3)) result_2
  9  from test;

COL
------------------------------------------------------------
RESULT
------------------------------------------------------------
RESULT_2
------------------------------------------------------------
http://cdn.steamcommunity.com/economy/image/ABCDEFG/
https://steamcommunity-a.akamaihd.net/economy/image/ABCDEFG/
https://steamcommunity-a.akamaihd.net/economy/image/ABCDEFG/


SQL>

As you want to update the table, then ... use update, e.g.

SQL> update test set
  2    col = replace(col, 'http://cdn.steamcommunity.com',
  3                       'https://steamcommunity-a.akamaihd.net'
  4                 );

1 row updated.

SQL> select * from test;

COL
------------------------------------------------------------
https://steamcommunity-a.akamaihd.net/economy/image/ABCDEFG/

SQL>