| Author |
Message
|
| rossired |
 Posted: Wed Sep 06, 2006 9:31 am Post subject: Is Numeric in ESQL |
 |
|
Novice
Joined: 22 Feb 2006
Posts: 22
|
Hi,
Whats the easiest way to check if a field has numeric data in it. I have a char field, that is supposed to contain numeric data, being mapped to a numeric field. I just want to check, so if its non numeric trash, I will just assign a zero.
Thanks
Vale |
|
| Back to top |
|
 |
| rossired |
| Posted: Wed Sep 06, 2006 9:49 am Post subject: Re: Is Numeric in ESQL |
|
|
Novice
Joined: 22 Feb 2006
Posts: 22
|
| rossired wrote: |
Hi,
Whats the easiest way to check if a field has numeric data in it. I have a char field, that is supposed to contain numeric data, being mapped to a numeric field. I just want to check, so if its non numeric trash, I will just assign a zero.
Thanks
Vale |
Ah, I think I may have found the answer: "IS NUMBER" |
|
| Back to top |
|
|
| mgk |
| Posted: Wed Sep 06, 2006 2:01 pm Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi,
IS NUMBER does not do what you think it does. It only checks a DECIMAL field or variable to ensure that the value in it is not NAN or INFINITY or NULL. It does not allow you to test a char field to see if it contains a valid numeric value.
That said, the way to do this on V6 of MB is to use a CAST with a DEFAULT clause which causes the CAST to substitute the value of the DEFAULT if the CAST would fail. In your case, you would have a DEFAULT clause of 0 to assign 0 if the cast fails.
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| m.schneider |
| Posted: Tue Jul 01, 2008 5:30 am Post subject: |
|
|
Centurion
Joined: 10 Apr 2007
Posts: 132
Location: Germany
|
|
| Back to top |
|
|
| m.schneider |
| Posted: Thu Jul 03, 2008 10:33 pm Post subject: |
|
|
Centurion
Joined: 10 Apr 2007
Posts: 132
Location: Germany
|
Any updates? I really need to solve this one.
Thx |
|
| Back to top |
|
|
| bielesibub |
| Posted: Fri Jul 04, 2008 12:37 am Post subject: |
|
|
Apprentice
Joined: 02 Jul 2008
Posts: 40
Location: Hampshire, UK
|
Hi there,
You could use:
IF LENGTH(TRANSLATE(aValue, '0123456789', '')) <> 0 THEN
RETURN FALSE
ELSE
RETURN TRUE
END IF;
This will replace any numeric values with a '', so hopefully the end result will be a string length of 0, anything else and you've got a non numeric character in your string. You could add a '.' and/or ',' in the searchString expression ('0123456789,.) to cater for decimal points etc.. |
|
| Back to top |
|
|
| m.schneider |
| Posted: Fri Jul 04, 2008 3:50 am Post subject: |
|
|
Centurion
Joined: 10 Apr 2007
Posts: 132
Location: Germany
|
| Thanks, that's a pretty good workaround. |
|
| Back to top |
|
|
| m.schneider |
| Posted: Mon Jul 07, 2008 11:30 pm Post subject: |
|
|
Centurion
Joined: 10 Apr 2007
Posts: 132
Location: Germany
|
| I'm still wondering how to use CAST with DEFAULT? |
|
| Back to top |
|
|
| paintpot |
| Posted: Fri Jul 11, 2008 9:24 am Post subject: |
|
|
Centurion
Joined: 19 Sep 2005
Posts: 112
Location: UK
|
Hi,
Works for Integer...I don't know if that will help with your particular data to test on. Decimal doesn't work for me (latest WMB 6.0, not WMB 6.1).
DECLARE aValue CHARACTER '1x34';
DECLARE nValue INTEGER;
SET nValue = CAST(aValue AS INTEGER DEFAULT 0);
-- it's a number (or zero)
IF (nValue > 0) THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF; |
|
| Back to top |
|
|
| DaveCanto |
| Posted: Fri Sep 10, 2010 5:29 am Post subject: |
|
|
Novice
Joined: 01 Feb 2008
Posts: 11
Location: Belgium - EU
|
According to IBM Help:
| Code: |
The DEFAULT parameter provides a method of avoiding exceptions being thrown from CAST statements by providing a last-resort value to return.
The DEFAULT parameter must be a valid ESQL expression that returns the same data type as that specified on the DataType parameter, otherwise an exception is thrown. |
Because you're casting to a decimal value instead of an integer value, you shouldn't use 0 but try something like 0,0 or 0.0 
_________________
Do not underestimate the power of ... unit testing and continuous integration. |
|
| Back to top |
|
|
|
|
