SEARCH

SEARCH (전체 텍스트 검색)

한 개 이상의 테이블에서 지정된 열의 문자 데이터(텍스트)를 검색하며, VARIANT, OBJECT, ARRAY 열의 필드도 포함해요. 텍스트 분석기(analyzer)가 텍스트를 토큰(단어나 숫자 같은 이산적인 텍스트 단위)으로 나눠요. 분석기를 지정하지 않으면 기본 분석기가 적용돼요.

이 함수 사용에 대한 자세한 내용은 Using full-text search를 참고해요.

출처: Snowflake SQL Reference - SEARCH

본문

구문

SEARCH( <search_data>, '<search_string>'
  [, ANALYZER => '<analyzer_name>' ]
  [, SEARCH_MODE => { 'OR' | 'AND' | 'PHRASE' | 'EXACT' } ]
)

필수 인자

search_data

검색하려는 데이터로, 문자열 리터럴, 열 이름, 또는 VARIANT 열의 필드 경로의 쉼표로 구분된 목록으로 표현돼요. 검색 데이터는 단일 리터럴 문자열일 수도 있는데, 함수를 테스트할 때 유용할 수 있어요.

와일드카드 문자(*)를 지정할 수 있으며, 여기서 *는 함수 범위에 있는 모든 테이블의 모든 적격 열로 확장돼요. 적격 열은 VARCHAR(텍스트), VARIANT, ARRAY, OBJECT 데이터 타입을 가진 열이에요. VARIANT, ARRAY, OBJECT 데이터는 검색을 위해 텍스트로 변환돼요.

함수에 와일드카드를 전달할 때 와일드카드를 테이블 이름이나 별칭으로 한정할 수 있어요. 예를 들어 mytable이라는 이름의 테이블에서 모든 열을 전달하려면 다음을 지정해요:

(mytable.*)

또한 필터링에 ILIKE 및 EXCLUDE 키워드를 사용할 수 있어요:

• ILIKE는 지정된 패턴과 일치하는 열 이름을 필터링해요. 패턴은 하나만 허용돼요. 예: (* ILIKE 'col1%')
• EXCLUDE는 지정된 열과 일치하지 않는 열 이름을 필터링해요. 예: (* EXCLUDE col1), (* EXCLUDE (col1, col2))

이러한 키워드를 사용할 때 한정자가 유효해요. 다음 예시는 ILIKE 키워드를 사용해 mytable 테이블에서 col1% 패턴과 일치하는 모든 열을 필터링해요:

(mytable.* ILIKE 'col1%')

ILIKE와 EXCLUDE 키워드는 단일 함수 호출에서 결합할 수 없어요.

ILIKE 및 EXCLUDE 키워드에 대한 자세한 내용은 SELECT의 "Parameters" 섹션을 참고해요.

테이블을 조인하거나 UNION 집합 연산자를 사용해 여러 테이블이 범위에 있으면 둘 이상의 테이블에서 열을 검색할 수 있어요. 조인 또는 UNION 쿼리의 출력에서 모든 열을 검색하려면 한정되지 않은 * 와일드카드를 다음과 같이 사용할 수 있어요:

SELECT * FROM t AS T1
    JOIN t AS T2 USING (col1)
  WHERE SEARCH((*), 'string');

테이블을 조인할 때 특정 열을 검색하려면 열 이름을 한정해야 할 수 있어요(예: table2.colname). 또한 다음과 같이 한정된 * 와일드카드를 사용할 수 있어요:

SELECT * FROM t AS T1
    JOIN t AS T2 USING (col1)
  WHERE SEARCH((T2.*), 'string');

그러나 함수에 * 또는 table.*를 두 번 이상 지정할 수는 없어요. 이전 조인 예시에서 SEARCH((T1.*, T2.*), 'string')을 지정할 수 없어요. 이 구문은 오류를 반환해요.

*, table.*, 또는 여러 항목이 나열될 때 search_data 인자에 괄호가 필요해요. 예를 들어:

SEARCH((col1, col2, col3), 'string')
SEARCH((t1.*), 'string')
SEARCH((*), 'string')

괄호를 사용해 여러 항목을 구분하지 않으면 쉼표는 함수 인자 사이의 구분자로 파싱돼요.

VARIANT 데이터의 필드를 검색하려면 열 이름, 콜론 또는 점, 그리고 점으로 구분된 하위 필드를 지정해요. 예: colname:fieldname.subfieldname. 이러한 열에서 필드를 지정하는 방법에 대한 자세한 내용은 Traversing Semi-structured Data를 참고해요.

'<search_string>'

