在 SQL Server 中将 unicode 字符串转换为 ascii [英] Convert unicode string to ascii in SQL Server

问题描述

如何将字符串'۱۳۹۴' 转换为'1394'?

我尝试更改排序规则但不起作用.

请注意，我在 C# 中从外部设备读取数据.

解决方案

我在网上搜索后尝试解决这个问题我得出的结论是解决这个问题的最好方法是函数

ALTER FUNCTION [dbo].[udf_ReplaceArabicNumbers](@str NVARCHAR(1000))回报 NVARCHAR(2000)作为开始声明@i INT = 1而@i<=LEN(@str)开始声明@val NVARCHAR(1)SET @val = SUBSTRING(@str, @i, 1)声明@newchar NVARCHAR(1)SET @newchar = CASE(@val)WHEN N'۱' THEN 1WHEN N'۲' THEN 2WHEN'۳' THEN 3WHEN'۴' THEN 4WHEN'۵' THEN 5WHEN'۶' THEN 6WHEN N'۷' THEN 7WHEN'۸' THEN 8WHEN'۹' THEN 9WHEN N'۰' THEN 0结尾SET @str = REPLACE(@str, @val, @newchar)设置@i+=1;结尾返回@str结尾

并调用这个函数

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

我参考这个网站

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

解决方案

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

这篇关于在 SQL Server 中将 unicode 字符串转换为 ascii的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！