4

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
  • 3,082
  • 6
  • 47
  • 73
  • Do you mean by won't print @output2 correctly? What behaviour are you getting? – Phil C Feb 14 '11 at 19:03
  • Is there some control char that is written to Output2 and which cannot be printed? – Karl Feb 14 '11 at 19:18
  • @output2 will always print "Start:" which is what I am initializing it to. I am not sure about the control character as that is what I want to find out. – abhi Feb 14 '11 at 19:25

2 Answers2

6

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
Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
  • Thanks. I am now trying to build a function out of this so that it can mimic Oracle's DUMP() function – abhi Feb 14 '11 at 21:33
2

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.

Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281