하나 이상의 검색어를 포함하는 VARCHAR 문자열이에요. 이 인자는 리터럴 문자열이어야 해요. 열 이름은 지원되지 않아요. 전체 문자열 주위에 작은따옴표 한 쌍을 지정해요. 개별 용어나 구문 주위에는 따옴표를 지정하지 마세요. 예를 들어 'blue red green'을 사용해요. 'blue' 'red' 'green'을 사용하지 마세요.

용어 목록은 SEARCH_MODE 인자에 OR 또는 AND가 설정되면 이접적(disjunctive) 또는 연접적(conjunctive)일 수 있어요. 그러나 'NO_OP_ANALYZER'가 사용되면 쿼리 문자열은 토큰화도, 이접적/연접적 의미론도 없이 정확히 그대로 일치돼요.

'NO_OP_ANALYZER'가 사용될 때를 제외하고 검색은 대소문자를 구분하지 않으므로, 문자열 'Once upon a time'에 대해 용어 'ONCE'를 검색하면 TRUE를 반환해요.

SEARCH_MODE 인자에 OR 또는 AND가 설정되면 검색어의 순서는 검색 데이터에서의 존재와 관련해 중요하지 않아요. SEARCH_MODE 인자에 PHRASE 또는 EXACT가 설정되면 검색어의 순서는 검색 데이터와 정확히 일치해야 해요.

선택 인자

ANALYZER => '<analyzer_name>'

텍스트 분석기의 이름이에요. 이름은 작은따옴표로 묶어야 해요.

분석기는 검색어(및 검색되는 열의 텍스트)를 토큰으로 나눠요. 검색 문자열에서 추출된 토큰과 검색되는 열이나 필드에서 추출된 토큰에 대한 일치 의미론(disjunctive, conjunctive, phrase, exact)은 SEARCH_MODE 인자의 값에 따라 달라져요.

분석기는 특정 구분자를 찾는 지점에서 나누어 문자열을 토큰화해요. 이러한 구분자는 결과 토큰에 포함되지 않으며, 빈 토큰은 추출되지 않아요.

이 파라미터는 다음 값 중 하나를 받아요:

• 'DEFAULT_ANALYZER' — 다음 구분자에 따라 텍스트를 토큰으로 나눠요: 공백(U+0020), [(U+005B), ](U+005D), ;(U+003B), <(U+003C), >(U+003E), ((U+0028), )(U+0029), {(U+007B), }(U+007D), |(U+007C), !(U+0021), ,(U+002C), '(U+0027), "(U+0022), *(U+002A), &(U+0026), ?(U+003F), +(U+002B), /(U+002F), :(U+003A), =(U+003D), @(U+0040), .(U+002E), -(U+002D), $(U+0024), %(U+0025), \(U+005C), _(U+005F), \n(U+000A), \r(U+000D), \t(U+0009).
• 'UNICODE_ANALYZER' — 공백과 특정 구두점 문자를 구분자로 취급하는 유니코드 분할 규칙에 따라 토큰화해요. 이러한 내부 규칙은 여러 언어의 자연어 검색을 위해 설계됐어요. 예를 들어 기본 분석기는 IP 주소의 점과 축약형의 아포스트로피를 구분자로 취급하지만, 유니코드 분석기는 그렇게 하지 않아요. 자세한 내용은 Using an analyzer to adjust search behavior 및 Unicode Text Segmentation 알고리즘을 참고해요.
• 'NO_OP_ANALYZER' — 데이터도 쿼리 문자열도 토큰화하지 않아요. 검색어는 대소문자 구분을 포함해 열이나 필드의 전체 텍스트와 정확히 일치해야 해요. 그렇지 않으면 SEARCH 함수는 FALSE를 반환해요. 쿼리 문자열이 여러 토큰을 포함하는 것처럼 보여도(예: 'sky blue') 열이나 필드는 전체 쿼리 문자열과 정확히 같아야 해요. 이 경우 'sky blue'만 일치하고 'sky'와 'blue'는 일치하지 않아요.

다양한 분석기의 동작에 대한 자세한 내용은 How search terms are tokenized를 참고해요.

SEARCH_MODE => { 'OR' | 'AND' | 'PHRASE' | 'EXACT' }

검색에 사용되는 의미론이에요. 이 인자를 다음 값 중 하나로 설정해요:

