SQL Server Performance

How to convert invoice total into a Chinese wording?

Discussion in 'SQL Server 2008 General Developer Questions' started by seemun, Sep 1, 2009.

  1. seemun New Member

    Dear all,How to convert invoice total into a Chinese wording.
    Example:
    USD 176.00 --> 美元壹佰柒拾陸
    USD 176.00 --> 人民币壹佰柒拾陸
    rgds,sm
  2. davidfarr Member


    I am reasonably certain that this is not possible, unless you write your own function to do it.
    The main issue here is that invoice amounts are almost always a money data type, or least a data type that is numeric.
    The chinese language requires Kanji characters which is only supported by Unicode (nchar, nvarchar and ntext) data types.
    Mathematical totals can only be done on numeric data types. SQL Server cannot return a data type conversion and Kanji translation by itself.
    You would need to write your own function to do this, by first calculating the invoice total as a numeric calculation and then passing the Total to the function.
    SELECT dbo.fnConvertChinese(sum(InvoiceValue)) as InvoiceTotal from InvoiceTable
    In the function you would need to map numeric values to Chinese Kanji characters and dynamicaly build the string. I dont know the chinese language well enough to help you any further.
  3. seemun New Member

    I had tried to build the function in SQL and the output is ???????, cannot display the wording in Chinese.
    Is anyone have build the sample coding in SQL which successfully return Chinese wording, please share me the coding.
    Please assist.
  4. davidfarr Member

    To change the ???? to Chinese font, you would need to CAST N' the value as an NVARCHAR
    Run the examples below (which are Japanese i think) to see what I mean.

    SELECT CAST('これは☆ です。' AS NVARCHAR(MAX))
    -- the result will be ???? ???
    SELECT CAST(N'これは☆ です。' AS NVARCHAR(MAX))
    -- the result will be 'これは☆ です。'
    SELECT N'これは☆ です。'
    -- the result will be 'これは☆ です。'
    Note that the query result in your query window might look like little connected squares, but if you paste that result back into your query window or into Micorosft Word then you will see it as Kanji text.
    This just means that your client application (Windows forms, Internet Explorer, etc.) must also support Kanji characters in unicode in order to display the result correctly.

    (Thanks and credit to Quatrei Quorizawa for the above examples)

Share This Page