Convert Unicode string containing Arabic characters to ASCII in SQL Server

Question

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# .

Answer 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

ALTER FUNCTION [dbo].[udf_ReplaceArabicNumbers]
    (@str NVARCHAR(1000))
    RETURNS NVARCHAR(2000)
AS
BEGIN

    DECLARE @i INT = 1
    WHILE @i<=LEN(@str)

    BEGIN
        DECLARE @val NVARCHAR(1)
        SET @val = SUBSTRING(@str, @i, 1)
            DECLARE @newchar NVARCHAR(1)
            SET @newchar = CASE(@val)
                    WHEN N'۱' THEN 1
                    WHEN N'۲' THEN 2
                    WHEN N'۳' THEN 3
                    WHEN N'۴' THEN 4
                    WHEN N'۵' THEN 5
                    WHEN N'۶' THEN 6
                    WHEN N'۷' THEN 7
                    WHEN N'۸' THEN 8
                    WHEN N'۹' THEN 9
                    WHEN N'۰' THEN 0
                END
        SET @str = REPLACE(@str, @val, @newchar)
        SET @i+=1;
    END

RETURN @str
END

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