Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
0
Help us understand the problem. What is going on with this article?
@takiru

【Oracle】文字列をバイト数指定で区切る方法

どうしても文字列をバイト数で文字を区切りたい場合のファンクション

VARCHAR2(5000)だけ、望む項目長に調整しなきゃだけど。

CREATE OR REPLACE FUNCTION SUBSTR_EX(
    A_TEXT IN VARCHAR2
  , A_BYTE IN NUMBER
  , A_ROW  IN NUMBER
)
RETURN VARCHAR2
IS
  L_START_POSITION  NUMBER;
  L_RESULT          VARCHAR2(5000);
  L_ROW_COUNT       NUMBER;

BEGIN
  L_RESULT := '';
  L_ROW_COUNT := 1;
  L_START_POSITION := 1;

  -- テキスト値がNULLの場合は何も出力しない
  IF A_TEXT IS NULL THEN
    RETURN NULL;
  END IF;

  LOOP
    L_RESULT := L_RESULT || SUBSTR(A_TEXT, L_START_POSITION, 1);

    -- バイト数が合致した場合
    IF LENGTHB(L_RESULT) = A_BYTE THEN
      EXIT WHEN(L_ROW_COUNT = A_ROW);
      L_ROW_COUNT := L_ROW_COUNT + 1;
      L_RESULT := '';
    END IF;

    -- 全角文字を取得してバイト数が超過した場合
    IF LENGTHB(L_RESULT) > A_BYTE THEN
      L_RESULT := SUBSTR(L_RESULT, 1, LENGTH(L_RESULT) - 1);
      EXIT WHEN(L_ROW_COUNT = A_ROW);
      L_ROW_COUNT := L_ROW_COUNT + 1;
      L_RESULT := '';
      L_START_POSITION := L_START_POSITION - 1;
    END IF;

    -- 全文字処理したら終了
    IF L_START_POSITION = LENGTH(A_TEXT) THEN
      IF A_ROW > L_ROW_COUNT THEN
        L_RESULT := '';
      END IF;
      EXIT;
    END IF;
    L_START_POSITION := L_START_POSITION + 1;
  END LOOP;

  RETURN L_RESULT;
END;
/
0
Help us understand the problem. What is going on with this article?
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away

Comments

No comments
Sign up for free and join this conversation.
Sign Up
If you already have a Qiita account Login
0
Help us understand the problem. What is going on with this article?