/**********************************************************************************************
-- Title : [2k] BLOB TEXT 조회 및 업데이트, Pointer Valid 체크
-- Reference : dBRang.com
-- Key word : textptr, patindex, datalength, textvalid, blob, pointer valid, cast
**********************************************************************************************/
-- CAST문으로 NVARCHAR타입 변환도 가능.
-- 2k5이상에서는 NVARCHAR(MAX)로도 가능.
-- 작은 경우 NVARCHAR 타입으로 변경하여 REPLACE, LEFT, RIGHT, SUBSTRUNG 처리 가능.
USE tempdb
GO
IF (OBJECT_ID('pub_info') IS NOT NULL)
DROP TABLE pub_info
GO
CREATE TABLE pub_info (a INT IDENTITY, pr_info TEXT, pr_info2 TEXT)
GO
INSERT INTO pub_info (pr_info, pr_info2) VALUES ('12345678901234567890', null)
INSERT INTO pub_info (pr_info) VALUES ('abcdefghijabcdefghij')
INSERT INTO pub_info (pr_info, pr_info2) VALUES ('최명환최고봉', '하나두울세엣')
GO
/*
** 일반 연산 확인
*/
SELECT * FROM pub_info WHERE pr_info = '12345678901234567890' -- error
SELECT * FROM pub_info WHERE pr_info like '12345678901234567890' -- like 연산만 가능
SELECT LEN(pr_info) FROM pub_info -- LEN() 사용 불가
SELECT DATALENGTH(pr_info) FROM pub_info -- DATALENGTH() 사용 가능
SELECT SUBSTRING(pr_info,2,2) FROM pub_info -- 한글경우 깨짐
GO
/*
** TEXTPTR(), READTEXT 활용한 읽기
*/
DECLARE @textptr BINARY(16)
SELECT @textptr=TEXTPTR(pr_info)
FROM pub_info (holdlock)
WHERE a = 1
READTEXT pub_info.pr_info @textptr 2 5 -- 0부터 시작하는데 2부터 5개 추출
/*
** PATINDEX()로 원하는 부분 찾아 읽기
*/
USE pubs
GO
DECLARE @textptr BINARY(16), @patindex INT, @patlength INT
SELECT @textptr=TEXTPTR(pr_info)
, @patindex=PATINDEX('%algodata infosystems%', pr_info) - 1 /* -1 : 자리수 맞춤 */
, @patlength=DATALENGTH('algodata infosystems')
FROM pub_info (holdlock)
WHERE PATINDEX('%algodata infosystems%', pr_info) <> 0
READTEXT pub_info.pr_info @textptr @patindex @patlength
/*
-- TEXTVALID()를 활용한 TEXTPOINT 유효성 확인
-- insert시 null을 지정해도 invalid pointer를 갖는다.
-- valid pointer를 가지기 위해선 null이 아닌 값을 넣거나
-- "UPDATE~ SET <column> = null~"을 해야한다.
*/
DROP TABLE #valied_t
go
CREATE TABLE #valied_t
( wkey CHAR(16)
, cl TEXT
, cla TEXT
)
go
INSERT INTO #valied_t(wkey) VALUES('US19766122908B2A')
INSERT INTO #valied_t(wkey) VALUES('US19766122906B2A')
INSERT INTO #valied_t(wkey, cl) VALUES('US19766122903B2A', null)
INSERT INTO #valied_t(wkey, cla) VALUES('US19766122901B2A', null)
INSERT INTO #valied_t(wkey, cl, cla) VALUES('US19766122905B2A', null, null)
INSERT INTO #valied_t(wkey, cl, cla) VALUES('US19766120905B2A', 'aaa', 'bbb')
INSERT INTO #valied_t(wkey, cl, cla) VALUES('US19766122925B2A', 'aaa', null)
INSERT INTO #valied_t(wkey, cl, cla) VALUES('US19766126905B2A', null, 'bbb')
UPDATE #valied_t SET cla = null
WHERE cla IS NULL
SELECT wkey
, 'Valid (if 1) Text data' = TEXTVALID ('#valied_t.cl', TEXTPTR(cl)) -- 0 이 존재
, 'Valid (if 1) Text data' = TEXTVALID ('#valied_t.cla', TEXTPTR(cla)) -- 0 이 없음.
FROM #valied_t
GO
UPDATE #valied_t SET cl = null
WHERE cl IS NULL
SELECT wkey
, 'Valid (if 1) Text data' = TEXTVALID ('#valied_t.cl', TEXTPTR(cl)) -- 0 이 존재
, 'Valid (if 1) Text data' = TEXTVALID ('#valied_t.cla', TEXTPTR(cla)) -- 0 이 없음.
FROM #valied_t
GO
SELECT * FROM #valied_t
GO
/*
** UPDATETEXT를 활용한 TEXT필드 업데이트
*/
DECLARE @ptrval1 VARBINARY(16), @ptrval2 VARBINARY(16)
SELECT * FROM #valied_t WHERE WKEY = 'US19766120905B2A'
SELECT @ptrval1 = TEXTPTR(cl), @ptrval2 = TEXTPTR(cla) from #valied_t
WHERE WKEY = 'US19766120905B2A'
UPDATETEXT #valied_t.cla @ptrval2 null 0 #valied_t.cl @ptrval1
SELECT * FROM #valied_t WHERE WKEY = 'US19766120905B2A'