[[["わかりやすい","easyToUnderstand","thumb-up"],["問題の解決に役立った","solvedMyProblem","thumb-up"],["その他","otherUp","thumb-up"]],[["わかりにくい","hardToUnderstand","thumb-down"],["情報またはサンプルコードが不正確","incorrectInformationOrSampleCode","thumb-down"],["必要な情報 / サンプルがない","missingTheInformationSamplesINeed","thumb-down"],["翻訳に関する問題","translationIssue","thumb-down"],["その他","otherDown","thumb-down"]],["最終更新日 2025-09-05 UTC。"],[],[],null,["# Perform a substring search\n\n| **Note:** This feature is available with the Spanner Enterprise edition and Enterprise Plus edition. For more information, see the [Spanner editions overview](/spanner/docs/editions-overview).\n\n\u003cbr /\u003e\n\nIn addition to full token matching, Spanner\n[search indexes](/spanner/docs/full-text-search/search-indexes)\nsupport substring searches. This page describes how to perform a substring\nsearch as part of a [full-text search](/spanner/docs/full-text-search) in\nSpanner.\n\nSubstring searches have the following characteristics:\n\n- Case insensitive, discards most punctuation, and normalizes whitespace.\n- No Chinese, Japanese, Korean (CJK) segmentation, since partial CJK queries often segment incorrectly.\n- For multiple search terms, the result must contain a substring from each term. For example, `'happ momen'` matches `\"happy moment\"`, because both substrings are found in the text. It doesn't match `\"happy day\"`.\n\n**Examples**\n\nFor a substring search, use the [`TOKENIZE_SUBSTRING`](/spanner/docs/reference/standard-sql/search_functions#tokenize_substring)\nfunction in the `TOKENLIST` column definition, as shown in the following DDL\nexample: \n\n### GoogleSQL\n\n CREATE TABLE Albums (\n AlbumId STRING(MAX) NOT NULL,\n AlbumTitle STRING(MAX),\n AlbumTitle_Tokens TOKENLIST AS (TOKENIZE_SUBSTRING(AlbumTitle)) HIDDEN\n ) PRIMARY KEY(AlbumId);\n\n### PostgreSQL\n\nThis example uses\n[`spanner.tokenize_substring`](/spanner/docs/reference/postgresql/search_functions#tokenize_substring). \n\n CREATE TABLE albums (\n albumid character varying NOT NULL,\n albumtitle character varying,\n albumtitle_tokens spanner.tokenlist\n GENERATED ALWAYS AS (spanner.tokenize_substring(albumtitle)) VIRTUAL HIDDEN,\n PRIMARY KEY(albumid));\n\nIn the SQL query, use the [`SEARCH_SUBSTRING`](/spanner/docs/reference/standard-sql/search_functions#search_substring) function in the `WHERE` clause. For\nexample, the following query matches an album with title \"happy\" from the table\ncreated in the previous example: \n\n### GoogleSQL\n\n SELECT Album\n FROM Albums\n WHERE SEARCH_SUBSTRING(AlbumTitle_Tokens, 'happ');\n\n### PostgreSQL\n\nThis example uses\n[`spanner.search_substring`](/spanner/docs/reference/postgresql/functions-and-operators#search_functions). \n\n SELECT album\n FROM albums\n WHERE spanner.search_substring(albumtitle_tokens, 'happ');\n\n`TOKENIZE_SUBSTRING` generates *[n-grams](https://en.wikipedia.org/wiki/N-gram)*\nfor each token and stores these n-grams in the search index. The minimum and\nmaximum length of n-grams to generate are configured through optional arguments.\n\nSubstring search indexes can use 10-30x more storage as full-text indexes over\nthe same data, because the tokenization produces a lot more tokens. This is\nespecially true if as the difference between `ngram_size_min` and\n`ngram_size_max` grows. Substring queries also use more resources to execute.\n\nLike [`TOKENIZE_FULLTEXT`](/spanner/docs/reference/standard-sql/search_functions#tokenize_fulltext),\nyou can configure `TOKENIZE_SUBSTRING` to use specific types of content.\n\nEnable a relative substring search\n----------------------------------\n\nIn addition to the basic substring search,\n[`SEARCH_SUBSTRING`](/spanner/docs/reference/standard-sql/search_functions#search_substring)\nsupports the relative search mode. A relative search refines substring search\nresults.\n\nTo enable the relative search mode, set the `relative_search_types` parameter of\n[`TOKENIZE_SUBSTRING`](/spanner/docs/reference/standard-sql/search_functions#tokenize_substring)\nto a non-empty array with elements of supported relative search types.\n\nWhen relative search is enabled in tokenization, `SEARCH_SUBSTRING` can perform\nqueries with the following relative search types:\n\n- `phrase`: matches contiguous substrings\n\n **Examples**\n\n- `value_prefix`: matches contiguous substrings and the match has to\n start at the beginning of the value. This is conceptually similar to the\n `STARTS_WITH` function for case and whitespace normalized strings.\n\n **Examples**\n\n- `value_suffix`: matches contiguous substrings and the match has to match at\n the end of the value. This is conceptually similar to the `ENDS_WITH`\n function for case and whitespace normalized strings.\n\n **Examples**\n\n- `word_prefix:` like `value_prefix`, but the string has to match at a term\n boundary (rather than a value boundary).\n\n **Examples**\n\n- `word_suffix`: like `value_suffix`, but the string has to match at the end\n of a term boundary.\n\n **Examples**\n\nWhat's next\n-----------\n\n- Learn about [full-text search queries](/spanner/docs/full-text-search/query-overview).\n- Learn how to [rank search results](/spanner/docs/full-text-search/ranked-search).\n- Learn how to [paginate search results](/spanner/docs/full-text-search/paginate-search-results).\n- Learn how to [mix full-text and non-text queries](/spanner/docs/full-text-search/mix-full-text-and-non-text-queries).\n- Learn how to [search multiple columns](/spanner/docs/full-text-search/search-multiple-columns)."]]