在 SQL Server 中将 unicode 字符串转换为 ascii

问题描述

如何将字符串'????' 转换为'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://www.51sjk.com/Upload/Articles/1/0/339/339437_20221213104102077.jpg 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