Subject RE: [firebird-support] CHECK for all numeric characters
Author David Johnson
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
>
>
>
>
>
>
>