本文介绍了将 Varchar 转换为 Ascii的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在尝试将 VARCHAR 字段的内容转换为可以被 3rd 方轻松引用的唯一数字.

I'm trying to convert the contents of a VARCHAR field to be unique number that can be easily referenced by a 3rd party.

如何将 varchar 转换为等效的 ascii 字符串?在 TSQL 中?ASCII() 函数可转换单个字符,但如何转换整个字符串?

How can I convert a varchar to the ascii string equivalent? In TSQL? The ASCII() function converts a single character but what can I do to convert an entire string?

我试过使用

CAST(ISNULL(ASCII(Substring(RTRIM(LTRIM(PrimaryContactRegion)),1,1)),'')AS VARCHAR(3))
+ CAST(ISNULL(ASCII(Substring(RTRIM(LTRIM(PrimaryContactRegion)),2,1)),'')AS VARCHAR(3))

....但是这看起来很乏味,很愚蠢,而且如果我有很长的字符串就真的不起作用.或者如果更好,我将如何在 SSRS 中做同样的事情?

....but this is tedious, stupid looking, and just doesn't really work if I had long strings. Or if it is better how would I do the same thing in SSRS?

推荐答案

尝试这样的事情:

DECLARE @YourString   varchar(500)

SELECT @YourString='Hello World!'

;WITH AllNumbers AS
(
    SELECT 1 AS Number
    UNION ALL
    SELECT Number+1
        FROM AllNumbers
        WHERE Number<LEN(@YourString)
)
SELECT
       (SELECT
            ASCII(SUBSTRING(@YourString,Number,1))
            FROM AllNumbers
            ORDER BY Number
            FOR XML PATH(''), TYPE
       ).value('.','varchar(max)') AS NewValue
       --OPTION (MAXRECURSION 500) --<<needed if you have a string longer than 100

输出:

NewValue
---------------------------------------
72101108108111328711111410810033

(1 row(s) affected)

只是为了测试一下:

;WITH AllNumbers AS
(
    SELECT 1 AS Number
    UNION ALL
    SELECT Number+1
        FROM AllNumbers
        WHERE Number<LEN(@YourString)
)
SELECT SUBSTRING(@YourString,Number,1),ASCII(SUBSTRING(@YourString,Number,1)),* FROM AllNumbers

输出:

                 Number
---- ----------- -----------
H    72          1
e    101         2
l    108         3
l    108         4
o    111         5
     32          6
W    87          7
o    111         8
r    114         9
l    108         10
d    100         11
!    33          12

(12 row(s) affected)

另外,你可能想使用这个:

Also, you might want to use this:

RIGHT('000'+CONVERT(varchar(max),ASCII(SUBSTRING(@YourString,Number,1))),3)

将所有 ASCII 值强制转换为 3 位数字,根据您的使用情况,我不确定这是否有必要.

to force all ASCII values into 3 digits, I'm not sure if this is necessary based on your usage or not.

每个字符使用 3 位数字输出:

Output using 3 digits per character:

NewValue
-------------------------------------
072101108108111032087111114108100033

(1 row(s) affected)

这篇关于将 Varchar 转换为 Ascii的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

06-02 17:08
查看更多