LENGTHB & LENGTH,SUBSTRB & SUBSTR, INSTRB & INSTR

这篇主要介绍三对关于字符函数和字节函数：
1.LENGTHB & LENGTH FUNCTION:

SQL> SELECT LENGTHB('我A')AS BYTE_LEN,LENGTH('我A') AS WORD_LEN FROM DUAL;
BYTE_LEN   WORD_LEN
---------- ----------
         4          2

2.SUBSTRB & SUBSTR FUNCTION:

SQL> SELECT SUBSTRB('我ABC们', 1,3)AS BYTE_SUBSTR,SUBSTR('我ABC们',1,3) AS WORD_SUBSTR    FROM DUAL;
BYTE_SUBSTR WORD_SUBSTR
----------- -----------
我          我AB

3. INSTRB & INSTR FUNCTION:

  SQL> SELECT INSTRB('我ABC们D','D',5,1) AS BYTE_INSTR, INSTR('我ABC们D','D',5,1) AS WORD_INSTR FROM DUAL;
BYTE_INSTR WORD_INSTR
---------- ----------
        10          6

下面简单介绍一下这三对函数的用法：

 ---F_GET_UTF8_LENGTH 为GBK编码字节长度(一个汉字,两个字节)
FUNCTION F_GET_UTF8_LENGTH(I_UTF8_STR IN VARCHAR2) RETURN INTEGER AS
    N_BYTE_LENGTH NUMBER(4) := 0;
    N_ASCII_LENGTH NUMBER(4) := 0;
  BEGIN
    N_BYTE_LENGTH := LENGTHB(I_UTF8_STR);
    N_ASCII_LENGTH := LENGTH(I_UTF8_STR);
    IF LENGTHB('人') = 3 AND N_BYTE_LENGTH > N_ASCII_LENGTH THEN
      RETURN (N_BYTE_LENGTH + N_ASCII_LENGTH) / 2;
    ELSE
      RETURN N_BYTE_LENGTH;
    END IF;
  END F_GET_UTF8_LENGTH;
 ---F_GET_UTF8_SUBSTR 为GBK编码字节子串(一个汉字,两个字节)
  FUNCTION F_GET_UTF8_SUBSTR(I_UTF8_STR IN VARCHAR2,
                             I_OFFSET IN OUT INTEGER,
                             I_LENGTH IN INTEGER) RETURN VARCHAR2 AS
    N_BYTE_LENGTH NUMBER(4) := 0;
    N_ASCII_LENGTH NUMBER(4) := 0;
    N_BYTE_LENGTH1 NUMBER(4) := 0;
    N_ASCII_LENGTH1 NUMBER(4) := 0;
    S_BYTE_STR VARCHAR2(500);
    S_ASCII_STR VARCHAR2(500);
    S_TMP_STR VARCHAR2(500);
  BEGIN
    N_BYTE_LENGTH := LENGTHB(I_UTF8_STR);
    N_ASCII_LENGTH := LENGTH(I_UTF8_STR);
    IF LENGTHB('人') = 3 AND N_BYTE_LENGTH > N_ASCII_LENGTH THEN
      S_BYTE_STR := SUBSTRB(I_UTF8_STR,
                            I_OFFSET,
                            I_LENGTH);
      S_ASCII_STR := SUBSTR(I_UTF8_STR,
                            I_OFFSET,
                            I_LENGTH);
      N_ASCII_LENGTH1 := LENGTH(S_BYTE_STR);
      IF LENGTHB(S_BYTE_STR) = LENGTHB(S_ASCII_STR) THEN
        S_TMP_STR := SUBSTRB(I_UTF8_STR,
                             I_OFFSET,
                             I_LENGTH);
        I_OFFSET := I_OFFSET + I_LENGTH;
        RETURN S_TMP_STR;
      ELSE
        N_BYTE_LENGTH1 := (3 * I_LENGTH - N_ASCII_LENGTH1) / 2;
        S_TMP_STR := SUBSTRB(I_UTF8_STR,
                             I_OFFSET,
                             N_BYTE_LENGTH1);
        I_OFFSET := I_OFFSET + N_BYTE_LENGTH1;
        RETURN S_TMP_STR;
      END IF;
    ELSE
      S_TMP_STR := SUBSTRB(I_UTF8_STR,
                           I_OFFSET,
                           I_LENGTH);
      I_OFFSET := I_OFFSET + I_LENGTH;
      RETURN S_TMP_STR;
    END IF;
  END F_GET_UTF8_SUBSTR;