Re: Verifying a variable only contains letters and not punctuation...

On Fri, 24 Oct 1997 18:10:51 GMT, lgmiller_at_elmrd.u-net.com (Graham Miller) wrote:

>"Bryan J. Gentile" <bgentile_at_bdsinc.com> wrote...
>
>| Is there a way in PL/SQL to check for all punctuation marks such as
>| commas, periods, etc. I will not know what the punctuation mark will be
>| therefore, I cannot really use the REPLACE(variable, ',', '') function.
>| I would rather use something more reliable. Is there something that
>| PL/SQL has to do this?
>|
>| Also, if I wanted to check that a variable of type NUMBER was only
>| numbers, how could I do that?
>
>Hello,
> The 'TRANSLATE' function can be very useful for this sort of
>checking. It converts characters between user defined character sets
>by position in the list. Any character not in the first list is copied
>unchanged. It is not possible to convert a character to null. If the

Actually, you can convert a character to null via TRANSLATE. E.g. TRANSLATE('A1B2C3','A0123456789','A') results in the string 'ABC'. This translate example removes all numeric digits from the source string.

>target charaset contains all the same characters then you are mapping
>a set of variable input characters to the same character! i.e the
>following command will convert various punctuation characters to a '?'
>character.
>
>
> TRANSLATE( UPPER(<String>),
> '.".,:;',
> '??????'
> )
>
>Example
>
>SQL> l
> 1 select
> 2 TRANSLATE( 'This is, a string. With various :;,. characters in
>it!',
> 3 '.".,:;',
> 4 '??????'
> 5 )
> 6* from dual
>SQL> /
>
>TRANSLATE('THISIS,ASTRING.WITHVARIOUS:;,.CHARACTERSINI
>
>------------------------------------------------------
>
>This is? a string? With various ???? characters in it!
>
>
>
>
>Then apply your replace function.
>
>
>
>Number Test:
>
> RTRIM(
> TRANSLATE( UPPER(<number string>),
> '0123456789',
> '9999999999'
> ), '9'
> )
> IS NULL
>
>OR
>
>
>CREATE OR REPLACE FUNCTION StrToNumber ( pStr in VARCHAR2)
> RETURN NUMBER IS
>
> BEGIN
> RETURN TO_NUMBER( pStr);
> EXCEPTION
> WHEN VALUE_ERROR THEN
> RETURN NULL;
> END;
>/
>
>graham (aka grumpy)

Bob Cunningham
bcunn_at_oanet.com
bcunn_at_compuserve.com Received on Sun Oct 26 1997 - 00:00:00 CDT