Soundex function error with charactor 'w' on Sqlserver

pumcpc 21 Reputation points
2021-07-16T06:49:00.603+00:00

Hello,guys,
I'm running Soundex() on sqlserver, but the output is confusing:

SELECT SOUNDEX( 'wah' )

0000

Would it not be a letter like 'w........'?
I have tried any other letter starts with 'w' and the outcome is the same 0000.
Letters start without 'w' (ie 'a') is correct.
I have tried on Sqlserver2008R2 and sqlserver2012,both the same.
Could anyone kind enough to tell me what's the matter,Thx!

My environment:
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64) Jun 11 2012 16:41:53 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
Chinese_PRC_CI_AS

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,483 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
{count} votes

Accepted answer
  1. Olaf Helper 40,156 Reputation points
    2021-07-16T07:53:35.537+00:00

    Chinese_PRC_CI_AS

    That's and the functionallity of SOUNDEX ist the reason. If you cast the collation like

    select SOUNDEX('wah' collate Chinese_PRC_CI_AS);  
    select SOUNDEX('wah' collate Latin1_General_CI_AS);  
    

    You get W000 as result.

    Additional see SOUNDEX (Transact-SQL) : "string sounds when spoken in English."
    Chinese words spoken in English ... I don't think you will get any result

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-07-16T08:28:21.473+00:00

    Hi @pumcpc

    Welcome to the microsoft TSQL Q&A forum!

    Soundex was specifically designed to handle the English pronunciation of names (mostly those of Western European origin) and does not function particularly well outside of that use.By design it works best on English sentences using the ASCII character set.

    You can try to change the collation to Latin1_General_CI_AS.
    The following link may help you:
    SQL Server's SoundEx function on non-Latin character sets?

    If you have any question, please feel free to let me know.

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  2. pumcpc 21 Reputation points
    2021-07-17T01:56:15.007+00:00

    Thanks to @Olaf Helper and @EchoLiu-MSFT , your answers help me out!
    It works with 'collate Latin1_General_CI_AS'

    0 comments No comments