| Author |
Message
|
| suraj |
 Posted: Wed Oct 14, 2009 1:16 pm Post subject: check whether the string contains numeric value in ESQL |
 |
|
Acolyte
Joined: 31 Jul 2007
Posts: 56
|
how do i check whether the string contains numeric value in ESQL??
the IS NUM doest not work because If applied to non-numeric types, the result is FALSE. |
|
| Back to top |
|
 |
| Vitor |
| Posted: Wed Oct 14, 2009 2:38 pm Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
CAST to decimal & check for failure?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| suraj |
| Posted: Wed Oct 14, 2009 6:54 pm Post subject: |
|
|
Acolyte
Joined: 31 Jul 2007
Posts: 56
|
| I dont want an exception to be thrown. The whole message will error out just for this check. |
|
| Back to top |
|
|
| WMBDEV1 |
| Posted: Wed Oct 14, 2009 11:58 pm Post subject: |
|
|
Sentinel
Joined: 05 Mar 2009
Posts: 888
Location: UK
|
| suraj wrote: |
| I dont want an exception to be thrown. The whole message will error out just for this check. |
Declare an error handler that detects this error, takes the appropriate action and then resumes processing? |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Thu Oct 15, 2009 12:17 am Post subject: Re: check whether the string contains numeric value in ESQL |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| suraj wrote: |
| I dont want an exception to be thrown. The whole message will error out just for this check. |
... or use CAST with DEFAULT parameter? |
|
| Back to top |
|
|
| zpat |
| Posted: Thu Oct 15, 2009 12:18 am Post subject: |
|
|
Jedi Council
Joined: 19 May 2001
Posts: 5866
Location: UK
|
You could check that each character in the string (using a SUBSTRING loop) is present in another validation string which contains '0123456789'.
Various ways to code this, using POSITION or CONTAINS function. |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Oct 15, 2009 6:23 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| WMBDEV1 wrote: |
| suraj wrote: |
| I dont want an exception to be thrown. The whole message will error out just for this check. |
Declare an error handler that detects this error, takes the appropriate action and then resumes processing? |
This was more the sort of thing I had in mind...... 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| suraj |
| Posted: Thu Oct 15, 2009 6:39 am Post subject: |
|
|
Acolyte
Joined: 31 Jul 2007
Posts: 56
|
CAST with DEFAULT works perfectly fine. Thanks rekarm01
Heres how to do it
DECLARE iValue INTEGER CAST(InputRoot.MRM.ns:Action AS INTEGER DEFAULT 0);
IF(iValue <> 0)THEN
-- input is numeric in this case
SET OutputRoot.XMLNSC.value = InputRoot.MRM.ns:Action;
ELSE
-- input is non numeric in this case
SET OutputRoot.XMLNSC.value = 'String';
END IF; |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Oct 15, 2009 6:43 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| suraj wrote: |
CAST with DEFAULT works perfectly fine. Thanks rekarm01
|
And thank you for posting your solution for the benefit of future posters 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Thu Oct 15, 2009 2:02 pm Post subject: Re: check whether the string contains numeric value in ESQL |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| suraj wrote: |
| CAST with DEFAULT works perfectly fine. Thanks rekarm01 |
Be careful though, to pick a suitable DEFAULT value, (where "suitable" depends on the given requirements).
For example, if '0' happens to be a legal numeric value for InputRoot.MRM.ns:Action, then obviously "DEFAULT 0" won't reliably indicate non-numeric values.
If no suitable DEFAULT value exists, then one alternative is to use a second CAST:
| Code: |
DECLARE Action CHARACTER InputRoot.MRM.ns:Action;
IF CAST(Action AS INTEGER DEFAULT 0) = 0
AND CAST(Action AS INTEGER DEFAULT -1) = -1
THEN
-- input is non-numeric; it can't be both '0' and '-1' at the same time
SET OutputRoot.XMLNSC.value = 'String';
ELSE
-- input is numeric
SET OutputRoot.XMLNSC.value = Action;
END IF; |
In this case, it doesn't matter what the DEFAULT values are, as long as they are different from each other. |
|
| Back to top |
|
|
| Bartez75 |
| Posted: Fri Oct 16, 2009 4:28 am Post subject: |
|
|
Voyager
Joined: 26 Oct 2006
Posts: 80
Location: Poland, Wroclaw
|
Or it could be like (one CAST only):
| Code: |
IF (InputRoot.MRM.ns:Action <> '0') THEN
DECLARE iValue INTEGER CAST(InputRoot.MRM.ns:Action AS INTEGER DEFAULT 0);
IF(iValue <> 0)THEN
-- input is numeric in this case
SET OutputRoot.XMLNSC.value = InputRoot.MRM.ns:Action;
ELSE
-- input is non numeric in this case
SET OutputRoot.XMLNSC.value = 'String';
END IF;
ELSE
SET OutputRoot.XMLNSC.value = '0';
END IF; |
|
|
| Back to top |
|
|
| WMBDEV1 |
| Posted: Fri Oct 16, 2009 4:41 am Post subject: |
|
|
Sentinel
Joined: 05 Mar 2009
Posts: 888
Location: UK
|
What if the number in action was > 10? or > 1000? or > 100000?
I think I prefer rekarms solution.... (after my own of course  ) |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Sat Oct 17, 2009 12:27 pm Post subject: Re: check whether the string contains numeric value in ESQL |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| rekarm01 wrote: |
| If no suitable DEFAULT value exists, then one alternative is to use a second CAST ... |
In hindsight, using two CASTS with different DEFAULTs looks a bit kludgy. One CAST with "DEFAULT NULL" looks better, (though it's not explicitly documented):
| Code: |
| IF CAST(Action AS INTEGER DEFAULT NULL) IS NULL THEN ... |
It looks even better without the IF-ELSE statement(s):
| Code: |
SET OutputRoot.XMLNSC.value
= COALESCE(CAST(InputRoot.XMLNSC.Action AS INTEGER DEFAULT NULL), 'String'); |
|
|
| Back to top |
|
|
|
|
