SQL statement to select numeric data?
I am working with data that is not normalized. One column has numeric and non-numeric data as in the following example: 20,234.17 WAIVER 10.99 CANCEL Is there a way to select only the rows with numeric data? I was hoping to use something like IS NUMERIC or IS NUMBER, but couldn't find anything that will work. If there is not an SQL statement that will work, does anyone have sample stored procedure code that will work? Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.
joel_anderson@prosolution.com wrote:
>
> I am working with data that is not normalized. One column has numeric
> and non-numeric data as in the following example:
>
> 20,234.17
> WAIVER
> 10.99
> CANCEL
>
> Is there a way to select only the rows with numeric data? I was hoping
> to use something like IS NUMERIC or IS NUMBER, but couldn't find
> anything that will work. If there is not an SQL statement that will
> work, does anyone have sample stored procedure code that will work?
It is possible to do with a MATCHES clause and regular expression but
it is not pretty, not universally applicable, weird. Towit (assuming a
12 character column):
SELECT *
FROM tablename
WHERE poor_key MATCHES"[0-9,\\.][0-9,\\.][0-9,\\.][0-9,\\.][0-9,\\.][0-9,\\.][0-9,\\.][0-9,\\.][0-9,\\.][0-9,\\.][0-9,\\.][0-9,\\.]";
Matches allows limited, UNIX style, regular expression parsing.
Art S. Kagel
In a stored procedure, assign the value to a numeric variable and trap the error. Bashar Chalabi CTL, London joel_anderson@prosolution.com wrote in message <7k3g8v$vak$1@nnrp1.deja.com>... >I am working with data that is not normalized. One column has numeric >and non-numeric data as in the following example: > > 20,234.17 > WAIVER > 10.99 > CANCEL > >Is there a way to select only the rows with numeric data? I was hoping >to use something like IS NUMERIC or IS NUMBER, but couldn't find >anything that will work. If there is not an SQL statement that will >work, does anyone have sample stored procedure code that will work? > > >Sent via Deja.com http://www.deja.com/ >Share what you know. Learn what you don't.