T-SQL function to get ASCII character values

Question

I am using a T-SQL block to dump ascii characters stored in a database column. I know this is easy to do in Oracle using the DUMP () function. I am not familiar with SQL Server sytax, but I am using something like this.

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 related to @ output2, it will not print @ output2 correctly. The idea is to get all ascii values ​​returned as a single string, instead of getting a string for each character. Am I doing something wrong?

Answer 1

Change the data types for @output and @ output2 to varchar. Replace DataLength with Len. Char is a fixed length and does not increase when adding lines at the end of a line.

Answer 2

If you are looking for a single line, this is probably the easiset path (based on Cyberkiwi answer)

 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