要在一段字串中

找出某個字串的計算方式

1.使用TRANSLATE

SELECT LENGTH(TRANSLATE('AABBADDDDCCAAA', 'A'||'AABBADDDDCCAAA','A')) FROM DUAL;

SELECT LENGTH(TRANSLATE('欄位_A', 'Y'||'欄位_A','Y')) FROM DUAL;

SELECT A.STATUS_FLAG,INSTR(A.STATUS_FLAG,'YYYYYY')  STR_SC,
NVL(LENGTH(TRANSLATE(SUBSTR(A.STATUS_FLAG,INSTR(A.STATUS_FLAG,'YYYYYY')+6,(100-LENGTH(A.STATUS_FLAG))) , 'Y'||SUBSTR(A.STATUS_FLAG,INSTR(A.STATUS_FLAG,'YYYYYY')+6,(100-LENGTH(A.STATUS_FLAG))) ,'Y')),0) AS CNT_SC_AFTER,
NVL(SUBSTR(A.STATUS_FLAG,INSTR(A.STATUS_FLAG,'YYYYYY')+6,(100-LENGTH(A.STATUS_FLAG))),'Z') AS AFTER_SC
FROM WMG_FUND_SC_CNT_REP_ME A
WHERE  A.STATUS_FLAG LIKE '%YYYYYY%'
AND   A.STATUS_FLAG LIKE '%X%'

2.寫一個type function 來計算

CREATE OR REPLACE TYPE SERCHARTYPE IS TABLE OF CHAR;
 
DECLARE
 V_CNT   SERCHARTYPE := SERCHARTYPE();
 V_TEXT   CHAR(20) := 'XXXYYYXXXYYYY';
 V_DATA  CHAR;
 V_VALUE  NUMBER;
 
BEGIN
     FOR I IN 1..LENGTH(V_TEXT) LOOP
         V_CNT.EXTEND;
         V_CNT(I) := SUBSTR(V_TEXT,I,1);
     END LOOP;
 
     INSERT INTO TCF_TEST
     SELECT COLUMN_VALUE, COUNT(*)
     FROM TABLE(V_CNT)
     GROUP BY COLUMN_VALUE;
 
END;
/
 
===================================================================================
CREATE TABLE TCF_TEST (V_DATA CHAR, V_VALUE NUMBER);
 DECLARE
   V_CNT   SERCHARTYPE := SERCHARTYPE();
   V_TEXT   CHAR(20) := 'XXXYYYOOOOXXXYYYOOO';
   V_DATA  CHAR;
   V_VALUE  NUMBER;
 
  BEGIN
       FOR I IN 1..LENGTH(V_TEXT) LOOP
           V_CNT.EXTEND;
           V_CNT( I ) := SUBSTR(V_TEXT,I,1);
       END LOOP;
 
       INSERT INTO TCF_TEST
       SELECT COLUMN_VALUE, COUNT(*)
       FROM TABLE(V_CNT)
       GROUP BY COLUMN_VALUE;
 
  END;
  /
 

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
 

SQL> SELECT * FROM TCF_TEST;
V    V_VALUE
- ----------
O          7
X          6
Y          6
           1