Oracle string segmentation

Processing of Oracle string segmentation 1. create or replace type t_ret_table is table of varchar2 (20); 2. use a custom function to implement 1. Use the function to return an array. using create or replace function f_split_string (var_str in string, var_split In String) return t_ret_table isvar_out t_ret_table; var_tmp varchar2 (4000); var_element varchar2 (4000); usage: = var_str; var_out: = t_ret_table (); -- if a matched separator while instr (var_tmp, var_split)> 0 loopvar_elemen exists T: = substr (var_tmp, 1, instr (var_tmp, var_split)-1); var_tmp: = substr (var_tmp, instr (var_tmp, var_split) + length (var_split ), length (var_tmp); var_out.extend (1); var_out (var_out.count): = var_element; end loop; var_out.extend (1); var_out (var_out.count): = var_tmp; return var_out; end f_split_string; 3. use the pipelined Function. create or replace function f_split (var_str in string, var_split In String) return t_r Et_table implements varchar2 (4000); var_element varchar2 (4000); n_length Number: = length (var_split); beginvar_tmp: = var_str; while instr (var_tmp, var_split)> 0 loopvar_element: = substr (var_tmp, 1, instr (var_tmp, var_split)-1); var_tmp: = substr (var_tmp, instr (var_tmp, var_split) + n_length, length (var_tmp )); pipe row (var_element); end loop; pipe row (var_tmp); return; end f_split; 4. returns an array of string-separated functions, but can also be queried using statements. Query select * from table (f_split_string ('hanjs-zhaos ','-'); 5. the sorting can be fully reflected in the query. note: The queried column name is Column_Value 6. how to remove the repeated characters www.2cto.com in a string such,, 'How do I remove repeated characters in the string with commas and sort the characters in ascending order to get ', 9, 'You can't explain it. It's a good expert to give it a try. Answer: select col from (select sys_connect_by_path (col, ',') | ', 'col, level from (select col, row_number () over (order by rownum) rn from (select distinct substr (col, instr (col, ',', 1, rownum) + 1, instr (col, ',', 1, rownum + 1) -instr (col, ',', 1, rownum)-1) col from (select ', 9, 'col from dual) connect by rownum <length (translate (col, ',' | col, ',') connect by prior rn = rn-1 order by level desc) where rownum = 1