Soundex function error with charactor 'w' on Sqlserver

pumcpc 21 Reputation points

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



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)

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

Accepted answer
  1. Olaf Helper 43,166 Reputation points


    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,581 Reputation points

    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.


    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

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

    0 comments No comments