问题描述
我正在尝试将 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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!