Re: PL/SQL

Jonathan G Gennick (gennick_at_worldnet.att.net) wrote:
: sam s <samuels_at_singnet.com.sg> wrote:
:
: >Hi there!
: > i'm interested to know if there is a procedure or function
: > to check for a value and prompt an error msg if the value
: > does not fall in the range of A-Z and 0-9, leaving out all
: > the special characters?
: >thanks!
: >regards
: >Sam

create or replace function isalphanumeric (str in varchar2) return boolean is

Begin

If nvl(length(replace(translate(str,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',

                                  'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'), 
				  'X', NULL)), 0) > 0 Then
	Return True;
Else
	Return False;

End;
/

If you want to check for [a-z][A-Z][0-9], then, use upper(str) in call to translate.
Hope this helps,

• Ranga

  ---

  If it doesn't work, don't worry; if it did, you are out of a job!

  ---

  Ranga Chakravarthi e-mail: ranga_at_one.net

  ---