aud06_sudachi_vertico.sql

with src as ( 		
	/*★で別行にする*/ 	
	select 	
	outkey as docid, 	
	token_nbr as seqno, 	
	result_string 	
	from table (	
		regexp_split_to_table 
		(jumbo.aud05_sudachi.docid, jumbo.aud05_sudachi.docdesc, '★','c') 
		returns (outkey integer, token_nbr integer, result_string varchar(1500) character set unicode) 
	) a1 	
) 		
, rpls as ( 		
	 /*最大文字数の確認62文字: select max(char_length(result_string)) as aaa from src */ 	
	 /*余計な文字列を落とす*/ 	
	select 	
	docid, 	
	seqno, 	
	oreplace( 	
	oreplace( 	
	oreplace( 	
	oreplace( 	
	oreplace( 	
	result_string 	
	,',,(',',') 	
	,'\n','') 	
	,''')',',') 	
	,''', ''',',') 	
	,',''',',') 	
	 as result_string 	
	from src 	
) 		
	 /*カンマで列分解する*/	
	select 	
	docid, 	
	cast(row_number() over(partition by docid order by seqno) as integer) as seqno, 	
	strtok(result_string, ',',1) as word, 	
	strtok(result_string, ',',2) as 品詞, 	
	 /*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 n7, */ 	
	strtok(result_string, ',',8) as 正規表現 	
	 /*strtok(result_string, ',',9) as n9, */  	
	from rpls 	
	;