Oninit cdi / Archive
Talk to an expert →

Re: Numeric validation. Help !

Posted in 1999

Topics: Data Types & Schema Design, Third-Party Tools & Monitoring

Daniel Peleg — — source: Informix-list mailing list archive (1991-1998)

In article <37C2F5E0.A5937DCA@bloomberg.net>, kagel@bloomberg.net wrote: > Umm. Alter the column to float or money or integer or decimal..... > > Art S. Kagel DON'T alter the column to Float data type, if you want exact results (and I guess that's the case here, having a Charged Amount field). A Money or Numeric data type would be more appropriate. > > thain@writeme.com wrote: > > > > All, > > > > Anyone know if there is any SQL function or a better way > > to validate that a 7-character defined field > > if having only numbers (0-9). Since we have a char > > column used as a charged ammount in a table. Here is my current SQL query: > > "select sum(case when amount[1,7] not matches '[0-9][0-9][0-9][0-9] [0-9][0-9][0-9]' then '0' else amount end +0) from mytable" > > > > Any help is appreciate. > > > > TN. > > > > --------------------------------------------------- > > Get free personalized email at http://www.iname.com > Sent via Deja.com http://www.deja.com/ Share what you know. Learn what you don't.

↪ replying to Daniel Peleg
Art S. Kagel — — source: Informix-list mailing list archive (1991-1998)

Daniel Peleg wrote: > > In article <37C2F5E0.A5937DCA@bloomberg.net>, > kagel@bloomberg.net wrote: > > Umm. Alter the column to float or money or integer or decimal..... > > > > Art S. Kagel > > DON'T alter the column to Float data type, if you want exact results > (and I guess that's the case here, having a Charged Amount field). > A Money or Numeric data type would be more appropriate. No argument there. I was just trying to make a point about the inappropriateness of storing a numeric value in a character column. Are we back to COBOL folk? Art S. Kagel > > > > thain@writeme.com wrote: > > > > > > All, > > > > > > Anyone know if there is any SQL function or a better way > > > to validate that a 7-character defined field > > > if having only numbers (0-9). Since we have a char > > > column used as a charged ammount in a table. Here is my current SQL > query: > > > "select sum(case when amount[1,7] not matches '[0-9][0-9][0-9][0-9] > [0-9][0-9][0-9]' then '0' else amount end +0) from mytable" > > > > > > Any help is appreciate. > > > > > > TN. > > > > > > --------------------------------------------------- > > > Get free personalized email at http://www.iname.com > > > > Sent via Deja.com http://www.deja.com/ > Share what you know. Learn what you don't.