如何最好分割csv字符串在oracle 9i [英] How to best split csv strings in oracle 9i

问题描述

我想要在Oracle 9i中拆分csv字符

我阅读了以下文章：
http://www.oappssurd.com/2009/03/string-split-in-oracle.html

但我不明白如何使这项工作。
这里是我的一些问题

1. 这在Oracle 9i中是否有效，如果没有，为什么不？ li>
   
2. 是否有更好的方法拆分csv字符串然后上述解决方案？


3. 我需要创建一个新类型吗？


4. 我可以在函数中声明w /类型吗？

<下面是一个字符串tokenizer，它比这个页面更简单一些，但不知道它是否如此快速：

$ b

 创建或替换函数splitter_count（str在varchar2中，delim in char）return int as 
 val int; 
 begin 
 val：= length（replace（str，delim，delim ||''））; 
 return val  -  length（str）; 
 end; 
 
 create type token_list is varray（100）of varchar2（200）; 
 
 CREATE或替换函数tokenize（str varchar2，delim char）return token_list as 
 ret token_list; 
 target int; 
 i int 
 this_delim int; 
 last_delim int; 
 BEGIN 
 ret：= token_list（）; 
 i：= 1; 
 last_delim：= 0; 
 target：= splitter_count（str，delim）; 
 while i <= target 
 loop 
 ret.extend（）; 
 this_delim：= instr（str，delim，1，i）; 
 ret（i）：= substr（str，last_delim + 1，this_delim  -  last_delim -1）; 
 i：= i + 1; 
 last_delim：= this_delim; 
 end loop; 
 ret.extend（）; 
 ret（i）：= substr（str，last_delim + 1）; 
 return ret; 
 end; 
  

您可以这样使用：

  select tokenize（'hi you person'，''）from dual; 
 VARCHAR（hi，you，person）
  

I want to be able to split csv strings in Oracle 9i

I've read the following article http://www.oappssurd.com/2009/03/string-split-in-oracle.html

But I didn't understand how to make this work. Here are some of my questions pertaining to it

1. Would this work in Oracle 9i, if not, why not?
2. Is there a better way of going about splitting csv strings then the solution presented above?
3. Do I need to create a new type? If so, do I need specific privilages for that?
4. Can I declare the type w/in the function?

解决方案

Here's a string tokenizer for Oracle that's a little more straightforward than that page, but no idea if it's as fast:

create or replace function splitter_count(str in varchar2, delim in char) return int as
val int;
begin
  val := length(replace(str, delim, delim || ' '));
  return val - length(str); 
end;

create type token_list is varray(100) of varchar2(200);

CREATE or replace function tokenize (str varchar2, delim char) return token_list as
ret token_list;
target int;
i int;
this_delim int;
last_delim int;
BEGIN
  ret := token_list();
  i := 1;
  last_delim := 0;
  target := splitter_count(str, delim);
  while i <= target
  loop
    ret.extend();
    this_delim := instr(str, delim, 1, i);
    ret(i):= substr(str, last_delim + 1, this_delim - last_delim -1);
    i := i + 1;
    last_delim := this_delim;
  end loop;
  ret.extend();
  ret(i):= substr(str, last_delim + 1);
  return ret;
end;

You can use it like this:

select tokenize('hi you person', ' ') from dual;
VARCHAR(hi,you,person)

这篇关于如何最好分割csv字符串在oracle 9i的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！