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
(adsbygoogle = window.adsbygoogle || []).push({});
Sqljunkieshare 