Re: How do I test if a value is numeric?

From: Eltschinger Markus <markus.eltschinger_at_swisscom.com>
Date: Fri, 20 Dec 2002 19:39:43 +0100
Message-ID: <1040409591.61209@exnews>

Dear Graeme,

the following SQL may also help (tested on Oracle 9iR2):

SELECT CASE WHEN REPLACE(TRANSLATE('12.34','0123456789.',' '),' ','') IS NULL

              THEN 'numeric'
            ELSE 'not numeric'
       END

  FROM DUAL Kind regards,
Markus Eltschinger

---

Swisscom IT Services Ltd
Data Warehouse Development
Villars-Sur-Glāne FR
Switzerland
http://www.swisscom.com/it/content/index_EN.html

"Graeme Richardson" <graeme_at_adept_zero_spam_x.co.nz> wrote in message news:atomdi$8dk$1_at_news.wave.co.nz...
> I have a Char(30) field in a database that has been used to store a
> variable length persons name and optionally a 2 decimal number (e.g. 12.34,
> and 2.34). The number is always preceded by at least one space.
>
> I need records where there is a number on the end. How do I write a clause
> to do this?
>
> I have clause that converts the last six characters to a numeric value (used
> in Select clause):
>
> CAST(SUBSTRING(CB_TR_NARRATIVE FROM POSITION (' ' IN CB_TR_NARRATIVE FROM
> 24) AS NUMERIX(4,2)) AS Actual
>
> but for the case where a number is not included, this fails.
>
> Thanks for any help you can offer,
> --
> Graeme Richardson
> Analyst Programmer
> AdeptX Limited
>
>
Received on Fri Dec 20 2002 - 12:39:43 CST