Viewing Category: SQL Server  [clear category selection]

SQL Server Function: String to ASCII Decimal Values

I was working with a data issue today and I needed to verify that the values returned from a query were what I expected. I wanted to see the actual ASCII characters of a string in a column. So, I whipped this up (for SQL Server):

CREATE FUNCTION dbo.asciiChars (@string AS VARCHAR(MAX)) RETURNS VARCHAR(MAX) AS BEGIN   DECLARE @result VARCHAR(MAX);   DECLARE @char CHAR(1);     SET @result = '';     WHILE LEN(@string) > 0   BEGIN     SET @char = LEFT(@string, 1);     SET @result = @result + CONVERT(VARCHAR(3), ASCII(@char)) + ' ';     SET @string = SUBSTRING(@string, 2, LEN(@string));   END   RETURN (RTRIM(@result)); END

Running the function converts a string into a space-separated list of decimal ASCII values, like so:

SELECT dbo.asciiChars('foo');   102 111 111

Enjoy.