T-SQL Function to get ASCII values of characters stored
Solution 1
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.
Solution 2
If you're looking for a single row this is probably the easiset way to go (building on Cyberkiwi's 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
![abhi](https://i.stack.imgur.com/9M9p7.png?s=256&g=1)
abhi
I am a passionate developer who's well versed in a few technologies and always learning new ones. I have been coding (GW-BASIC) since 8th Grade. I am currently developing on the Salesforce Platform. I am also dabbling with angularJs with the objective of replacing the ASP .NET MVC architecture with an HTML5.0 - AngularJS- WebApi architecture. I have used the simple-salesforce library and Python to create test scripts for my salesforce org.
Updated on July 10, 2022Comments
-
abhi almost 2 years
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.
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?
-
abhi over 13 yearsThanks. I am now trying to build a function out of this so that it can mimic Oracle's DUMP() function