--使用instr,substr,處理字符串數據

------------------------------------shangpin表增加一個caigou_date字段用于存儲采購日期-------
drop table shangpin;
--建立商品表
create table shangpin
(name varchar(20), --商品名
 price number(8,2),--價格
 shuliang number(3),--庫存數量
 caigou_date date) --采購日期;

--插入數據,商品名為 廠家,產品
insert into shangpin values ('TCL,電視',100,3,'29-8月 -02');
insert into shangpin values ('三元,豆腐',1,30,'28-8月 -02');
insert into shangpin values ('tcl,電腦',1000,3,'30-8月 -02');
insert into shangpin values ('Tcl,電話',50,3,'29-8月 -02');
insert into shangpin values ('Abb,開關',1,30,'29-7月 -02');
insert into shangpin values ('Abb,電腦',100,30,'29-8月 -02');


select  name,instr(name,',',1) from shangpin;

select substr(name,1,instr(name,',',1)-1) 
from shangpin;



--將廠家與產品分別顯示
select substr(name,1,instr(name,',',1)-1),
substr(name,instr(name,',',1)+1,99) ,price from shangpin
order by name;

select substr(name,1,instr(name,',',1)-1),
substr(name,instr(name,',',1)+1,length(name)) ,price from shangpin
order by name;

--將所有含字母的廠家名大寫
select upper(substr(name,1,instr(name,',',1)-1)),
substr(name,instr(name,',',1)+1,length(name)) ,price from shangpin
order by name;

--使用distinct get 單一name
select distinct 
upper(substr(name,1,instr(name,',',1)-1))
from shangpin;


--建立產品信息表，包括：生產廠商,商品名,單價
用shangpin表的為產品信息表添加數據




create table product (company varchar2(20), product varchar2(20),price number(6,2));

insert into product select upper(substr(name,1,instr(name,',',1)-1)),
substr(name,instr(name,',',1)+1,length(name)) ,price from shangpin
order by name;