close

翻譯公司

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
 

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

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;
  /
 

找出某個字串的較量爭論體例

要在一段字串中

2.寫一個type function 來較量爭論

1.使用TRANSLATE

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%'

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


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