clob 컬럼을 substr한다는 말씀이신가요?

11.2.0.3.0 기준 substr은 잘 되고 substrb는 안 되네요.

substrb는

substrb(to_char(substr(컬럼,1,10)),1,10)

혹은

substrb(dbms_lob.substr(컬럼,10),1,10)

이런 식으로 구현하면 될듯합니다.

더 좋은 방법이 있는지는 모겠네요.

아.. 제대로 안 잘린다고 하신 거 보니.. 자르기는 잘랐는데 원하는 결과가 아닌 것 같네요?

substrb가 바이트로 자르는 것 맞는데 어떻게 잘 안 되신다는 건지..

실제 데이터를 예로 들어 주세요.

원본데이터, 사용쿼리, 결과데이터, 원하는 결과

올려주신 데이터를 보면
(492,  6) => '나나  '
(498, 19) => '            다다다0'
(498, 20) => '            다다다00'
이렇게 나오네요.
숫자를 정확하게 알려주신게 맞는지?
전혀 다른 값은 뭐가 나오나요? 공백까지 포함해서 정확하게 알려주세요.
오라클 버전은 어떻게 되나요?
 

제 환경에서는 또 다르게 나오네요.

한글이 3Byte 여서 그런 것인데...

-- Version : Oracle Database 11g Express Edition Release 11.2.0.2.0
-- nls_characterset : AL32UTF8
-- nls_nchar_characterset : AL16UTF16
WITH data_table AS
(
SELECT
TO_CLOB(
'00000000000    0000  00000000000000                                                            000                                        00000000000000000                                        0000                                        00000                                                  00000000                                                                                        가가가가가가가가가가                                                                      가가가가가  나나              다다다00000000                                                        가가                                                      가가가                                                            0000000000000000000                                            가가가         000000000000 ZZZZZZZZZ00 00000000000000              가가가00000000000000ZZZZZZZZZ000 ZZZZZZZZZ00 ZZZZZZ000    ZZZZZZ0000000.0000000000000000000000.00000000000000000000000.000000000'
) AS data
FROM dual
)
SELECT SUBSTRB(TO_CHAR(data), 492,  6) x
     , SUBSTRB(TO_CHAR(data), 498, 19) x
     , SUBSTRB(TO_NCHAR(data), 492,  6) y
     , SUBSTRB(TO_NCHAR(data), 498, 19) y
  FROM data_table
;
-- 결과 : '  가 ', '  가가  나나   ', '  ', '         '

한글자씩 잘라서 루프돌리시면서 사이즈 체크해가면서 글자를 완성시켜 나가야 할 듯 ㅡ,.ㅡ

WITH data_table AS
(
-- 원본 테이블
SELECT 1 no,
TO_CLOB(
'00000000000    0000  00000000000000                                                            000                                        00000000000000000                                        0000                                        00000                                                  00000000                                                                                        가가가가가가가가가가                                                                      가가가가가  나나              다다다00000000                                                        가가                                                      가가가                                                            0000000000000000000                                            가가가         000000000000 ZZZZZZZZZ00 00000000000000              가가가00000000000000ZZZZZZZZZ000 ZZZZZZZZZ00 ZZZZZZ000    ZZZZZZ0000000.0000000000000000000000.00000000000000000000000.000000000'
) AS data
FROM dual
)
, code_table AS
(
-- 잘라올 항목들의 시작위치와 길이를 코드화
SELECT 1 cd, 492 a, 6 b FROM dual
UNION ALL SELECT 2, 498, 19 FROM dual
)
SELECT *
  FROM (SELECT a.no
             , c.cd
             , LISTAGG(v) WITHIN GROUP(ORDER BY lv) v
          FROM (SELECT no
                     , lv
                     , SUBSTR(v, lv, 1) v
                     , SUM(LEAST(LENGTHB(SUBSTR(v, lv, 1)), 2)) OVER(PARTITION BY no ORDER BY lv)
                         - LEAST(LENGTHB(SUBSTR(v, lv, 1)), 2) + 1 x
                  FROM (SELECT no, TO_CHAR(data) v FROM data_table)
                     , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 999)
                 WHERE lv <= LENGTH(v)
                ) a
             , code_table c
         WHERE a.x BETWEEN c.a AND c.a + c.b - 1
         GROUP BY a.no, c.cd
        )
 PIVOT (MIN(v) FOR cd IN (1, 2))
;

substrb(convert(to_char(컬럼), 'KO16KSC5601','AL32UTF8'),1,10) 잘될지는 모르겠습니다.

convert라고 characterset 바꿔주는 함수가 10g부터 있었네요.

CONVERT( '대상 문자열', '타겟 캐릭터셋', '소스 캐릭터셋')

http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions034.htm#SQLRF00620