| Author |
Message
|
| DTechBuddy |
 Posted: Wed May 13, 2009 5:45 am Post subject: [Solved] ESQL SUBSTRING BEFORE |
 |
|
Novice
Joined: 05 May 2009
Posts: 20
|
Hi Guyz,
I have a requirement to select only the value occuring before the character '#'.
E.g. if String is "HelloABC#Company", then i want to retrieve the value "HelloABC".
I am working on WMBT 6.0.2.
Thnx in advance. |
|
| Back to top |
|
 |
| Vitor |
| Posted: Wed May 13, 2009 5:53 am Post subject: Re: ESQL SUBSTRING BEFORE |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| DTechBuddy wrote: |
I have a requirement to select only the value occuring before the character '#'.
E.g. if String is "HelloABC#Company", then i want to retrieve the value "HelloABC". |
Thank you for sharing.
Given the subject line of this post, I wonder what the question is you're implying....? 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| DTechBuddy |
| Posted: Wed May 13, 2009 6:02 am Post subject: Re: ESQL SUBSTRING BEFORE |
|
|
Novice
Joined: 05 May 2009
Posts: 20
|
[quote]I wonder what the question is you're implying....? [/quote]
As the eg. stated before, I have to retrieve the value "HelloABC" from "HelloABC#Company".
The position of '#' can be anything (Not fixed).
How to do this?
The syntax :- SUBSTRING (MyValue FROM <INTEGER>).
I don't have this Integer value.
I recall reading somewhere, that WMBT 6.0.1.3 ESQl has SUBSTRING BEFORE and AFTER facilities.
Is there anything of this sort in WMBT6.0.2 ESQl? |
|
| Back to top |
|
|
| dmw1986 |
| Posted: Wed May 13, 2009 6:05 am Post subject: Re: ESQL SUBSTRING BEFORE |
|
|
Apprentice
Joined: 24 Sep 2008
Posts: 35
|
| Vitor wrote: |
| DTechBuddy wrote: |
I have a requirement to select only the value occuring before the character '#'.
E.g. if String is "HelloABC#Company", then i want to retrieve the value "HelloABC". |
Thank you for sharing.
Given the subject line of this post, I wonder what the question is you're implying....? |
I think he wants to do a substring of any text that falls before #. So if the string is..
'TextBeforePound#BlahBah' he wants 'TextBeforePound'
or
'CompanyABC-CompnyBCA#GoGreen' he wants 'CompanyABC-CompnyBCA'
So the substring length will vary depending on where the # is in the string. |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed May 13, 2009 6:08 am Post subject: Re: ESQL SUBSTRING BEFORE |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| DTechBuddy wrote: |
I recall reading somewhere, that WMBT 6.0.1.3 ESQl has SUBSTRING BEFORE and AFTER facilities.
Is there anything of this sort in WMBT6.0.2 ESQl? |
Oh, right, I see! Sorry about that; the coffee machine is broken and my brain's having to make do with water.... 
| DTechBuddy wrote: |
| I don't have this Integer value. |
No, but you could easily obtain it by finding the position of the '#'. 2 lines of ESQL not 1 but should work like a charm.
And an improvement waiting for you once you upgrade. 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed May 13, 2009 6:10 am Post subject: Re: ESQL SUBSTRING BEFORE |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| dmw1986 wrote: |
| I think he wants to do a substring of any text that falls before #. |
I think you're right. And I think I failed to notice he (or she) is on a previous version not containing the function in question.
Even I have off days. And I claim external circumstances; the SLA on coffee machine fixing here is ridiculously long! 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed May 13, 2009 6:11 am Post subject: Re: ESQL SUBSTRING BEFORE |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Vitor wrote: |
2 lines of ESQL not 1 but should work like a charm.
|
Or 1 slightly chunky line. 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Wed May 13, 2009 6:23 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi All,
I'm a bit annoyed by this topic, so many posts and no one has even come to the point of answering it....and it's not that the question was not understandable in the first post itself...what did I miss ?
@DTechBuddy:
| Code: |
| SET someVar = SUBSTRING('HelloABC#Company' FROM 1 FOR (POSITION('#' IN 'HelloABC#Company')-1)); |
Regards. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed May 13, 2009 7:41 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| elvis_gn wrote: |
| I'm a bit annoyed by this topic, so many posts and no one has even come to the point of answering it....and it's not that the question was not understandable in the first post itself...what did I miss ? |
I think a lot of people were wondering why the OP didn't just use
| Code: |
| SUBSTRING('HelloABC#Company' BEFORE '#'); |
|
|
| Back to top |
|
|
| Vitor |
| Posted: Wed May 13, 2009 10:02 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| mqjeff wrote: |
I think a lot of people were wondering why the OP didn't just use
| Code: |
| SUBSTRING('HelloABC#Company' BEFORE '#'); |
|
Because that's not in v6.0, only in v6.1?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed May 13, 2009 10:40 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
well, technically DTechBuddy only specified the level of *Toolkit* that was being used...   |
|
| Back to top |
|
|
| DTechBuddy |
| Posted: Wed May 13, 2009 9:55 pm Post subject: Re : Solved. Thnx. |
|
|
Novice
Joined: 05 May 2009
Posts: 20
|
Hey Guyz,
Thnx to all.
Thnx Elvis. Vitor's hint to find the position of '#' solved my query which matched ur code solution.
Cheers.  |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu May 14, 2009 12:14 am Post subject: Re: Re : Solved. Thnx. |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| DTechBuddy wrote: |
| Vitor's hint to find the position of '#' solved my query which matched ur code solution. |
Enjoy your fishing, and well done you! 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| mgk |
| Posted: Thu May 14, 2009 1:01 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Don't forget to cope with the possibility of the '#' not occuring in the input data. I believe that the above code will cause an exception in this case as your POSITION will return a 0, which will then become -1 and so the StringLength parameter will be "less than the StartPosition" which is not allowed if you read the docs for SUBSTRING.
Kind Regards,
_________________
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 |
|
|
|
|
