create table PILOT (
PILOT_NO             varchar2 (4),
NAME                 varchar2 (25),
ADDRESS              varchar2 (25),
CITY                 varchar2 (25),
PHONE                varchar2 (12),
TOTAL_HOURS                      number (7,2));


insert into PILOT values ('0001', 'Whiteknuckles, Harry', '9 Muddy Lane',
     'Swift Current', '306.911.1234', 0);

insert into PILOT 
values ('0002', 'Timson, Jean', '2302 - 67th St NE', 
    'Calgary', '403.288.1492',0);

insert into PILOT 
values ('0003', 'Lee, Wong', '17 Chester Place',
    'Calgary', '403.247.6543',0);

insert into PILOT 
values ('0004', 'Bedard, George', '7902 - 69 Ave', 
    'Saskatoon', '306.444.5555',0);

insert into PILOT 
values ('0005', 'Trachuk, Tony', '123 - 3rd St',
    'Saskatoon', '306.354.1234',0);

insert into PILOT 
values (
'0006', 'Paulson, Penny', '49 Gaetz Ave',
    'Red Deer', '403.356.4321',0);
commit;

CodePudding user response：

Function SUBSTRING_INDEX() is what you want:

SELECT SUBSTRING_INDEX(PHONE, '.', 1) as `Area Code`, 
    SUBSTRING_INDEX(PHONE, '.', -2) as `Local Number`
FROM PILOT;

CodePudding user response：

with this

ALTER PILOT
ADD COLUMN AreaCode INT,
ADD COLUMN AreaCode VARCHAR(12);