Sunday, June 8, 2008

Check for Alpha Only

This is a way to restrict data entry to alpha if you are using a release below 10G. It's not particularly pretty, but it works.

I'm basically translating all alpha characters to spaces, and then trimming them out,which of course, leaves everything else. Then I check for a length. Remove the UPPER if you don't want lower case.
Create Table Testalpha
(Alphaonly Varchar2(10)
Constraint Ck_Alphaonly Check
(Length
(Trim
(Translate
(Upper(alphaonly),
'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
' '
)
)
) is null
)
);
In 10G, you can use the REGEXP functions:

Create Table Testalpha
(Alphaonly Varchar2(10)
Constraint Ck_Alphaonly Check
(Regexp_Like(AlphaOnly, '^[[:alpha:]]+$')
)
);

No comments:

Post a Comment