Convert Unicode String To Ascii In Sql Server

How to convert string '۱۳۹۴' to '1394'? I try change collation but does not work. Please note that I read data from external device in C# .

Solution 1:

i have tried to solve problem after search on internet i came to the conclusion the best way to solve this problem is function

ALTERFUNCTION [dbo].[udf_ReplaceArabicNumbers]
    (@str NVARCHAR(1000))
    RETURNS NVARCHAR(2000)
ASBEGINDECLARE@iINT=1
    WHILE @i<=LEN(@str)

    BEGINDECLARE@val NVARCHAR(1)
        SET@val=SUBSTRING(@str, @i, 1)
            DECLARE@newchar NVARCHAR(1)
            SET@newchar=CASE(@val)
                    WHEN N'۱'THEN1WHEN N'۲'THEN2WHEN N'۳'THEN3WHEN N'۴'THEN4WHEN N'۵'THEN5WHEN N'۶'THEN6WHEN N'۷'THEN7WHEN N'۸'THEN8WHEN N'۹'THEN9WHEN N'۰'THEN0ENDSET@str= REPLACE(@str, @val, @newchar)
        SET@i+=1;
    ENDRETURN@strEND

and call to this function

select[dbo].[udf_ReplaceArabicNumbers] (N'۱۳۹۴')

i refer this site http://unicode-table.com/en/ with the help of UNICODE we can get HTML-Code and use in our Program

select'&#'+cast (UNICODE(N'۱')as nvarchar(10)) +';',
        '&#'+cast (UNICODE(N'۳')as nvarchar(10)) +';',
        '&#'+cast (UNICODE(N'۹')as nvarchar(10)) +';',
        '&#'+cast (UNICODE(N'۴')as nvarchar(10)) +';'

and result would be

Solution 2:

Based on the properties of the unicode code points numbers, you could use something like this:

DECLARE@ArabicNumber NVARCHAR(4)
SET@ArabicNumber=N'۱۳۹۴'SELECTLEFT(CONVERT(NVARCHAR(4),CONVERT(VARBINARY(8), 
        CONVERT(BIGINT,CONVERT(VARBINARY(8),CONVERT(NCHAR(4),@ArabicNumber))) 
        &CONVERT(VARBINARY(8),REPLICATE(0x0F00,4))
        ^CONVERT(VARBINARY(8),REPLICATE(0x3000,4))
    )),LEN(@ArabicNumber))

This works if the input string contains only numbers and it is limited to 4 characters, to fit in a bigint, for the bitwise operations. For longer strings, you should use a WHILE loop to process each character.