謝謝大神的建議,不過可以指明下思路不,然後我自己寫下,後面不知道怎麼寫了 CREATE OR REPLACE FUNCTION SFIS1.CHECK_Field_BY_Comma (Str IN VARCHAR2) RETURN STRING AS Get_qty INT; Get_Str STRING;
BEGIN SELECT LENGTH (Str) - LENGTH (REPLACE (Str, ',', '')) + 1 INTO Get_qty FROM DUAL ; While Get_qty > 0 Loop begin
Get_qty := Get_qty+1; end end Loop; RETURN Get_Str; END; /
不用写循环,利用sql语句进行拼接就可以了select TO_CHAR(WMSYS.WM_CONCAT('REGEXP_SUBSTR(A.BOM_NO, ''[^,]+'', 1, '||ROWNUM||')')) INTO Get_Str from dual CONNECT BY ROWNUM <= LENGTH (Str)-LENGTH (REPLACE (Str, ',', ''))+1
楼主到底想要什么? group by 了,要使用聚合函数, 使用聚合函数了,又想分开? 个人觉得,楼主直接把自己的需求贴上,加上表结构和一些测试数据。
select SFIS1.CHECK_Field_BY_Comma(A.BOM_NO) ,A.KEY_PART_NO from ( SELECT TO_CHAR(WMSYS.WM_CONCAT (A.BOM_NO)) BOM_NO,A.KEY_PART_NO , TO_CHAR(WMSYS.WM_CONCAT (A.FEEDER_NO )) FROM SFIS1.C_SMT_BOM_T A WHERE A.BOM_NO IN('PCDNR6D008210-A302-AF209-1','TCATHA1CPU100-A301-AF209-2','TCDNR6D008210-A301-AF209-1') and A.KEY_PART_NO in(select b.KEY_PART_NO from SFIS1.C_SMT_BOM_T b where B.BOM_NO='TCDNR6D008210-A301-AF209-1' ) GROUP BY A.KEY_PART_NO HAVING COUNT(1)>1 ORDER BY BOM_NO) a 這樣子不能帶進去算嗎???
select
REGEXP_SUBSTR(A.BOM_NO, '[^,]+', 1, 1),
REGEXP_SUBSTR(A.BOM_NO, '[^,]+', 1, 2),
REGEXP_SUBSTR(A.BOM_NO, '[^,]+', 1, 3),
……
--11G之后可以用下面这个
REGEXP_COUNT(str, ',') + 1
謝謝大神的建議,不過可以指明下思路不,然後我自己寫下,後面不知道怎麼寫了
CREATE OR REPLACE FUNCTION SFIS1.CHECK_Field_BY_Comma (Str IN VARCHAR2)
RETURN STRING
AS
Get_qty INT;
Get_Str STRING;
BEGIN
SELECT LENGTH (Str) - LENGTH (REPLACE (Str, ',', '')) + 1
INTO Get_qty
FROM DUAL ;
While Get_qty > 0 Loop
begin
Get_qty := Get_qty+1;
end
end Loop;
RETURN Get_Str;
END;
/
INTO Get_Str
from dual
CONNECT BY ROWNUM <= LENGTH (Str)-LENGTH (REPLACE (Str, ',', ''))+1
group by 了,要使用聚合函数,
使用聚合函数了,又想分开?
个人觉得,楼主直接把自己的需求贴上,加上表结构和一些测试数据。
REGEXP_SUBSTR(A.BOM_NO, '[^,]+', 1, 1),REGEXP_SUBSTR(A.BOM_NO, '[^,]+', 1, 2),REGEXP_SUBSTR(A.BOM_NO, '[^,]+', 1, 3)
from (
SELECT TO_CHAR(WMSYS.WM_CONCAT (A.BOM_NO)) BOM_NO,A.KEY_PART_NO ,
TO_CHAR(WMSYS.WM_CONCAT (A.FEEDER_NO ))
FROM SFIS1.C_SMT_BOM_T A
WHERE A.BOM_NO IN('PCDNR6D008210-A302-AF209-1','TCATHA1CPU100-A301-AF209-2','TCDNR6D008210-A301-AF209-1')
and A.KEY_PART_NO in(select b.KEY_PART_NO from SFIS1.C_SMT_BOM_T b where B.BOM_NO='TCDNR6D008210-A301-AF209-1' )
GROUP BY A.KEY_PART_NO HAVING COUNT(1)>1
ORDER BY BOM_NO) a
這樣子不能帶進去算嗎???
為什麽值沒有按正則表達式表示了,下面的顯示的數值直接就變成這樣子
REGEXP_SUBSTR(A.BOM_NO, '[^,]+', 1, 1),REGEXP_SUBSTR(A.BOM_NO, '[^,]+', 1, 2),REGEXP_SUBSTR(A.BOM_NO, '[^,]+', 1, 3)