Subject RE: [firebird-support] CHECK for all numeric characters
Author Rick Debay
I can't find transform in the fbudf or ib_udf libs. Am I missing
something?

Thanks, Rick DeBay

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of David Johnson
Sent: Thursday, September 01, 2005 2:01 PM
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] CHECK for all numeric characters

transform takes the data in the specified column and transforms all of
the characters that are in the first set of arguments to the
corresponding characters in the second set of characters.

For example:
transform ('abc123', '0123456789', ' ') would return 'abc ', and
transform ('abc123', 'cde', 'CDE') would return 'abC123'

In this case, all of the characters 0 through 9 are changed to spaces.
If there are any non-space characters left, RTRIM will return a string
that is more than 0 characters long.

Note that this mechanism can still be fooled by something with spaces
embedded in a set of digits, like '99 99'.


On Thu, 2005-09-01 at 13:43 -0400, Rick Debay wrote:
> I changed 'length' to 'strlen' and added the UDFs length and strlen.
> What's transform?
>
> -----Original Message-----
> From: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com] On Behalf Of David Johnson
> Sent: Thursday, September 01, 2005 1:25 PM
> To: firebird-support@yahoogroups.com
> Subject: Re: [firebird-support] CHECK for all numeric characters
>
> On Thu, 2005-09-01 at 18:26 +0200, Ivan Prenosil wrote:
> > > I'm creating a domain that's CHAR(11) and I'd like to add a check
> > > to
>
> > > make sure all the characters are numeric. I'm experiencing a
> > > brain freeze right now and can't come up with one. Can anyone
> > > suggest the
>
> > > appropriate CHECK statement(s)?
> >
> > CHECK (Value BETWEEN '00000000000' AND '99999999999')
> >
> > Ivan
> >
> >
>
> Not quite ... this will allow '0A'.
>
> I have recently had to coerce some character columns with garbage data

> into numerics in another SQL dialect. I believe that there are UDF's
> that will do this task:
>
> length(rtrim(transform(column, '0123456789', ' '))) = 0
>
>
>
> ------------------------ Yahoo! Groups Sponsor
> --------------------~--> Get Bzzzy! (real tools to help you find a
> job). Welcome to the Sweet Life.
> http://us.click.yahoo.com/A77XvD/vlQLAA/TtwFAA/67folB/TM
> --------------------------------------------------------------------~-
> >
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item on
> the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item on
> the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>



------------------------ Yahoo! Groups Sponsor --------------------~-->
Fair play? Video games influencing politics. Click and talk back!
http://us.click.yahoo.com/T8sf5C/tzNLAA/TtwFAA/67folB/TM
--------------------------------------------------------------------~->

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item on
the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links