aud05_mecab_vertico.sql

drop table jumbo.aud06_mecab_vertical; 		
		
create table jumbo.aud06_mecab_vertical ( 		
docid integer, 		
seqno integer, 		
word varchar(10) character set unicode, 		
pos varchar(10) character set unicode, 		
norm varchar(10) character set unicode 		
) primary index(docid) 		
; 		
		
insert into jumbo.aud06_mecab_vertical 		
with src as ( 		
	/*改行コードで別行にする*/ 	
	select 	
	outkey as docid, 	
	token_nbr as seqno, 	
	result_string 	
	from table (	
		regexp_split_to_table 
		(jumbo.aud04_mecab.docid, jumbo.aud04_mecab.docdesc, '\n','c') 
		returns (outkey integer, token_nbr integer, result_string varchar(1000) character set unicode) 
	) a1 	
) 		
	 /*49文字: select max(char_length(result_string)) as aaa from src*/ 	
	 /*タブとカンマで列分解する*/	
	select 	
	docid, 	
	seqno, 	
	strtok(result_string, '	',1) as word, 
	strtok((strtok(result_string, '	', 2)), ',', 1) as pos, 
	 /*strtok(result_string, ',', 3) as n3, */ 	
	 /*strtok(result_string, ',', 4) as n4, */ 	
	 /*strtok(result_string, ',', 5) as n5, */ 	
	 /*strtok(result_string, ',', 6) as n6, */ 	
	strtok(result_string, ',', 7) as norm 	
	 /*strtok(result_string, ',', 8) as n8, */ 	
	 /*strtok(result_string, ',', 9) as n9, */ 	
	from src 	
	where result_string<>'EOS' 	
	;