SQL function we could use more often — ASCII () function with examples

6 min readApr 8, 2021

ASCII is an abbreviation for American Standard Code for Information Interchange, and to make things a bit more clear it is a character encoding standard for electronic communication. It was invented as a 7-bit character set (128 bytes), but as it became common to use 8-bit bytes to store each character in memory, the opportunity for extended 8-bit relatives of ASCII was provided. Also, as communication and computer technology has spread throughout the world, different variations of ASCII were developed to express non-English languages characters.

The first 32 codes of the ASCII table are reserved for unprintable control characters such as backspace, end of the text, escape, vertical or horizontal tab etc. Codes 32 to 127 are common for all other variations of the ASCI table and know as printable characters. There are 95 printable characters in total representing letters (uppercase and lowercase), digits, punctuation marks and a few miscellaneous symbols. The third group known as the extended ASCII codes represents eight-bit or larger encoding sets of additional characters. One of the most used encodings nowadays is Windows-1252 or CP-1252, which is being used by default in the legacy components of Microsoft Windows for the English language and many European languages. It is a single-byte character encoding of the Latin alphabet also called ISO Latin-1.

The table below shows the grouped list of ASCII characters, from ASCII code 00 to ASCI code 255.

SQL Server `ASCII()` and CHAR() syntax

ASCII and CHAR functions are often used together or in combination, because they are actually an inverted form of each other — the result of the ASCII function, when used as a parameter in CHAR function will bring the result that was used as a parameter in the ASCII function to get an initial result and vice versa.

In SQL the ASCII function returns the ASCII code, value/number that represents the specified character, and the syntax is as follows:

ASCII (string_expession)

A string can be a single character, expression or a column, but the ASCII function will always return the code value for the first character (leftmost character) in a string.

SELECT ASCII ('M') = 77
SELECT ASCII ('Medium article') = 77

In reverse to the ASCII function, the CHAR function returns the character value of the integer expression based on the ASCII code.

CHAR (integer_ expession)

If the integer expression is within the ASCII range (1–255), the CHAR function will bring the result, otherwise, the result will be NULL.

SELECT CHAR(77) = 'M'
SELECT CHAR(777) = NULL

SQL Server `ASCII()` function examples

The ASCII()function used standalone or in combination with the CHAR() function can be used in various useful cases such as generating a password, removing unwanted characters from a string, testing if a special character is inserted into a column etc. A few other examples are shown below.

Example 1. — print alphabet (upper case)

The first example will show how the ASCII() function can be used to generate (print) 26 alphabet characters. The output of the code below will bring 26 lines in two columns APLHA (A-Z) and COUNTER (1–26). The result from the CTE table must be limited to 100, otherwise, the statement will be terminated and an error will occur. In the code below the select statement has been limited to 26 rows, so this statement will bring the first 26 ASCII characters starting from ASCII (‘A’). It means that the last character will be ‘Z’, so all alphabet characters will be printed.

To print alphabet characters in lower case, the same code can be used but ASCII(‘A’) should be replaced with ASCII(‘a’).

Example 2. — print special ASCII characters

The next example will bring a similar output as the one above, but in this case, the Extended ASCII characters will be printed. To achieve this, the counter parameter has been set to 128, since this is the first number that represents an ASCII extended character. The print statement will be executed until the counter reaches 256, which has been set as the end of the ‘while loop’ because it represents the last Extended ASCII character.

In this example only theCHAR() function has been used.

Example 3. — remove special characters

The next example shows how special or “unwanted” characters can be removed from the string. Written in the form of a function, characters from the ASCII range 128–255 (Extended ASCII characters) will be removed whenever this function is called. If we want to apply this function on the string ‘Õ123ÖÞABCÕÕ11’, after removing special characters, the result will be ‘123ABC11’.

With a similar logic in mind, this function can be adjusted and used in many useful scenarios like generating a random string including/excluding specific characters, to check if the password contains specific characters, used as a trigger on the table to throw an error whenever an unwanted character is inserted in the column, etc.

Example 4. — create a full address based on street, city, and state info

In this example, using the ASCII function, a FULL_ADDRESS column will be populated based on STREET, CITY and STATE information from the table.

In this case, a FULL_ADDRESS will be formatted as ‘Street City, State’ using the ASCII function. To achieve that, the ‘Street City, State’ format has been shown as ‘A B, C’, where A represents a Street, B represents a City and C represents a State. The function will use ASCII values for characters A, B, and C, and replace them with the values that have been assigned to them. The logic that has been applied to this can be seen in the code below.

Two versions of the function that will create a Full Address are shown in Example 4 and Example 5. Both are bringing the same result and both are using the part of the code shown above as the core “converting” part of the function, but they are written in two different ways to show how ASCII functions can be used in various ways.

A similar approach can be used to create a ‘Full Name function’ based on First Name, Middle Name, Last Name, Salutation, or Title.

Example 4 — function version 1

Example 4 — function version 2

`ASCII() art`

Printable ASCII characters can be formed into a picture, meaning that and real text-based visual art can be created. That technique is called ASCII art and is often used when the transmission of the picture is not possible (typewriters, teleprinters, non-graphic computer terminals, printer separators), within the source code of computer programs for the representation of company or product logos, in signatures at the end of emails, etc. Probably the most common ASCII art picture is the smiley :-)
=) but it can get a lot more sophisticated than that. To comprehend the variety of ASCII characters in use, let’s finish this article with some real art made by using printable ASCII characters. Fascinating, isn’t it?

References:

_____ _   _    _    _   _ _  ____   _____  _   _ 
|_   _| | | |  / \  | \ | | |/ /\ \ / / _ \| | | |
  | | | |_| | / _ \ |  \| | ' /  \ V / | | | | | |
  | | |  _  |/ ___ \| |\  | . \   | || |_| | |_| |
  |_| |_| |_/_/   \_\_| \_|_|\_\  |_| \___/ \___/

SQL function we could use more often — ASCII () function with examples

SQL Server ASCII() and CHAR() syntax

SQL Server ASCII() function examples

ASCII() art

Written by Biljana Jelić

SQL Server `ASCII()` and CHAR() syntax

SQL Server `ASCII()` function examples

`ASCII() art`