SQL: ASCII characters code for each character of a field

The code below can be used to obtain a list of the ASCII character codes for each character of a field:

 
SET NOCOUNT ON;

CREATE TABLE #Characters
( ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
  [Character] NCHAR(1),
  [ASCIIValue] INT
)

DECLARE @counter int = 1;
DECLARE @colString NVARCHAR(MAX);

SELECT @colString = FIELD --Replace FIELD with your column name
FROM dbo.TABLE --Replace TABLE with your table name
WHERE ID = 123 --Replace ID with any unique filter

WHILE @counter <= DATALENGTH(@colString)
BEGIN
	INSERT INTO #Characters ([Character], [ASCIIValue])
    SELECT
		CHAR(ASCII(SUBSTRING(@colString, @counter, 1))) as [Character],
		ASCII(SUBSTRING(@colString, @counter, 1)) as [ASCIIValue]

	SET @counter = @counter + 1
END
SELECT * FROM #Characters WHERE ASCIIValue IS NOT NULL ORDER BY ASCIIValue
DROP TABLE #Characters
SET NOCOUNT OFF
GO

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.