Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Function to get ISO Country Code (11g)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Oracle Function to get ISO Country Code [message #626504 is a reply to message #626503] |
Tue, 28 October 2014 08:33   |
 |
suddhasatwa_oracle
Messages: 24
Registered: January 2014
|
Junior Member |
|
|
Here is my table:
CREATE TABLE COUNTRY(
COUNTRY_CODE NUMBER NOT NULL,
COUNTRY_NAME VARCHAR2(255) NOT NULL,
ISO_COUNTRY CHAR(10) NOT NULL,
CURRENCY_CODE VARCHAR2(3) NOT NULL,
TIMEZONE_OFFSET NUMBER NOT NULL,
prefix_country_code varchar2(5),
ISO_CODE VARCHAR2(3)
);
Here's a few lines of data for above table:
INSERT INTO COUNTRY VALUES (0,'Belguim','BE','EUR', 0, '32','BE');
INSERT INTO COUNTRY VALUES (1,'Switzerland','CH','EUR', 0, '41','CH');
INSERT INTO COUNTRY VALUES (2,'Germany','DE','EUR', 0, '49','DE');
INSERT INTO COUNTRY VALUES (3,'Spain','ES','EUR', 0, '34','ES');
INSERT INTO COUNTRY VALUES (4,'France','FR','EUR', 0, '33','FR');
INSERT INTO COUNTRY VALUES (5,'UK','GB','GBP',-1, '44','GB');
INSERT INTO COUNTRY VALUES (6,'Greece','GR','EUR', 2, '30','GR');
INSERT INTO COUNTRY VALUES (7,'Hungary','HU','HUF', 0, '36','HU');
INSERT INTO COUNTRY VALUES (8,'Ireland','IE','EUR', -1, '353','IE');
INSERT INTO COUNTRY VALUES (9,'Italy','IT','EUR', 0, '39','IT');
INSERT INTO COUNTRY VALUES (10,'Netherlands','NL','EUR', 0, '31','NL');
INSERT INTO COUNTRY VALUES (11,'Poland','PL','PLN', 0, '48','PL');
INSERT INTO COUNTRY VALUES (12,'Portugal','PT','EUR', -1, '351','PT');
COMMIT;
As seen above, there are instances where the ISD code is 3 characters as well.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Oracle Function to get ISO Country Code [message #626632 is a reply to message #626596] |
Thu, 30 October 2014 02:42 |
|
suddhasatwa_oracle
Messages: 24
Registered: January 2014
|
Junior Member |
|
|
Apologies, I was on leave
This is the SQL Query which I used inside a function I created, which uses phone number as the input (variable: v_msisdn) and returns the country code from the base table.
SELECT
CASE
WHEN SUBSTR(v_msisdn , 1, 1) IN
(SELECT c.prefix_country_code
FROM country c
WHERE c.prefix_country_code= SUBSTR(v_msisdn , 1, 1)
)
THEN
(SELECT c.iso_country
FROM country c
WHERE c.prefix_country_code=SUBSTR(v_msisdn , 1, 1)
)
WHEN SUBSTR(v_msisdn , 1, 2) IN
(SELECT c.prefix_country_code
FROM country c
WHERE c.prefix_country_code= SUBSTR(v_msisdn , 1, 2)
)
THEN
(SELECT c.iso_country
FROM country c
WHERE c.prefix_country_code=SUBSTR(v_msisdn , 1, 2)
)
WHEN SUBSTR(v_msisdn , 1, 3) IN
(SELECT c.prefix_country_code
FROM country c
WHERE c.prefix_country_code= SUBSTR(v_msisdn , 1, 3)
)
THEN
(SELECT c.iso_country
FROM country c
WHERE c.prefix_country_code=SUBSTR(v_msisdn , 1, 3)
)
ELSE 'N/A'
END AS ISO_COUNTRY
INTO v_return_code
FROM dual;
This solved the problem.
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon Sep 01 11:04:38 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
