데이터베이스 질문과 답변
SELECT A , B, C, D,
LENGTHB(A), LENGTHB(B), LENGTHB(C), LENGTHB(D)
FROM (
SELECT '가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가' AS A
, '123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789' AS B
, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' AS C
, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' AS D
FROM DUAL
) ;
LENGTHB 를 사용하여 문자열의 BYTE를 추출한건데요.
100 BYTE 가 넘는 문자열을 오른쪽 끝에서 절삭하여 100byte이내의 문자열을 리턴하고 싶습니다.
oracle 버전은 9i 입니다.
by 우리집아찌
[2013.07.17 14:43:46]
SUBSTRB 가 있긴한데 ORACLE 9i에서 되는지는 몰겠네요..
by 빈이
[2013.07.17 14:45:50]
우리집아찌님 감사합니다.
SUBSTRB('문자열', 0,100) 으로 해결했습니다.
by DIIIN
[2013.07.17 14:50:36]
SELECT LENGTHB(AA)
, LENGTHB(BB)
, LENGTHB(CC)
, LENGTHB(DD)
FROM (
SELECT case when lengthb(substr(a,1,1)) = 1 then substr(a,1,100) else substr(a,1,50) end aa
, case when lengthb(substr(b,1,1)) = 1 then substr(b,1,100) else substr(b,1,50) end bb
, case when lengthb(substr(c,1,1)) = 1 then substr(c,1,100) else substr(c,1,50) end cc
, case when lengthb(substr(d,1,1)) = 1 then substr(d,1,100) else substr(d,1,50) end dd
FROM (
SELECT '가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가가' AS A
, '123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789123456789' AS B
, 'AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA' AS C
, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' AS D
FROM DUAL
)
) ;
by DIIIN
[2013.07.17 14:52:38]
SUBSTRB 라는 것도 있군요.
by 손님
[2013.07.19 10:06:35]
DIIIN 님이 보내주신건 한글,영문,숫자가 포함 된 문자열에서는 애매해져요...
제가 예시를 저렇게만 하기는 했지만 ^^;;;
한글을 3byte로 인식하는 경우에도 다르겠네요.
) 버튼을 클릭하여 작성 하시면 됩니다.