Interview Question of the Week #055 – How to Convert ASCII to DECIMAL or DECIMAL to ASCII?

Question: How do I turn an ASCII character into its decimal code, or a decimal code into a character? Use ASCII for the character’s code and CHAR for the reverse direction. The decimal number is an integer representation of a code, not a request to cast the text to the DECIMAL data type.

The original example spelled my name, PINAL, using five character codes. It is a small example worth keeping because you can check every position in both directions:

SELECT CHAR(80)+CHAR(73)+CHAR(78)+CHAR(65)+CHAR(76) AS SQLAuthorityAuthor;
SELECT V.Position,V.Character,ASCII(V.Character) AS DecimalCode
FROM (VALUES(1,'P'),(2,'I'),(3,'N'),(4,'A'),(5,'L'))
 AS V(Position,Character)
ORDER BY V.Position;
SELECT ASCII('PINAL') AS FirstCharacterOnly,
       UNICODE(N'æ') AS UnicodeCodePoint,NCHAR(230) AS UnicodeCharacter;

The first result is PINAL. The second gives P=80, I=73, N=78, A=65 and L=76, in that explicit position order. ASCII(‘PINAL’) returns only 80 because ASCII reads the leftmost character, not the whole string.

CHAR the function is not CHAR the data type

The original definition accidentally described fixed-length CHAR data instead of the CHAR function. The function takes an integer from 0 through 255 and returns its single-byte character under the current database’s encoding. An out-of-range or incomplete character code can return NULL.

ASCII is not a general Unicode conversion

The seven-bit ASCII characters used in PINAL are straightforward. For arbitrary multilingual text, do not treat ASCII as a Unicode code-point function. UNICODE and NCHAR are the corresponding Unicode tools; the last result demonstrates code point 230 and the character æ.

Code-page and UTF-8 encodings matter outside the ASCII range. A numeric value obtained from a byte is not automatically the character’s Unicode code point. Keep the representation explicit rather than relying on a round trip that works only for these five letters.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.