• 'OR' — 함수는 이접적 의미론을 사용해요. 검색되는 열이나 필드에서 추출된 토큰 중 하나라도 검색 문자열에서 추출된 토큰 중 하나 이상과 일치하면 일치가 있어요. 예를 들어 search_string 값이 'blue red green'이면 함수는 검색되는 열이나 필드 중 하나에 blue OR red OR green이 포함된 행에 대해 TRUE를 반환해요.
• 'AND' — 함수는 연접적 의미론을 사용해요. 검색되는 열이나 필드 중 적어도 하나에서 추출된 토큰이 검색 문자열에서 추출된 모든 토큰과 일치하면 일치가 있어요. 일치하는 토큰은 모두 하나의 열이나 필드에 있어야 해요. 여러 열이나 필드에 걸쳐 있을 수 없어요. 예를 들어 search_string 값이 'blue red green'이면 함수는 검색되는 열이나 필드 중 적어도 하나에 blue AND red AND green이 포함된 행에 대해 TRUE를 반환해요.
• 'PHRASE' — 함수는 구문 일치 의미론을 사용해요. 검색되는 열이나 필드 중 적어도 하나에서 추출된 토큰이 토큰의 순서와 인접성을 포함해 검색 문자열에서 추출된 모든 토큰과 일치하면 일치가 있어요. 일치 의미론은 다음 차이점을 제외하면 연접적 의미론과 같아요: 토큰의 순서가 정확히 일치해야 해요. 예를 들어 search_string 값이 'blue,red,green'이면 함수는 red,green,blue에 대해 FALSE를 반환해요. 검색 데이터에 추가 토큰이 섞여 있을 수 없어요. 예를 들어 search_string 값이 'blue,red,green'이면 함수는 blue,yellow,red,green에 대해 FALSE를 반환해요.
• 'EXACT' — 함수는 정확 일치 의미론을 사용해요. 검색되는 열이나 필드 중 적어도 하나에서 추출된 토큰이 구분자를 포함해 검색 문자열에서 추출된 모든 토큰과 정확히 일치하면 일치가 있어요. 일치 규칙은 구문 검색 의미론과 같지만 다음 차이점이 있어요: 토큰 사이의 구분자 문자열이 정확히 일치해야 해요. 예를 들어 search_string 값이 'blue,red,green'이면 함수는 검색되는 열이나 필드 중 적어도 하나에 blue,red,green이 포함된 행에 대해 TRUE를 반환해요. blue%red%green이나 blue, red, green 같은 변형에 대해서는 FALSE를 반환해요. 구분자가 search_string 값의 첫 번째 또는 마지막 문자이면 그 구분자는 일치를 위해 문자처럼 취급돼요. 따라서 첫 번째와 마지막 구분자의 왼쪽과 오른쪽 구분자로 인해 일치가 발생할 수 있어요. 예를 들어 search_string 값이 '[blue]'이면 함수는 foo [blue] bar, [[blue]], =[blue].에 대해 TRUE를 반환하지만 (blue)나 foo blue bar에 대해서는 FALSE를 반환해요.

모든 검색 모드에서 문자열은 왼쪽과 오른쪽에 구분자 기호로 구분되어야 해요. 예를 들어 search_string 값이 'blue,red,green'이면 함수는 -blue,red,green;에 대해 TRUE를 반환해요. darkblue,red,green이나 blue,red,greenish에 대해서는 FALSE를 반환해요. UNICODE_ANALYZER를 사용하면 정확 일치 의미론이 지원되지 않아요. DEFAULT_ANALYZER나 NO_OP_ANALYZER를 정확 일치 의미론과 함께 사용할 수 있지만, 일반적으로 이러한 검색 의미론은 DEFAULT_ANALYZER에 가장 적합해요. DEFAULT_ANALYZER와 함께 정확 일치 의미론을 사용한 검색은 다음과 같이 동등성 검색이나 NO_OP_ANALYZER를 사용한 전체 텍스트 검색과 다르게 동작해요:

• 동등성 검색은 열 값이 술어와 정확히 같을 때(문자 대소문자 포함) 검색 문자열 발생을 둘러싼 추가 텍스트 없이 행을 일치시켜요.
• NO_OP_ANALYZER를 사용한 전체 텍스트 검색은 대소문자를 구분하고 추가 텍스트를 허용하지 않는다는 점에서 동등성 검색과 유사해요.
• DEFAULT_ANALYZER를 사용한 정확 일치 의미론 검색은 열 값을 토큰화해요. 검색 문자열 발생의 왼쪽과 오른쪽에 토큰 구분자로 구분되는 한 추가 토큰을 허용해요. 검색은 대소문자를 구분하지 않아요.

기본값: 'OR'

반환 값

BOOLEAN을 반환해요:

• SEARCH_MODE 인자에 지정된 의미론에 따라 search_string 토큰이 search_data 토큰과 일치하면 값은 TRUE예요.
• 이 인자 중 하나가 NULL이면 NULL을 반환해요.
• 그렇지 않으면 FALSE를 반환해요.

사용상 주의사항

