ASCII() Function

Hey look at that! A function that returns the number code of a specified character! Doesn’t this sound familiar? This function works just as the CHAR() function does except the complete opposite. ASCII stands for American Standard Code for Information Interchange. When going down to the most basic levels of computing, computers run off of the good old binary code (a series of 1s or 0s, yes or no) which also means that computers only understand numbers. ASCII was developed to communicate text to computers.

SELECT  ASCII(‘R’) AS R, ASCII(‘r’) AS r

They are broken down into 3 categories:

1. ASCII control characters (code 0-32)
   • These characters are control codes used to control peripherals
2. ASCII printable characters (code 33-127)
   • These represent letters, digits, punctuation marks, and some other symbols. All of these will be found on your common keyboard.
3. ASCII extended printable characters (128-255)
   • The extended codes will be based off of whatever collation your database is set to. The following table is based off of the ISO Latin-1 collation.

Let’s have a little fun with this knowledge! The first part will be using a WHILE loop to grab each character in a string and show the ASCII and CHAR codes for each character.

DECLARE @String VARCHAR(50) = ‘SQLBLOG’;
DECLARE @Location INT = 0;

WHILE @Location < LEN(@String)
BEGIN

SELECT ASCII(SUBSTRING(@String, @Location + 1, @Location + 2)) AS [ASCII Code] ,
CHAR(ASCII(SUBSTRING(@String, @Location + 1, @Location + 2))) AS [CHAR Code];

SET @Location = @Location + 1;

END;

This gives us individual results containing the ASCII number and the character it refers to in the designated string (“SQLBLOG”). Since there are seven characters in this string there will be seven results returned. This is a good practice exercise but it’s not very practical for every day use. But what if we wanted to use this same logic to determine what characters cannot go into a table? Well besides using a complicated check constraint let’s use a trigger that’s activated on insert or update of the table.

CREATE TRIGGER [dbo].[InvalidCharacterInName] ON [dbo].[Employees]
INSTEAD OF INSERT, UPDATE
AS
BEGIN
DECLARE @Location INT = 0;
DECLARE @String VARCHAR(100) = ( SELECT [Inserted].[EmployeeName]
FROM [Inserted]
);

WHILE @Location < LEN(@String)
BEGIN
IF ( ASCII(SUBSTRING(@String, @Location + 1, @Location + 2)) > 124
AND ASCII(SUBSTRING(@String, @Location + 1, @Location + 2)) <= 255
)
BEGIN
RAISERROR(‘There is an invalid character in the string you are entering. Please remove invalid characters.’,10,1);
RETURN;
END;

ELSE
BEGIN

SET @Location = @Location + 1;

END;
END;

INSERT INTO dbo.Employees
( EmployeeName )
VALUES ( @String );

END;

Now when we run the following code we should receive an error stating that there is an invalid character and it needs to be fixed.

INSERT INTO dbo.Employees
( EmployeeName )
VALUES ( CONCAT(‘Green’,CHAR(212),’Lantern’) );

Obviously the invalid character is the CHAR(212) or Ô. Removing this will allow the transaction to complete and add the name to the table.

Before:

Code:

INSERT INTO dbo.Employees
( EmployeeName )
VALUES ( ‘Green Lantern’ );

After: