| Author |
Message
|
| madrox |
 Posted: Mon Apr 27, 2015 4:35 pm Post subject: ESQL Substring |
 |
|
Acolyte
Joined: 11 Mar 2015
Posts: 71
|
Hello All,
I am trying to use the substring function to capture the characters before and after '/'
for ex:
"firstpart/secondpart/thirdpart"
so i can retrieve
firstpart
secondpart
thirdpard
from the knowledge center i found
| Code: |
| SUBSTRING('firstpart/secondpart' BEFORE '/'); |
should give me : firstpart
| Code: |
| SUBSTRING('firstpart/secondpart' AFTER '/'); |
should give me : secondpart
how can i get thirdpart?
Thanks |
|
| Back to top |
|
 |
| smdavies99 |
| Posted: Mon Apr 27, 2015 10:27 pm Post subject: |
|
|
Jedi Council
Joined: 10 Feb 2003
Posts: 6076
Location: Somewhere over the Rainbow this side of Never-never land.
|
Won't the second on return
secondpart/thirdpart
because it is after the first /
Then you can do the substring again to get the third part.
you could submit an RFE requesting that the function be modified to say something like
| Code: |
SUBSTRING('firstpart/secondpart' AFTER n '/' );
|
Where n is the number of instances of '/' to skip.
alternatively you could write your own function to do the iteration and call that instead of calling SUBSTRING directly.
_________________
WMQ User since 1999
MQSI/WBI/WMB/'Thingy' User since 2002
Linux user since 1995
Every time you reinvent the wheel the more square it gets (anon). If in doubt think and investigate before you ask silly questions. |
|
| Back to top |
|
|
| iibmate |
| Posted: Tue Apr 28, 2015 1:12 am Post subject: Re: ESQL Substring |
|
|
Apprentice
Joined: 17 Mar 2015
Posts: 38
Location: Perth, WA
|
Try this..
SUBSTRING(SUBSTRING('firstpart/secondpart/thirdpart' AFTER '/') AFTER '/'); |
|
| Back to top |
|
|
| wbisantosh |
| Posted: Tue Apr 28, 2015 6:44 am Post subject: |
|
|
Apprentice
Joined: 12 Nov 2012
Posts: 47
|
You could try something similar, to make it work for variable number of Parts (not just 3)
| Code: |
DECLARE parts ROW;
DECLARE Req_String Char 'First/Second/Third';
DECLARE i INT 1;
WHILE LENGTH(Req_String) > 0 DO
SET parts.[i] = SUBSTRING(Req_String BEFORE '/');
SET Req_String = SUBSTRING(Req_String AFTER '/');
SET i = i + 1;
END WHILE; |
Thanks
Santosh |
|
| Back to top |
|
|
| madrox |
| Posted: Thu Apr 30, 2015 6:26 am Post subject: |
|
|
Acolyte
Joined: 11 Mar 2015
Posts: 71
|
| Thank you all for your input |
|
| Back to top |
|
|
| inMo |
| Posted: Mon May 04, 2015 11:54 am Post subject: |
|
|
Master
Joined: 27 Jun 2009
Posts: 216
Location: NY
|
| How does one submit an RFE? |
|
| Back to top |
|
|
| Vitor |
| Posted: Mon May 04, 2015 12:05 pm Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| inMo wrote: |
| How does one submit an RFE? |
From here
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| inMo |
| Posted: Mon May 04, 2015 12:13 pm Post subject: |
|
|
Master
Joined: 27 Jun 2009
Posts: 216
Location: NY
|
Thank you.  |
|
| Back to top |
|
|
|
|
