Before we go ahead about these functions first I will introduce little bit about ASCII

ASCII stands for American Standard Code for Information Interchange. Computers can only understand numbers, so an ASCII code is the numerical representation of a character such as ‘a’ or ‘@’ or an action of some sort. ASCII was developed a long time ago and now the non-printing characters are rarely used for their original purpose

These are divided into three categories listed below in the table

ASCII control characters (character code 0-31)

The first 32 characters in the ASCII-table are unprintable control codes and are used to control peripherals such as printers.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

So the ASCII function in TSQL returns the (ASCII code)value of  the first character of string expression

Syntax ASCII( string expression)

Char function does this in reverse converts the ASCII code value  to an equivalent character

Syntax CHAR(integer expression)

Now we have the basics of the ASCII and CHAR let’s have some fun here

a simple task is we have to write a trigger which should not allow a  column in the  table to accept any invalid characters like “ã,ô,….” it should simply throw an error message saying invalid characters

if you look at the extended ascii codes table you will notice all the ASCII codes from  128 to 255  have invalid characters which we do not want them to be inserted in to the table

so now we have a table  DBO.PRODUCT_CATALOG and the column is the name

in this we will exact similar logic we just used in the above ASCII() , CHAR() demo

CODE:

create

TRIGGER PRODUCT_INVALIDCHAR ON dbo.PRODUCT_CATALOG INSTEAD OF INSERT,UPDATE

AS

BEGIN

DECLARE @POSITION INT= 0

DECLARE @STRING VARCHAR(100)=(SELECT NAME FROM inserted)

WHILE @POSITION <LEN(@STRING) BEGIN

if(ASCII(SUBSTRING(@STRING,@POSITION+1,@POSITION+2))>124

 AND

ASCII(SUBSTRING(@STRING,@POSITION+1,@POSITION+2))<= 255)

BEGIN

PRINT‘INVALID CHARACTER , PLEASE INSERT A VALID CAHRACTER’

RETURN

END

ELSE

BEGINSET @POSITION = @POSITION + 1

ENDEND

INSERT INTO dbo.PRODUCT_CATALOG

VALUES(@STRING)

END