SOUNDEX (Transact-SQL)SOUNDEX (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

傳回四個字元的 (SOUNDEX) 代碼來評估兩個字串的相似度。Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

SOUNDEX ( character_expression )  

引數Arguments

character_expressioncharacter_expression
為字元資料的英數運算式Is an alphanumeric expression of character data. character_expression 可以是常數、變數或資料行。character_expression can be a constant, variable, or column.

傳回類型Return Types

varcharvarchar

RemarksRemarks

SOUNDEX 會根據字串發音,將英數字串轉換為四個字元的代碼。SOUNDEX converts an alphanumeric string to a four-character code that is based on how the string sounds when spoken. 代碼的第一個字元是 character_expression 的第一個字元,轉換成大寫。The first character of the code is the first character of character_expression, converted to upper case. 代碼的第二到第四個字元是數字,代表運算式中的字母。The second through fourth characters of the code are numbers that represent the letters in the expression. 字母 A、E、I、O、U、H、W 和 Y 除非是字串的第一個字母,否則會忽略它們。The letters A, E, I, O, U, H, W, and Y are ignored unless they are the first letter of the string. 如果需要產生四個字元的代碼,則在尾端加入零。Zeroes are added at the end if necessary to produce a four-character code. 如需 SOUNDEX 代碼的詳細資訊,請參閱 Soundex 索引系統For more information about the SOUNDEX code, see The Soundex Indexing System.

不同字串的 SOUNDEX 代碼可以相比較,來查看字串發音相似度。SOUNDEX codes from different strings can be compared to see how similar the strings sound when spoken. DIFFERENCE 函數會在兩個字串上執行 SOUNDEX,並傳回整數,表示這些字串的 SOUNDEX 代碼相似度。The DIFFERENCE function performs a SOUNDEX on two strings, and returns an integer that represents how similar the SOUNDEX codes are for those strings.

SOUNDEX 會區分定序。SOUNDEX is collation sensitive. 字串函數可以是巢狀函數。String functions can be nested.

SOUNDEX 相容性SOUNDEX Compatibility

在舊版 SQL ServerSQL Server 中,SOUNDEX 函數套用 SOUNDEX 規則的子集。In previous versions of SQL ServerSQL Server, the SOUNDEX function applied a subset of the SOUNDEX rules. 在資料庫相容性層級 110 或更高層級下,SQL ServerSQL Server 套用一組更完整的規則。Under database compatibility level 110 or higher, SQL ServerSQL Server applies a more complete set of the rules.

升級到相容性層級 110 或更高層級之後,您可能需要重建使用 SOUNDEX 函式的索引、堆積或 CHECK 條件約束。After upgrading to compatibility level 110 or higher, you may need to rebuild the indexes, heaps, or CHECK constraints that use the SOUNDEX function.

  • 在執行 ALTER TABLE <table> REBUILD 陳述式重建堆積之前,無法查詢包含使用 SOUNDEX 定義之保存的計算資料行的堆積。A heap that contains a persisted computed column defined with SOUNDEX cannot be queried until the heap is rebuilt by running the statement ALTER TABLE <table> REBUILD.

  • 在升級後,會停用使用 SOUNDEX 定義的 CHECK 條件約束。CHECK constraints defined with SOUNDEX are disabled upon upgrade. 若要啟用條件約束,請執行 ALTER TABLE <table> WITH CHECK CHECK CONSTRAINT ALL 陳述式。To enable the constraint, run the statement ALTER TABLE <table> WITH CHECK CHECK CONSTRAINT ALL.

  • 如果索引 (包括索引檢視表) 包含使用 SOUNDEX 定義之保存的計算資料行,在執行 ALTER INDEX ALL ON <object> REBUILD 陳述式重建索引之前,將無法查詢這類索引。Indexes (including indexed views) that contain a persisted computed column defined with SOUNDEX cannot be queried until the index is rebuilt by running the statement ALTER INDEX ALL ON <object> REBUILD.

範例Examples

下列範例會顯示 SOUNDEX 函數和相關的 DIFFERENCE 函數。The following example shows the SOUNDEX function and the related DIFFERENCE function. 在第一個範例中,傳回所有子音的標準 SOUNDEX 值。In the first example, the standard SOUNDEX values are returned for all consonants. 傳回 SOUNDEXSmithSmythe,會傳回相同的 SOUNDEX 結果,因為所有母音、y 字母、雙重字母和 h 字母都不包括在內。Returning the SOUNDEX for Smith and Smythe returns the same SOUNDEX result because all vowels, the letter y, doubled letters, and the letter h, are not included.

-- Using SOUNDEX  
SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe');  

以下為結果集:Here is the result set. 針對 Latin1_General 定序有效。Valid for a Latin1_General collation.

  
----- -----   
S530  S530    
  
(1 row(s) affected)  

DIFFERENCE 函數會比較 SOUNDEX 模式結果的差異。The DIFFERENCE function compares the difference of the SOUNDEX pattern results. 下列範例會顯示母音不同的兩個字串。The following example shows two strings that differ only in vowels. 傳回的差異是 4,這是最低的可能差異。The difference returned is 4, the lowest possible difference.

-- Using DIFFERENCE  
SELECT DIFFERENCE('Smithers', 'Smythers');  
GO  

以下為結果集:Here is the result set. 針對 Latin1_General 定序有效。Valid for a Latin1_General collation.

-----------   
4             
  
(1 row(s) affected)  

在下列範例中,字串的子音不同;因此,傳回的差異是 2,差異比較大。In the following example, the strings differ in consonants; therefore, the difference returned is 2, the greater difference.

SELECT DIFFERENCE('Anothers', 'Brothers');  
GO  

以下為結果集:Here is the result set. 針對 Latin1_General 定序有效。Valid for a Latin1_General collation.

-----------   
2             
  
(1 row(s) affected)  

另請參閱See Also

DIFFERENCE (Transact-SQL) DIFFERENCE (Transact-SQL)
字串函數 (Transact-SQL) String Functions (Transact-SQL)
ALTER DATABASE 相容性層級 (Transact-SQL)ALTER DATABASE Compatibility Level (Transact-SQL)