• SEARCH 함수는 VARCHAR, VARIANT, ARRAY, OBJECT 데이터에만 작동해요. search_data 인자가 이러한 데이터 타입의 데이터를 포함하지 않으면 함수는 오류를 반환해요. search_data 인자가 지원되는 데이터 타입과 지원되지 않는 데이터 타입의 데이터를 모두 포함하면 함수는 지원되는 데이터 타입의 데이터를 검색하고 지원되지 않는 데이터 타입의 데이터는 조용히 무시해요.
• ALTER TABLE 명령을 사용해 SEARCH 함수 호출의 대상이 되는 열에 FULL_TEXT 검색 최적화를 추가할 수 있어요. 예: ALTER TABLE lines ADD SEARCH OPTIMIZATION ON FULL_TEXT (play, character, line). 자세한 내용은 enable FULL_TEXT search optimization을 참고해요.

검색어가 토큰화되는 방법

다음 표는 입력 검색어가 토큰으로 분할되는 몇 가지 예시를 보여줘요. 이는 사용된 분석기가 적용하는 규칙에 따라 달라져요. 표에서 쉼표는 토큰이 분할되는 위치를 나타내요.

입력	DEFAULT_ANALYZER	UNICODE_ANALYZER	NO_OP_ANALYZER
10.210.158.44	10 , 210 , 158 , 44	10.210.158.44	10.210.158.44
192.0.2.0/24	192 , 0 , 2 , 24	192.0.2.0 , 24	192.0.2.0/24
high-tech	high , tech	high , tech	high-tech
Bob's Burgers	bob , s , burgers	bob's , burgers	Bob's Burgers
Three spaces	three , spaces	three , spaces	Three spaces
[email protected]	docs , snowflake , com	docs , snowflake.com	[email protected]
/opt/homebrew/README.md	opt , homebrew , readme , md	opt , homebrew , readme.md	/opt/homebrew/README.md

예시

다음 예시들은 SEARCH 함수를 사용하는 다양한 방법을 보여줘요. 간단한 사용부터 더 복잡한 사용 사례까지:

• 리터럴에 대한 일치
• 열 참조에 대한 일치
• 한 열에 대한 WHERE 절 검색
• 여러 열에 대한 WHERE 절 검색
• 테이블의 모든 적격 열에 대한 와일드카드 검색
• SELECT 목록의 와일드카드 검색
• 조인된 테이블의 적격 열에 대한 와일드카드 검색
• UNION 하위 쿼리 출력에 대한 와일드카드 검색
• 여러 검색 문자열과 일치하는 행 찾기
• phrase-match 및 exact-match 의미론으로 행 찾기
• 조인의 VARIANT 및 VARCHAR 데이터 검색
• 분석기를 사용한 검색 동작 조정
• 예상 오류 사례의 예시

리터럴에 대한 일치

SEARCH 함수의 가장 간단한 예시는 문자열 리터럴에 대한 TRUE 또는 FALSE 테스트예요. 첫 번째 예시는 비교가 대소문자를 구분하지 않으므로 첫 번째와 두 번째 인자의 리터럴이 일치하기 때문에 TRUE를 반환해요.

SELECT SEARCH('king', 'KING');

+-----------------------------+
| SEARCH('KING','KING')       |
|-----------------------------|
| True                        |
+-----------------------------+

두 번째 예시는 토큰 32가 첫 번째 인자에 지정된 리터럴 5.1.33에 나타나지 않으므로 FALSE를 반환해요.

SELECT SEARCH('5.1.33', '32');

+-----------------------------+
| SEARCH('5.1.33','32')       |
|-----------------------------|
| False                       |
+-----------------------------+

열 참조에 대한 일치

이 예시는 테이블의 열을 첫 번째 인자로 사용해요. 함수는 검색어 중 하나(king)가 문자 열에 존재하므로 TRUE를 반환해요. 용어 목록은 SEARCH_MODE 인자의 기본값이 'OR'이므로 이접적이에요.

SELECT SEARCH(character, 'king queen'), character
  FROM lines
  WHERE line_id=4;

+---------------------------------+---------------+
| SEARCH(CHARACTER, 'KING QUEEN') | CHARACTER     |
|---------------------------------+---------------|
| True                            | KING HENRY IV |
+---------------------------------+---------------+

다음 예시는 이전 예시와 유사하지만, SEARCH_MODE 인자가 'AND'로 설정되어 검색 의미론이 연접적이에요. 함수는 검색어 중 하나(king)만 문자 열에 존재하므로 FALSE를 반환해요. queen 용어는 검색 데이터에 나타나지 않아요.

샘플 데이터

SEARCH 함수의 쿼리를 실행하려면 먼저 SEARCH 함수의 샘플 데이터 생성을 참고해요.

더 알아보기

• String & binary functions (Full-Text Search) — 전체 텍스트 검색 함수 모음
• Using full-text search — 전체 텍스트 검색 사용