获取存储字符的 ASCII 值的 T-SQL 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4996060/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
T-SQL Function to get ASCII values of characters stored
提问by abhi
I am using a T-SQL block to get the dump of ascii characters stored in a database column. I know this is accomplished easily in Oracle using the DUMP() function. I am not taht familiar with SQL Server sytax, but I am using something like this.
我正在使用 T-SQL 块来获取存储在数据库列中的 ascii 字符的转储。我知道这可以在 Oracle 中使用 DUMP() 函数轻松完成。我不熟悉 SQL Server 语法，但我正在使用这样的东西。
SET NOCOUNT ON
-- Create the variables for the current character string position
-- and for the character string.
DECLARE @position int, @string char(15), @output char(1000), @output2 char(2000)
-- Initialize the variables.
SET @position = 1
SET @output2 = 'Start:'
SELECT @string = name from
location where location_type = 4405 and owner_id = 362
and location_id = 53183
WHILE @position <= DATALENGTH(@string)
BEGIN
SELECT @output = CAST(ASCII(SUBSTRING(@string, @position, 1)) AS CHAR)
+ ' ' + CHAR(ASCII(SUBSTRING(@string, @position, 1)))
PRINT @output
--SET @output2 = @output2 + '=' + @output
SET @position = @position + 1
END
--PRINT @output2
SET NOCOUNT OFF
GO
For some reason if I uncomment the code that relates to @output2, it won't print @output2 correctly. The idea is to get all the ascii values returned as a single line instead of getting a line for each character. Am I doing something wrong?
出于某种原因，如果我取消注释与 @output2 相关的代码，它将无法正确打印 @output2。这个想法是将所有 ascii 值作为一行返回，而不是为每个字符获取一行。难道我做错了什么？
采纳答案by Mikael Eriksson
Change the data types for @output and @output2 to varchar. Replace DataLength with Len. Char is fixed length and does not grow when you add strings at the end of the string.
将@output 和@output2 的数据类型更改为varchar。用 Len 替换 DataLength。Char 是固定长度，在字符串末尾添加字符串时不会增长。
回答by Conrad Frix
If you're looking for a single row this is probably the easiset way to go (building on Cyberkiwi's answer)
如果您正在寻找单行，这可能是最简单的方法（基于 Cyberkiwi 的答案）
DECLARE @string char(15),
@output1 varchar(1000),
@output2 varchar(1000)
SELECT @string = name
from location
where location_type = 4405 and owner_id = 362
and location_id = 53183
SET @output1 = ''
SET @output2 = ''
select
@output1 = @output1 + SUBSTRING(@string, number, 1) + ', ',
@output2 = @output2 + cast(ASCII(SUBSTRING(@string, number, 1)) as varchar) + ', '
from master..spt_values
where type='p' and number between 1 and LEN(@string)
order by number
PRINT @output1
PRINT @output2
