RE: How Can I find the ascii value of a char in the stored procedure?

Posted in 1999

Jonathan Leffler — Mon, 17 May 1999 17:13:37 -0700 (PDT) — source: Informix-list mailing list archive (1991-1998)

On Mon, 17 May 1999, Girish Kagrana wrote: >But how I will insert those characters whose ascii value is between 1 to 3= 2 >(that is my prime requirement) 1. Are you sure you don't want space ' ' ASCII(32) to be valid? 2. You won't; you'll insert only those from 33 ('!') through 255 (=FF, y-um= laut), or maybe 33..126 ('~'). Then your validation code will check to see whe= ther the return value from ORD(c) is NULL; if it is, the input character is i= nvalid. This assumes you are using a 7.3 engine so you can sensibly do variable substrings on character strings. Otherwise, you'll be forced to do something nasty in your SPL, but that's outside the immediate scope of my answer -- but not outside the scope of your problem. Hence the PS below. There might be a much better way of handling all this. And I note that not all locales will necessarily let you insert all values from control-A throu= gh y-umlaut into the ASCII table; and you will not be able to insert a row for ASCII NUL ('\\0') unless you allow the Value column to accept an SQL NULL, and you'll then have to do some special case coding in the stored procedure= s to handle that. Yours, Jonathan Leffler (jleffler@informix.com) #include <quotes/shakespeare.h> Guardian of DBD::Informix v0.60 (v0.61_02) -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn --------------------------------------------------------------------------- PS: One interesting little sidelight on testing the following procedure exhaustively shows that IDS/UDO v9.14.UC4 on Solaris 2.6 no longer has the limit of 255 characters in a literal string -- I tested with a 260 character literal (5 repetitions of both lower case and upper case alphabet) without problems, much to my surprise. PPS: I believe there are functionally equivalent stored procedures at the IIUG web site - http://www.iiug.org - as well as a general substr() procedure. There's no special magic to the choice of 16 in the code below; it just makes the code nicely symmetric. I have not done performance measurements to justify it -- and you could generate a 255-way list of (very boring) ELIF clauses to avoid the recursive call to char_at(). CREATE PROCEDURE char_at(str VARCHAR(255), pos SMALLINT) RETURNING CHAR(1); =09DEFINE c CHAR(1); =09IF LENGTH(str) < pos OR pos <=3D 0 THEN =09=09LET c =3D NULL; =09ELIF pos <=3D 16 THEN =09=09IF pos =3D 1 THEN LET c =3D str[ 1]; =09=09ELIF pos =3D 2 THEN LET c =3D str[ 2]; =09=09ELIF pos =3D 3 THEN LET c =3D str[ 3]; =09=09ELIF pos =3D 4 THEN LET c =3D str[ 4]; =09=09ELIF pos =3D 5 THEN LET c =3D str[ 5]; =09=09ELIF pos =3D 6 THEN LET c =3D str[ 6]; =09=09ELIF pos =3D 7 THEN LET c =3D str[ 7]; =09=09ELIF pos =3D 8 THEN LET c =3D str[ 8]; =09=09ELIF pos =3D 9 THEN LET c =3D str[ 9]; =09=09ELIF pos =3D 10 THEN LET c =3D str[10]; =09=09ELIF pos =3D 11 THEN LET c =3D str[11]; =09=09ELIF pos =3D 12 THEN LET c =3D str[12]; =09=09ELIF pos =3D 13 THEN LET c =3D str[13]; =09=09ELIF pos =3D 14 THEN LET c =3D str[14]; =09=09ELIF pos =3D 15 THEN LET c =3D str[15]; =09=09ELIF pos =3D 16 THEN LET c =3D str[16]; =09=09END IF; =09ELIF pos <=3D 32 THEN LET c =3D char_at(str[ 17, 32], pos - 1 * 16); =09ELIF pos <=3D 48 THEN LET c =3D char_at(str[ 33, 48], pos - 2 * 16); =09ELIF pos <=3D 64 THEN LET c =3D char_at(str[ 49, 64], pos - 3 * 16); =09ELIF pos <=3D 80 THEN LET c =3D char_at(str[ 65, 80], pos - 4 * 16); =09ELIF pos <=3D 96 THEN LET c =3D char_at(str[ 81, 96], pos - 5 * 16); =09ELIF pos <=3D 112 THEN LET c =3D char_at(str[ 97,112], pos - 6 * 16); =09ELIF pos <=3D 128 THEN LET c =3D char_at(str[113,128], pos - 7 * 16); =09ELIF pos <=3D 144 THEN LET c =3D char_at(str[129,144], pos - 8 * 16); =09ELIF pos <=3D 160 THEN LET c =3D char_at(str[145,160], pos - 9 * 16); =09ELIF pos <=3D 176 THEN LET c =3D char_at(str[161,176], pos - 10 * 16); =09ELIF pos <=3D 192 THEN LET c =3D char_at(str[177,192], pos - 11 * 16); =09ELIF pos <=3D 208 THEN LET c =3D char_at(str[193,208], pos - 12 * 16); =09ELIF pos <=3D 224 THEN LET c =3D char_at(str[209,224], pos - 13 * 16); =09ELIF pos <=3D 240 THEN LET c =3D char_at(str[225,240], pos - 14 * 16); =09ELIF pos <=3D 255 THEN LET c =3D char_at(str[241,255], pos - 15 * 16); =09ELSE LET c =3D NULL; =09END IF; =09RETURN c; END PROCEDURE; >> -----Original Message----- >> From:=09Jonathan Leffler [SMTP:jleffler@informix.com] >> Sent:=09Monday, May 17, 1999 3:11 PM >> To:=09Girish Kagrana >> Cc:=09Informix NewsGroup >> Subject:=09Re: FW: How Can I find the ascii value of a char in the >> stored proced ure? >>=20 >> On Mon, 17 May 1999, Girish Kagrana wrote: >> >Could you please help in this issue. >>=20 >> There isn't a very clean solution. I guess the best you can do is >> create a table of ASCII characters and codes: >>=20 >> CREATE TABLE ASCII >> ( >> Code SMALLINT NOT NULL CHECK (Code BETWEEN 0 AND 255) UNIQUE, >> Value CHAR(1) NOT NULL UNIQUE >> ) >> INSERT INTO ASCII VALUES(65, 'A'); >> INSERT INTO ASCII VALUES(66, 'B');>> ...boring... >>=20 >> You can then use this table to implement an ASCII or an ORD function: >>=20 >> CREATE PROCEDURE ASCII(i SMALLINT) RETURNING CHAR(1);>> DEFINE c CHAR(1); >> FOREACH SELECT Value INTO c FROM ASCII WHERE Code =3D i >> RETURN c; >> END FOREACH; >> LET c =3D NULL; >> RETURN c; >> END PROCEDURE; >>=20 >> CREATE PROCEDURE ORD(c CHAR(1)) RETURNING SMALLINT;>> DEFINE i SMALLINT; >> FOREACH SELECT Code INTO i FROM ASCII WHERE Value =3D c >> RETURN i; >> END FOREACH; >> LET i =3D NULL; >> RETURN i; >> END PROCEDURE; >>=20 >> I think that's as good as you can do unless you have IDS/UDO (aka IUS). >>=20 >> >> -----Original Message----- >> >> From: Girish Kagrana [SMTP:Girish_Kagrana@Vantive.COM] >> >> Sent: Friday, May 14, 1999 7:32 PM >> >> To: informix-list@iiug.org >> >> Subject: How Can I find the ascii value of a char in the stored >> procedure? >> >> >> >> >> >> How Can I find the ascii value of a char in the stored procedure? >> >> >> >> My requirement: >> >> To check the character is valid character or not, >> >> The invalid character set for my requirement is, whose ascii value is >> >> between 1 to 32 >> >> >> >> Since ascii function is not available in Informix(7.3 UC3) stored >> >> procedure , >> >> the only option I see is to hardcode those character and then >> >> compare, >> >> >> >> How Can I hard code those characters in the stored procedure, Is it >> >> possible with escape sequence ?