Changing the format of the entire column in a mysql table-CodePudding

I have a column in a mysql table like this:

Testtag
SG_QRA_SGBAKE_0012
SG_QRA_SGBAKE_0013
SG_QRA_SGHAST_0005
SG_QRA_SGHAST_0006
SG_QRA_SGHAST_0007

and I want to change the entire column to like this:

Testtag
SGBAKE.0012
SGBAKE.0013
SGHAST.0005
SGHAST.0006
SGHAST.0007

I am stuck here. Any help is appreciated thanks!

CodePudding user response：

We can handle your requirement using SUBSTRING_INDEX along with REPLACE:

UPDATE yourTable
SET Testtag = REPLACE(SUBSTRING_INDEX(Testtag, '_', -2), '_', '.');

If you just want to view your data this way, then use this query:

SELECT Testtag, REPLACE(SUBSTRING_INDEX(Testtag, '_', -2), '_', '.') AS NewTag
FROM yourTable;

Here is a working demo.