How To Get Info From Varchar Column And Create New Column Out Of It

I have a problem that I can't figure out. I have the table where it has column template_name, in every template, it ends with _US, _EE (aka country). I have to get that part to a

Solution 1:

If you just need the last two characters

SELECTSUBSTRING(Template_name,-2) AS'Country'FROMTABLE;

EDIT: Or as mentioned in the comments:

SELECTRIGHT(Template_name,2) AS'Country'FROMTABLE;

Solution 2:

Another way to handle non-2-digit country code.

SELECT Template_name, SUBSTRING_INDEX(Template_name, '_', -1) Country
FROM tbl;

To make this as a full demo. Let me post the full SQLs and output:

SQL:

-- Data preprarationcreatetable tbl(template_name varchar(200));
insertinto tbl values
('Party_package_US'),
('PARTY_Package_GB'),
('Random_temp_DE');
SELECT*FROM tbl;

-- Needed querySELECT Template_name, SUBSTRING_INDEX(Template_name, '_', -1) Country
FROM tbl;

Output:

mysql>SELECT*FROM tbl;
+------------------+| template_name    |+------------------+| Party_package_US || PARTY_Package_GB || Random_temp_DE   |+------------------+3rowsinset (0.00 sec)

mysql>
mysql>-- Needed query
mysql>SELECT Template_name, SUBSTRING_INDEX(Template_name, '_', -1) Country
    ->FROM tbl;
+------------------+---------+| Template_name    | Country |+------------------+---------+| Party_package_US | US      || PARTY_Package_GB | GB      || Random_temp_DE   | DE      |+------------------+---------+3rowsinset (0.00 sec)

mysql>
mysql>select version();
+-----------+| version() |+-----------+|5.7.8-rc  |+-----------+1rowinset (0.00 sec)