关于Oracle字符串截取!在线等,急!!!

大哥们,打扰一下,关于Oracle字符串截取!在线等,急!!!?

我有一张项目表,里面有一个字段是TECHNOLOGY_ID,表示的是项目所使用的技术,该字段的的记录形如:
TECHNOLOGY_ID:4402,4403,4404
TECHNOLOGY_ID:1202,4222
TECHNOLOGY_ID:1197
记录由每种技术的ID组成,用“,”隔开,长度不定,我怎么才能取得每个单独的ID呢?
谢谢各位!
最新回答
晚街听风

2024-09-27 19:17:04

没有这样的内置函数,需要自定义函数:
网上找来的例子,你参考下吧!

CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000);

CREATE OR REPLACE FUNCTION fn_split (p_str IN VARCHAR2, p_delimiter IN VARCHAR2)
RETURN ty_str_split
IS
j INT := 0;
i INT := 1;
len INT := 0;
len1 INT := 0;
str VARCHAR2 (4000);
str_split ty_str_split := ty_str_split ();
BEGIN
len := LENGTH (p_str);
len1 := LENGTH (p_delimiter);

WHILE j < len
LOOP
j := INSTR (p_str, p_delimiter, i);

IF j = 0
THEN
j := len;
str := SUBSTR (p_str, i);
str_split.EXTEND;
str_split (str_split.COUNT) := str;

IF i >= len
THEN
EXIT;
END IF;
ELSE
str := SUBSTR (p_str, i, j - i);
i := j + len1;
str_split.EXTEND;
str_split (str_split.COUNT) := str;
END IF;
END LOOP;

RETURN str_split;
END fn_split;
/

测试:

DECLARE
CURSOR c
IS
SELECT *
FROM TABLE (CAST (fn_split ('1;;12;;123;;1234;;12345', ';;') AS ty_str_split
)
);

r c%ROWTYPE;
BEGIN
OPEN c;

LOOP
FETCH c INTO r;

EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.put_line (r.column_value);
END LOOP;

CLOSE c;
END;
/

结果:
1
12
123
1234
12345
熊宝宝

2024-09-27 05:07:44

SELECT a.id,
b.lv AS lv,
rtrim(regexp_substr(a.value || ',', '.*?' || ',', 1, b.lv), ',') AS value
FROM (SELECT id,
value ,
length(regexp_replace(value || ',', '[^' || ',' || ']', NULL)) AS rp
FROM (select 1 id,'aa,bb,cc,e,d,dd' value from dual union all select 2,'aaa,bbb,ccc' from dual)tb ) a
INNER JOIN (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 100) b ON b.lv <=
a.rp
ORDER BY 1, 2
一清北华

2024-09-27 07:44:04

用存储过程实现,先instr再substring吧。单独的SQL语句很难完成。
拾柒

2024-09-27 16:56:28

为什么不能在后台编程软件代码那进行读取 然后进行处理呢?
追问
呵呵,我也想那样啊,可是TeamLeader交给我这个任务了,要做几张报表,要我把报表需要的信息做成视图,无奈,唉...
追答
额,那你需要去找截取的函数了。。。 不然很难搞的!就好像做成脚本那样。。 这样麻烦多了