ISNUMERIC Scalar Function

Gary Blumenthal

ISNUMERIC Scalar Function
All,

I am trying to return records from a table that contain only numeric values
in a particular column. I have seen other databases have an ISNUMERIC()
scalar function. For example:

SELECT last_name, first_name
FROM gift_master
WHERE ISNUMERIC(naughty_nice) = 1

This will return all the records from gift_list where naught_nice is a valid
number. How can this be done in DB2 SQL?

Thanks in advance,

Gary B.



Sibimon Philip

Re: ISNUMERIC Scalar Function
(in response to Gary Blumenthal)
DB2 OS/390 does not have this function. But I created a user defined
function for this to do the same.

Thanks..sibi


-----Original Message-----
From: Blumenthal, Gary [mailto:[login to unmask email]
Sent: Monday, December 16, 2002 02:42 PM
To: [login to unmask email]
Subject: ISNUMERIC Scalar Function


All,

I am trying to return records from a table that contain only numeric values
in a particular column. I have seen other databases have an ISNUMERIC()
scalar function. For example:

SELECT last_name, first_name
FROM gift_master
WHERE ISNUMERIC(naughty_nice) = 1

This will return all the records from gift_list where naught_nice is a valid
number. How can this be done in DB2 SQL?

Thanks in advance,

Gary B.








James Campbell

Re: ISNUMERIC Scalar Function
(in response to Sibimon Philip)
write your own UDF to do the processing.

James Campbell

On 16 Dec 2002 at 15:42, Blumenthal, Gary wrote:

> All,
>
> I am trying to return records from a table that contain only numeric values
> in a particular column. I have seen other databases have an ISNUMERIC()
> scalar function. For example:
>
> SELECT last_name, first_name
> FROM gift_master
> WHERE ISNUMERIC(naughty_nice) = 1
>
> This will return all the records from gift_list where naught_nice is a valid
> number. How can this be done in DB2 SQL?
>
> Thanks in advance,
>
> Gary B.
>
>
>



Suresh Sane

Re: ISNUMERIC Scalar Function
(in response to James Campbell)
Gary,

Try this...

Assuming that the column is to contain all numerics (i.e. not numbers
followed by spaces etc), they are stored as x'F0', x'F1' etc. Using this,
we get:

WHERE HEX(naughty_nice) LIKE REPEAT('F_',5)

>>>I am assuming column is char(5). I tried to use the length derived from
>>>rtrim but it does LIKE does not like it. Maybe someone can improve on
>>>this solution.

HTH.

Suresh


>From: "Blumenthal, Gary" <[login to unmask email]>
>Reply-To: DB2 Data Base Discussion List <[login to unmask email]>
>To: [login to unmask email]
>Subject: ISNUMERIC Scalar Function
>Date: Mon, 16 Dec 2002 15:42:30 -0500
>
>All,
>
>I am trying to return records from a table that contain only numeric values
>in a particular column. I have seen other databases have an ISNUMERIC()
>scalar function. For example:
>
>SELECT last_name, first_name
>FROM gift_master
>WHERE ISNUMERIC(naughty_nice) = 1
>
>This will return all the records from gift_list where naught_nice is a
>valid
>number. How can this be done in DB2 SQL?
>
>Thanks in advance,
>
>Gary B.
>
>
>
>the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
>can


_________________________________________________________________
The new MSN 8: advanced junk mail protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail



teldb2kals

Re: ISNUMERIC Scalar Function
(in response to Suresh Sane)
Hi Gary,

Not sure how worthwhile an option this is, but you can maybe give it a try.

Assuming the column which you want to check is of CHAR type, you can create
a separate table TAB1 with an integer column, and load it with continuosly
increasing nos. starting from 0 till 9 or 99 or 999 or 9999, etc, depending
on the length of your column. You can then code your query as

SELECT last_name, first_name
FROM gift_master
WHERE STRIP(naughty_nice) in (SELECT char(col1) from tab1);

The STRIP function may be optional depending on your actual data.

Regards,
Kals

>
>>From: "Blumenthal, Gary" <[login to unmask email]>
>>Reply-To: DB2 Data Base Discussion List <[login to unmask email]>
>>To: [login to unmask email]
>>Subject: ISNUMERIC Scalar Function
>>Date: Mon, 16 Dec 2002 15:42:30 -0500
>>
>>All,
>>
>>I am trying to return records from a table that contain only numeric
values
>>in a particular column. I have seen other databases have an ISNUMERIC()
>>scalar function. For example:
>>
>>SELECT last_name, first_name
>>FROM gift_master
>>WHERE ISNUMERIC(naughty_nice) = 1
>>
>>This will return all the records from gift_list where naught_nice is a
>>valid
>>number. How can this be done in DB2 SQL?
>>
>>Thanks in advance,
>>
>>Gary B.
>>



Bernd Oppolzer

Re: ISNUMERIC Scalar Function
(in response to teldb2kals)
Another solution using BUILTIN scalar functions:

supposed, you have to test a CHAR(6) field called COL1 to have only
decimal digits in it. You could do this by

WHERE TRANSLATE (COL1, '1234567890N', 'NNNNNNNNNNX') = 'NNNNNN'

that is, you translate all digits to N, but a N in the original string to
something else. If you get six Ns, the original COL1 was numeric.

Hope this helps

Regards

Bernd



Am Mit, 18 Dez 2002 schrieben Sie:
> Hi Gary,
>
> Not sure how worthwhile an option this is, but you can maybe give it a try.
>
> Assuming the column which you want to check is of CHAR type, you can create
> a separate table TAB1 with an integer column, and load it with continuosly
> increasing nos. starting from 0 till 9 or 99 or 999 or 9999, etc, depending
> on the length of your column. You can then code your query as
>
> SELECT last_name, first_name
> FROM gift_master
> WHERE STRIP(naughty_nice) in (SELECT char(col1) from tab1);
>
> The STRIP function may be optional depending on your actual data.
>
> Regards,
> Kals
>
> >
> >>From: "Blumenthal, Gary" <[login to unmask email]>
> >>Reply-To: DB2 Data Base Discussion List <[login to unmask email]>
> >>To: [login to unmask email]
> >>Subject: ISNUMERIC Scalar Function
> >>Date: Mon, 16 Dec 2002 15:42:30 -0500
> >>
> >>All,
> >>
> >>I am trying to return records from a table that contain only numeric
> values
> >>in a particular column. I have seen other databases have an ISNUMERIC()
> >>scalar function. For example:
> >>
> >>SELECT last_name, first_name
> >>FROM gift_master
> >>WHERE ISNUMERIC(naughty_nice) = 1
> >>
> >>This will return all the records from gift_list where naught_nice is a
> >>valid
> >>number. How can this be done in DB2 SQL?
> >>
> >>Thanks in advance,
> >>
> >>Gary B.
> >>
>
>
>



Grace Chen

Re: ISNUMERIC Scalar Function
(in response to Bernd Oppolzer)
This is a wonderful solution. I tested it. However, it need a little bit
change. The correct syntax should be :
WHERE TRANSLATE (COL1, 'NNNNNNNNNNX', '1234567890N') = 'NNNNNN'

Grace

-----Original Message-----
From: Bernd Oppolzer [mailto:[login to unmask email]
Sent: Wednesday, December 18, 2002 4:35 AM
To: [login to unmask email]
Subject: Re: ISNUMERIC Scalar Function


Another solution using BUILTIN scalar functions:

supposed, you have to test a CHAR(6) field called COL1 to have only
decimal digits in it. You could do this by

WHERE TRANSLATE (COL1, '1234567890N', 'NNNNNNNNNNX') = 'NNNNNN'

that is, you translate all digits to N, but a N in the original string to
something else. If you get six Ns, the original COL1 was numeric.

Hope this helps

Regards

Bernd



Am Mit, 18 Dez 2002 schrieben Sie:
> Hi Gary,
>
> Not sure how worthwhile an option this is, but you can maybe give it a
try.
>
> Assuming the column which you want to check is of CHAR type, you can
create
> a separate table TAB1 with an integer column, and load it with continuosly
> increasing nos. starting from 0 till 9 or 99 or 999 or 9999, etc,
depending
> on the length of your column. You can then code your query as
>
> SELECT last_name, first_name
> FROM gift_master
> WHERE STRIP(naughty_nice) in (SELECT char(col1) from tab1);
>
> The STRIP function may be optional depending on your actual data.
>
> Regards,
> Kals
>
> >
> >>From: "Blumenthal, Gary" <[login to unmask email]>
> >>Reply-To: DB2 Data Base Discussion List <[login to unmask email]>
> >>To: [login to unmask email]
> >>Subject: ISNUMERIC Scalar Function
> >>Date: Mon, 16 Dec 2002 15:42:30 -0500
> >>
> >>All,
> >>
> >>I am trying to return records from a table that contain only numeric
> values
> >>in a particular column. I have seen other databases have an ISNUMERIC()
> >>scalar function. For example:
> >>
> >>SELECT last_name, first_name
> >>FROM gift_master
> >>WHERE ISNUMERIC(naughty_nice) = 1
> >>
> >>This will return all the records from gift_list where naught_nice is a
> >>valid
> >>number. How can this be done in DB2 SQL?
> >>
> >>Thanks in advance,
> >>
> >>Gary B.
> >>
>
>
>









**************************************************************************************************
The contents of this email and any attachments are confidential.
It is intended for the named recipient(s) only.
If you have received this email in error please notify the system manager or the
sender immediately and do not disclose the contents to any one or make copies.

** eSafe scanned this email for viruses, vandals and malicious content **
**************************************************************************************************



teldb2kals

Re: ISNUMERIC Scalar Function
(in response to Grace Chen)
Thats a pretty neat solution, Bernd. Just one addition though, we need to
translate spaces as well to "N", so that something like "1234 " might be
considered a valid number.

On second thoughts (just thinking as I typed this), that would then
consider a field of all spaces, or a field with spaces in the middle, to be
numeric. I am sure there must be a way out for that as well.

Regards,
Kals

On Wed, 18 Dec 2002 10:05:14 -0700, Grace Chen <[login to unmask email]>
wrote:

>This is a wonderful solution. I tested it. However, it need a little bit
>change. The correct syntax should be :
>WHERE TRANSLATE (COL1, 'NNNNNNNNNNX', '1234567890N') = 'NNNNNN'
>
>Grace
>
>-----Original Message-----
>From: Bernd Oppolzer [mailto:[login to unmask email]
>Sent: Wednesday, December 18, 2002 4:35 AM
>To: [login to unmask email]
>Subject: Re: ISNUMERIC Scalar Function
>
>
>Another solution using BUILTIN scalar functions:
>
>supposed, you have to test a CHAR(6) field called COL1 to have only
>decimal digits in it. You could do this by
>
> WHERE TRANSLATE (COL1, '1234567890N', 'NNNNNNNNNNX') = 'NNNNNN'
>
>that is, you translate all digits to N, but a N in the original string to
>something else. If you get six Ns, the original COL1 was numeric.
>
>Hope this helps
>
>Regards
>
>Bernd
>
>
>
>Am Mit, 18 Dez 2002 schrieben Sie:
>> Hi Gary,
>>
>> Not sure how worthwhile an option this is, but you can maybe give it a
>try.
>>
>> Assuming the column which you want to check is of CHAR type, you can
>create
>> a separate table TAB1 with an integer column, and load it with
continuosly
>> increasing nos. starting from 0 till 9 or 99 or 999 or 9999, etc,
>depending
>> on the length of your column. You can then code your query as
>>
>> SELECT last_name, first_name
>> FROM gift_master
>> WHERE STRIP(naughty_nice) in (SELECT char(col1) from tab1);
>>
>> The STRIP function may be optional depending on your actual data.
>>
>> Regards,
>> Kals
>>
>> >
>> >>From: "Blumenthal, Gary" <[login to unmask email]>
>> >>Reply-To: DB2 Data Base Discussion List <[login to unmask email]>
>> >>To: [login to unmask email]
>> >>Subject: ISNUMERIC Scalar Function
>> >>Date: Mon, 16 Dec 2002 15:42:30 -0500
>> >>
>> >>All,
>> >>
>> >>I am trying to return records from a table that contain only numeric
>> values
>> >>in a particular column. I have seen other databases have an ISNUMERIC
()
>> >>scalar function. For example:
>> >>
>> >>SELECT last_name, first_name
>> >>FROM gift_master
>> >>WHERE ISNUMERIC(naughty_nice) = 1
>> >>
>> >>This will return all the records from gift_list where naught_nice is a
>> >>valid
>> >>number. How can this be done in DB2 SQL?
>> >>
>> >>Thanks in advance,
>> >>
>> >>Gary B.
>> >>
>>
>>
>>
>the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can
>
>
>
>
the
>
>
>
>
>***************************************************************************
***********************
>The contents of this email and any attachments are confidential.
>It is intended for the named recipient(s) only.
>If you have received this email in error please notify the system manager
or the
>sender immediately and do not disclose the contents to any one or make
copies.
>
>** eSafe scanned this email for viruses, vandals and malicious content **
>***************************************************************************
***********************
>
>
>
the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can



Sibimon Philip

Re: ISNUMERIC Scalar Function
(in response to teldb2kals)
SQLserver consider space in the middle as non numeric. So I wrote UDF based
on that. Space in the either end is fine for numeric.

thanks...sibi


-----Original Message-----
From: Teldb2kals [mailto:[login to unmask email]
Sent: Wednesday, December 18, 2002 01:40 PM
To: [login to unmask email]
Subject: Re: ISNUMERIC Scalar Function


Thats a pretty neat solution, Bernd. Just one addition though, we need to
translate spaces as well to "N", so that something like "1234 " might be
considered a valid number.

On second thoughts (just thinking as I typed this), that would then
consider a field of all spaces, or a field with spaces in the middle, to be
numeric. I am sure there must be a way out for that as well.

Regards,
Kals

On Wed, 18 Dec 2002 10:05:14 -0700, Grace Chen <[login to unmask email]>
wrote:

>This is a wonderful solution. I tested it. However, it need a little bit
>change. The correct syntax should be :
>WHERE TRANSLATE (COL1, 'NNNNNNNNNNX', '1234567890N') = 'NNNNNN'
>
>Grace
>
>-----Original Message-----
>From: Bernd Oppolzer [mailto:[login to unmask email]
>Sent: Wednesday, December 18, 2002 4:35 AM
>To: [login to unmask email]
>Subject: Re: ISNUMERIC Scalar Function
>
>
>Another solution using BUILTIN scalar functions:
>
>supposed, you have to test a CHAR(6) field called COL1 to have only
>decimal digits in it. You could do this by
>
> WHERE TRANSLATE (COL1, '1234567890N', 'NNNNNNNNNNX') = 'NNNNNN'
>
>that is, you translate all digits to N, but a N in the original string to
>something else. If you get six Ns, the original COL1 was numeric.
>
>Hope this helps
>
>Regards
>
>Bernd
>
>
>
>Am Mit, 18 Dez 2002 schrieben Sie:
>> Hi Gary,
>>
>> Not sure how worthwhile an option this is, but you can maybe give it a
>try.
>>
>> Assuming the column which you want to check is of CHAR type, you can
>create
>> a separate table TAB1 with an integer column, and load it with
continuosly
>> increasing nos. starting from 0 till 9 or 99 or 999 or 9999, etc,
>depending
>> on the length of your column. You can then code your query as
>>
>> SELECT last_name, first_name
>> FROM gift_master
>> WHERE STRIP(naughty_nice) in (SELECT char(col1) from tab1);
>>
>> The STRIP function may be optional depending on your actual data.
>>
>> Regards,
>> Kals
>>
>> >
>> >>From: "Blumenthal, Gary" <[login to unmask email]>
>> >>Reply-To: DB2 Data Base Discussion List <[login to unmask email]>
>> >>To: [login to unmask email]
>> >>Subject: ISNUMERIC Scalar Function
>> >>Date: Mon, 16 Dec 2002 15:42:30 -0500
>> >>
>> >>All,
>> >>
>> >>I am trying to return records from a table that contain only numeric
>> values
>> >>in a particular column. I have seen other databases have an ISNUMERIC
()
>> >>scalar function. For example:
>> >>
>> >>SELECT last_name, first_name
>> >>FROM gift_master
>> >>WHERE ISNUMERIC(naughty_nice) = 1
>> >>
>> >>This will return all the records from gift_list where naught_nice is a
>> >>valid
>> >>number. How can this be done in DB2 SQL?
>> >>
>> >>Thanks in advance,
>> >>
>> >>Gary B.
>> >>
>>
>>
>>
>the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can
>
>
>
>
the
>
>
>
>
>***************************************************************************
***********************
>The contents of this email and any attachments are confidential.
>It is intended for the named recipient(s) only.
>If you have received this email in error please notify the system manager
or the
>sender immediately and do not disclose the contents to any one or make
copies.
>
>** eSafe scanned this email for viruses, vandals and malicious content **
>***************************************************************************
***********************
>
>
>
the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can








James Campbell

Re: ISNUMERIC Scalar Function
(in response to Sibimon Philip)
case
-- ensure only a single leading '+' or '-'
when strip(strip(strip(COL1),l,'+'),l,'-') <>
strip(strip(strip(COL1),l,'-'),l,'+')
then 'NOK'

-- not just '+' or '-'
when strip(strip(strip(COL1),l,'+'),l,'-') = ' '
then 'NOK'
-- not just '+' or '-' then dot
when strip(strip(strip(COL1),l,'+'),l,'-') = '.'
then 'NOK'

-- all characters (if any) between leading '+' or '-' and dot (or
end, if none) are digits
when translate('0'||substr(strip(strip(strip(COL1),l,'+'),l,'-')
,1,
posstr(strip(strip(strip(COL1),l,'+'),l,'-')
||'.','.')),' A','0123456789 ') <> ' '
then 'NOK'
-- all characters after dot (if any) and trailing spaces are digits
when translate(substr(strip(COL1) ||'00',
posstr(strip(COL1)||'.','.')+1)
,' A','0123456789 ') <> ' '
then 'NOK'
else 'OK
end

(written off the top of my head - sure to have errors, but you get
the idea)

of course, an exponential (+123.456E-7) is also a valid numeric but
will fail this test, so perhaps we need to expand this into ....



James Campbell


On 18 Dec 2002 at 15:29, Philip, Sibimon wrote:

> SQLserver consider space in the middle as non numeric. So I wrote UDF based
> on that. Space in the either end is fine for numeric.
>
> thanks...sibi
>
>
> -----Original Message-----
> From: Teldb2kals [mailto:[login to unmask email]
> Sent: Wednesday, December 18, 2002 01:40 PM
> To: [login to unmask email]
> Subject: Re: ISNUMERIC Scalar Function
>
>
> Thats a pretty neat solution, Bernd. Just one addition though, we need to
> translate spaces as well to "N", so that something like "1234 " might be
> considered a valid number.
>
> On second thoughts (just thinking as I typed this), that would then
> consider a field of all spaces, or a field with spaces in the middle, to be
> numeric. I am sure there must be a way out for that as well.
>
> Regards,
> Kals
>
> On Wed, 18 Dec 2002 10:05:14 -0700, Grace Chen <[login to unmask email]>
> wrote:
>
> >This is a wonderful solution. I tested it. However, it need a little bit
> >change. The correct syntax should be :
> >WHERE TRANSLATE (COL1, 'NNNNNNNNNNX', '1234567890N') = 'NNNNNN'
> >
> >Grace
> >
> >-----Original Message-----
> >From: Bernd Oppolzer [mailto:[login to unmask email]
> >Sent: Wednesday, December 18, 2002 4:35 AM
> >To: [login to unmask email]
> >Subject: Re: ISNUMERIC Scalar Function
> >
> >
> >Another solution using BUILTIN scalar functions:
> >
> >supposed, you have to test a CHAR(6) field called COL1 to have only
> >decimal digits in it. You could do this by
> >
> > WHERE TRANSLATE (COL1, '1234567890N', 'NNNNNNNNNNX') = 'NNNNNN'
> >
> >that is, you translate all digits to N, but a N in the original string to
> >something else. If you get six Ns, the original COL1 was numeric.
> >
> >Hope this helps
> >
> >Regards
> >
> >Bernd
> >
> >
> >
> >Am Mit, 18 Dez 2002 schrieben Sie:
> >> Hi Gary,
> >>
> >> Not sure how worthwhile an option this is, but you can maybe give it a
> >try.
> >>
> >> Assuming the column which you want to check is of CHAR type, you can
> >create
> >> a separate table TAB1 with an integer column, and load it with
> continuosly
> >> increasing nos. starting from 0 till 9 or 99 or 999 or 9999, etc,
> >depending
> >> on the length of your column. You can then code your query as
> >>
> >> SELECT last_name, first_name
> >> FROM gift_master
> >> WHERE STRIP(naughty_nice) in (SELECT char(col1) from tab1);
> >>
> >> The STRIP function may be optional depending on your actual data.
> >>
> >> Regards,
> >> Kals
> >>
> >> >
> >> >>From: "Blumenthal, Gary" <[login to unmask email]>
> >> >>Reply-To: DB2 Data Base Discussion List <[login to unmask email]>
> >> >>To: [login to unmask email]
> >> >>Subject: ISNUMERIC Scalar Function
> >> >>Date: Mon, 16 Dec 2002 15:42:30 -0500
> >> >>
> >> >>All,
> >> >>
> >> >>I am trying to return records from a table that contain only numeric
> >> values
> >> >>in a particular column. I have seen other databases have an ISNUMERIC
> ()
> >> >>scalar function. For example:
> >> >>
> >> >>SELECT last_name, first_name
> >> >>FROM gift_master
> >> >>WHERE ISNUMERIC(naughty_nice) = 1
> >> >>
> >> >>This will return all the records from gift_list where naught_nice is a
> >> >>valid
> >> >>number. How can this be done in DB2 SQL?
> >> >>
> >> >>Thanks in advance,
> >> >>
> >> >>Gary B.
> >> >>
> >>
> >>
> >>
> >the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
> can
> >
> >
> >
> >
> the
> >
> >
> >
> >
> >***************************************************************************
> ***********************
> >The contents of this email and any attachments are confidential.
> >It is intended for the named recipient(s) only.
> >If you have received this email in error please notify the system manager
> or the
> >sender immediately and do not disclose the contents to any one or make
> copies.
> >
> >** eSafe scanned this email for viruses, vandals and malicious content **
> >***************************************************************************
> ***********************
> >
> >
> >
> the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
> can
>
>
>
>
>
>
>
>



James Campbell

Re: ISNUMERIC Scalar Function
(in response to Bernd Oppolzer)
case
-- ensure only a single leading '+' or '-'
when strip(strip(strip(COL1),l,'+'),l,'-') <>
strip(strip(strip(COL1),l,'-'),l,'+')
then 'NOK'

-- not just '+' or '-'
when strip(strip(strip(COL1),l,'+'),l,'-') = ' '
then 'NOK'
-- not just '+' or '-' then dot
when strip(strip(strip(COL1),l,'+'),l,'-') = '.'
then 'NOK'

-- all characters (if any) between leading '+' or '-' and dot (or
end, if none) are digits
when translate('0'||substr(strip(strip(strip(COL1),l,'+'),l,'-')
,1,
posstr(strip(strip(strip(COL1),l,'+'),l,'-')
||'.','.')),' A','0123456789 ') <> ' '
then 'NOK'
-- all characters after dot (if any) and trailing spaces are digits
when translate(substr(strip(COL1) ||'00',
posstr(strip(COL1)||'.','.')+1)
,' A','0123456789 ') <> ' '
then 'NOK'
else 'OK
end

(written off the top of my head - sure to have errors, but you get
the idea)

of course, an exponential (+123.456E-7) is also a valid numeric but
will fail this test, so perhaps we need to expand this into ....



James Campbell


On 18 Dec 2002 at 15:29, Philip, Sibimon wrote:

> SQLserver consider space in the middle as non numeric. So I wrote UDF based
> on that. Space in the either end is fine for numeric.
>
> thanks...sibi
>
>
> -----Original Message-----
> From: Teldb2kals [mailto:[login to unmask email]
> Sent: Wednesday, December 18, 2002 01:40 PM
> To: [login to unmask email]
> Subject: Re: ISNUMERIC Scalar Function
>
>
> Thats a pretty neat solution, Bernd. Just one addition though, we need to
> translate spaces as well to "N", so that something like "1234 " might be
> considered a valid number.
>
> On second thoughts (just thinking as I typed this), that would then
> consider a field of all spaces, or a field with spaces in the middle, to be
> numeric. I am sure there must be a way out for that as well.
>
> Regards,
> Kals
>
> On Wed, 18 Dec 2002 10:05:14 -0700, Grace Chen <[login to unmask email]>
> wrote:
>
> >This is a wonderful solution. I tested it. However, it need a little bit
> >change. The correct syntax should be :
> >WHERE TRANSLATE (COL1, 'NNNNNNNNNNX', '1234567890N') = 'NNNNNN'
> >
> >Grace
> >
> >-----Original Message-----
> >From: Bernd Oppolzer [mailto:[login to unmask email]
> >Sent: Wednesday, December 18, 2002 4:35 AM
> >To: [login to unmask email]
> >Subject: Re: ISNUMERIC Scalar Function
> >
> >
> >Another solution using BUILTIN scalar functions:
> >
> >supposed, you have to test a CHAR(6) field called COL1 to have only
> >decimal digits in it. You could do this by
> >
> > WHERE TRANSLATE (COL1, '1234567890N', 'NNNNNNNNNNX') = 'NNNNNN'
> >
> >that is, you translate all digits to N, but a N in the original string to
> >something else. If you get six Ns, the original COL1 was numeric.
> >
> >Hope this helps
> >
> >Regards
> >
> >Bernd
> >
> >
> >
> >Am Mit, 18 Dez 2002 schrieben Sie:
> >> Hi Gary,
> >>
> >> Not sure how worthwhile an option this is, but you can maybe give it a
> >try.
> >>
> >> Assuming the column which you want to check is of CHAR type, you can
> >create
> >> a separate table TAB1 with an integer column, and load it with
> continuosly
> >> increasing nos. starting from 0 till 9 or 99 or 999 or 9999, etc,
> >depending
> >> on the length of your column. You can then code your query as
> >>
> >> SELECT last_name, first_name
> >> FROM gift_master
> >> WHERE STRIP(naughty_nice) in (SELECT char(col1) from tab1);
> >>
> >> The STRIP function may be optional depending on your actual data.
> >>
> >> Regards,
> >> Kals
> >>
> >> >
> >> >>From: "Blumenthal, Gary" <[login to unmask email]>
> >> >>Reply-To: DB2 Data Base Discussion List <[login to unmask email]>
> >> >>To: [login to unmask email]
> >> >>Subject: ISNUMERIC Scalar Function
> >> >>Date: Mon, 16 Dec 2002 15:42:30 -0500
> >> >>
> >> >>All,
> >> >>
> >> >>I am trying to return records from a table that contain only numeric
> >> values
> >> >>in a particular column. I have seen other databases have an ISNUMERIC
> ()
> >> >>scalar function. For example:
> >> >>
> >> >>SELECT last_name, first_name
> >> >>FROM gift_master
> >> >>WHERE ISNUMERIC(naughty_nice) = 1
> >> >>
> >> >>This will return all the records from gift_list where naught_nice is a
> >> >>valid
> >> >>number. How can this be done in DB2 SQL?
> >> >>
> >> >>Thanks in advance,
> >> >>
> >> >>Gary B.
> >> >>
> >>
> >>
> >>
> >the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
> can
> >
> >
> >
> >
> the
> >
> >
> >
> >
> >***************************************************************************
> ***********************
> >The contents of this email and any attachments are confidential.
> >It is intended for the named recipient(s) only.
> >If you have received this email in error please notify the system manager
> or the
> >sender immediately and do not disclose the contents to any one or make
> copies.
> >
> >** eSafe scanned this email for viruses, vandals and malicious content **
> >***************************************************************************
> ***********************
> >
> >
> >
> the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
> can
>
>

>
>
>
>
>
>






pm3iinc

Re: ISNUMERIC Scalar Function
(in response to James Campbell)
Depending on what you may encounter in that particular column, you may also
use the
lower()=upper()
method.

Letters should convert from lower to upper so
if any letters are encountered in the string, they would be different.
(by inverse, if there are only numeric chars, u/l will be equal)
limitation : lower(spaces) = upper(spaces), special chars, etc.

translate is costly. maybe it's better to select translate() .. from
(select column from ... where upper()=lower())
(for an sql only solution)

Of course there is also the external c udf.

PM



Bernd Oppolzer

Re: ISNUMERIC Scalar Function
(in response to pm3iinc)
I would suggest

WHERE TRANSLATE (STRIP(COL1), 'NNNNNNNNNNX', '1234567890N') = 'NNNNNN'

With STRIP, you remove the spaces around the (hopefully) numeric digits.

Sorry for the error in my first posting (with wrong order of 2nd and 3rd
argument of TRANSLATE). I also had this error in the foils I use for DB2
education, so thank you again for correcting me.

Regards

Bernd



Am Mit, 18 Dez 2002 schrieben Sie:
> Thats a pretty neat solution, Bernd. Just one addition though, we need to
> translate spaces as well to "N", so that something like "1234 " might be
> considered a valid number.
>
> On second thoughts (just thinking as I typed this), that would then
> consider a field of all spaces, or a field with spaces in the middle, to be
> numeric. I am sure there must be a way out for that as well.
>
> Regards,
> Kals
>
> On Wed, 18 Dec 2002 10:05:14 -0700, Grace Chen <[login to unmask email]>
> wrote:
>
> >This is a wonderful solution. I tested it. However, it need a little bit
> >change. The correct syntax should be :
> >WHERE TRANSLATE (COL1, 'NNNNNNNNNNX', '1234567890N') = 'NNNNNN'
> >
> >Grace
> >



Bernd Oppolzer

Re: ISNUMERIC Scalar Function
(in response to Bernd Oppolzer)
Sorry,

once again I made a mistake.

WHERE TRANSLATE (STRIP(COL1), 'NNNNNNNNNNX', '1234567890N') = 'NNNNNN'

doesn't work, because the length of the substring with digits of COL1 is not
always six. So at the right side there must be a string consisting of x Ns,
where x is the LENGTH of STRIP(COL1).

That should be

WHERE TRANSLATE (STRIP(COL1), 'NNNNNNNNNNX', '1234567890N') =
REPEAT ('N', LENGTH(STRIP(COL1)))

I did no test on it (no time). Maybe this conditions gives OK for COL1 = all
blank. If so, you should add

AND COL1 <> ' '

Regards

Bernd



Am Mit, 18 Dez 2002 schrieben Sie:
> Thats a pretty neat solution, Bernd. Just one addition though, we need to
> translate spaces as well to "N", so that something like "1234 " might be
> considered a valid number.
>
> On second thoughts (just thinking as I typed this), that would then
> consider a field of all spaces, or a field with spaces in the middle, to be
> numeric. I am sure there must be a way out for that as well.
>
> Regards,
> Kals
>
> On Wed, 18 Dec 2002 10:05:14 -0700, Grace Chen <[login to unmask email]>
> wrote:
>
> >This is a wonderful solution. I tested it. However, it need a little bit
> >change. The correct syntax should be :
> >WHERE TRANSLATE (COL1, 'NNNNNNNNNNX', '1234567890N') = 'NNNNNN'
> >
> >Grace
> >