전체 텍스트 검색 full text search

USE Northwind
GO
IF OBJECT_ID('fulltest') IS NOT NULL
    DROP TABLE fulltest
GO

CREATE TABLE fulltest
(
    r_id    int    NOT NULL
                    CONSTRAINT [PK_fulltest] PRIMARY KEY -- ROW ID
    , u_name    varchar(10)    NOT NULL
    , u_profile    text    NULL
)


INSERT INTO fulltest VALUES (1, '김기영', '서울상계동거주함')
INSERT INTO fulltest VALUES (2, '손호성', '서울대입구지하철역근처에거주')
INSERT INTO fulltest VALUES (3, '조영민', '역시 서울 상계동에 삽니다')
INSERT INTO fulltest VALUES (4, '박세희', '음.. 어디사는지기억이......')
INSERT INTO fulltest VALUES (5, '박성준', '경기도광명시가거주지나거의대부분은건개입구에있음')
INSERT INTO fulltest VALUES (6, '허미영', '부천서강남까지거의매일여행을함')
INSERT INTO fulltest VALUES (7, '양창호', '사무실은그의방이다, 퇴근을안함')
INSERT INTO fulltest VALUES (8, '탁노준', '통신두절또는사무실에만있음')
INSERT INTO fulltest VALUES (9, '최문용', '부천에거주하는또한사람')
INSERT INTO fulltest VALUES (10, '최영서', '부천에거주하는또한사람의딸')

--EXEC sp_configure



--전체 텍스트 검색을 위하여 데이터베이스 활성화 시키기
EXEC sp_fulltext_database 'ENABLE'
GO

--전체 텍스트 카탈로그 생성
EXEC sp_fulltext_catalog 'fulltest_catalog', 'create'
GO

--전체 텍스트 검색을 원하는 테이블 등록
EXEC sp_fulltext_table 'fulltest', 'create', 'fulltest_catalog', 'PK_fulltest'
GO

--전체 텍스트 검색을 원하는 컬럼 등록
EXEC sp_fulltext_column 'fulltest', 'u_profile', 'add'
GO

--전문 텍스트 생성을 위한 활성화
EXEC sp_fulltext_table 'fulltest', 'activate'
GO

--전체 텍스트 인덱스를 전체 텍스트 카탈로그에 채우기
EXEC sp_fulltext_catalog 'fulltest_catalog', 'start_full'
GO


--CONTAINS
SELECT * FROM fulltest WHERE contains(u_profile, '"서울 상계동"');

SELECT * FROM fulltest WHERE contains(u_profile, '"서울*"');

SELECT * FROM fulltest WHERE contains(u_profile, 'FORMSOF(INFLECTIONAL, "거주")');

SELECT * FROM fulltest WHERE contains(u_profile, '"서울*" ~ "거주*"')

SELECT * FROM fulltest WHERE contains(u_profile, '"서울*" AND "거주*"')

SELECT * FROM fulltest WHERE contains(u_profile, 'ISABOUT("부천*", "서울*")')

SELECT * FROM fulltest WHERE contains(u_profile, 'ISABOUT("부천*" WEIGHT(1.0), "서울*" WEIGHT(.2))')


--FREETEXT
--전체 텍스트 검색에 사용되는 키워드로 완전한 문장의 집합으로 검색할 경우 사용된다.
--FREETEXT 키워드로 문장을 조건으로 전달하면 내부적으로 전달된 문장을 단어와 구절로 분해하여
--적절한 순위에 대한 가중치를 주어 검색을 진행한다.
--CONTAINS 키워드에 비해 정확성이 떨어진다.
SELECT * FROM fulltest WHERE FREETEXT(u_profile, '상계동에 거주하는 사람');


--CONTAINSTABLE
--CONTAINSTABLE과 FREETEXTTABLE은 CONTAINS와 FREETEXT 키워드와 약간 다르다.
--CONTAINSTABLE은 검색한 단어를 포함한 열의 인덱스의 키 값과 순위를 반환한다.
--키 값은 KEY라는 컬럼으로, 순위는 RANK라는 컬럼으로 반환한다.
--CONTAINSTABLE은 하나의 테이블로 반환되며 FROM 절에 사용된다.
SELECT * FROM CONTAINSTABLE(fulltest, u_profile, 'ISABOUT("부천*", "서울*")')

SELECT a.r_id, a.u_name, a.u_profile, b.RANK
FROM fulltest AS a INNER JOIN
    CONTAINSTABLE(fulltest, u_profile, 'ISABOUT("부천*", "서울*")') AS b
    ON a.r_id = b.[key]
ORDER BY b.RANK DESC


--FREETEXTTABLE
--FREETEXTTABLE도 CONTAINSTABLE과 같이 FROM절에 사용되어 결과를 검색한다.
SELECT * FROM FREETEXTTABLE(fulltest, u_profile, '상계동에 거주하는 사람');

SELECT a.r_id, a.u_name, a.u_profile, b.RANK
FROM fulltest AS a INNER JOIN
    FREETEXTTABLE(fulltest, u_profile, '상계동에 거주하는 사람') AS b
        ON a.r_id = b.[key]
ORDER BY b.RANK DESC


--전체 텍스트 인덱스에 대한 정보 수집
sp_help_fulltext_catalogs fulltest_catalog
sp_help_fulltext_tables
sp_help_fulltext_columns