Reputation: 349
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 new column that is Country.
Example data:
Template_name Country
Party_package_US US or USA
PARTY_Package_GB GB or England
Random_temp_DE DE or Germany
The output to the new column can be just the three last characters.
I don't know what kind of query I have to do to get that result.
Okay now, what can I do get that result to the new column in table?
UPDATE #silverpop_header
SET MARKET_AREA = a.template_name
FROM #silverpop_header pop
join dw.f_CRM a
ON pop.template_name = a.TEMPLATE_NAME
left join (
select
RIGHT(RTRIM(Template_name), 2) country
from dw.f_CRM )
It is on Sybase
Upvotes: 1
Views: 82
Reputation: 6065
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 prepraration
create table tbl(template_name varchar(200));
insert into tbl values
('Party_package_US'),
('PARTY_Package_GB'),
('Random_temp_DE');
SELECT * FROM tbl;
-- Needed query
SELECT 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 |
+------------------+
3 rows in set (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 |
+------------------+---------+
3 rows in set (0.00 sec)
mysql>
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.8-rc |
+-----------+
1 row in set (0.00 sec)
Upvotes: 0
Reputation: 443
If you just need the last two characters
SELECT
SUBSTRING(Template_name,-2) AS 'Country'
FROM TABLE;
EDIT: Or as mentioned in the comments:
SELECT
RIGHT(Template_name,2) AS 'Country'
FROM TABLE;
Upvotes: 2