ASCII() , CHAR() — String Functions

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.

ASCII printable characters (character code 32-127)

Codes 32-127 are common for all the different variations of the ASCII table, they are called printable characters, represent letters, digits, punctuation marks, and a few miscellaneous symbols. You will find almost every character on your keyboard. Character 127 represents the command DEL.

 

The extended ASCII codes (character code 128-255)

There are several different variations of the 8-bit ASCII table. The table below is according to ISO 8859-1, also called ISO Latin-1. Codes 129-159 contain the Microsoft® Windows Latin-1 extended characters.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s