| Author |
Message
|
| hellobond070 |
 Posted: Tue Aug 10, 2010 11:34 am Post subject: Keep alphabets and numerics ONLY in String |
 |
|
Centurion
Joined: 18 Nov 2009
Posts: 118
|
Hi,
I have a string while will have data like [;'}-] along with characters like a,b,3,0,4 etc.
Suppose my source string is
My result should be
Is there any esql function which can do this ?
Thanks !! |
|
| Back to top |
|
 |
| Amitha |
| Posted: Tue Aug 10, 2010 11:41 am Post subject: |
|
|
Voyager
Joined: 20 Nov 2009
Posts: 80
Location: Newyork
|
| You can use TRANSLATE function to remove those characters individually. |
|
| Back to top |
|
|
| hellobond070 |
| Posted: Tue Aug 10, 2010 11:45 am Post subject: |
|
|
Centurion
Joined: 18 Nov 2009
Posts: 118
|
| Well I am not sure about the kind of characters that will be present. I had this solution in mind but when the business users told me that it can be anything but we need only alphanumeric to be the output, I thought of shouting for help |
|
| Back to top |
|
|
| Gaya3 |
| Posted: Tue Aug 10, 2010 12:54 pm Post subject: |
|
|
Jedi
Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US
|
Regular Expression Pattern could be one solution for this kind of scenarios.
for this you have to go for JCN 
_________________
Regards
Gayathri
-----------------------------------------------
Do Something Before you Die |
|
| Back to top |
|
|
| kimbert |
| Posted: Tue Aug 10, 2010 1:39 pm Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
| Can I ask why these chars need to be removed rather than escaped? Does the target system have any kind of escape mechanism for representing non-alphanumeric characters? |
|
| Back to top |
|
|
| hellobond070 |
| Posted: Tue Aug 10, 2010 2:19 pm Post subject: |
|
|
Centurion
Joined: 18 Nov 2009
Posts: 118
|
| The target is SAP. Well they want the integration layer to do this job |
|
| Back to top |
|
|
| inMo |
| Posted: Wed Aug 11, 2010 2:25 am Post subject: |
|
|
Master
Joined: 27 Jun 2009
Posts: 216
Location: NY
|
If I understand correctly, it is not just a task of identifying that the characters are present, but also removing them from the payload?
You can create a function/procedure in esql to do this, but what a bottleneck. As I see it at the moment, You'd have to go through every character and check it against an allowed set and copy it over if it is allowed. Skip the characters that don't match the allowed list. |
|
| Back to top |
|
|
| kimbert |
| Posted: Wed Aug 11, 2010 3:38 am Post subject: |
|
|
Jedi Council
Joined: 29 Jul 2003
Posts: 5542
Location: Southampton
|
I'm not an SAP expert, but...usually when illegal characters are encountered, the transaction fails and the user finds out that they have supplied illegal data. Silently removing the illegal characters could change the meaning of the user's request without the user knowing that anything went wrong.
Which SAP field are you populating? What does SAP use that field for? |
|
| Back to top |
|
|
| hellobond070 |
| Posted: Wed Aug 11, 2010 8:08 am Post subject: |
|
|
Centurion
Joined: 18 Nov 2009
Posts: 118
|
I found a very simple solution. It works !!!!
INV_NUMB has good and bad characters.
| Code: |
SET JUNK_CHAR = TRANSLATE(INV_NUMB,'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789','');
SET INV_NUMB = TRANSLATE(INV_NUMB,JUNK_CHAR,''); |
Finally, INV_NUMB has only good characters |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Aug 11, 2010 9:21 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| hellobond070 wrote: |
I found a very simple solution. It works !!!!
INV_NUMB has good and bad characters.
| Code: |
SET JUNK_CHAR = TRANSLATE(INV_NUMB,'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789','');
SET INV_NUMB = TRANSLATE(INV_NUMB,JUNK_CHAR,''); |
Finally, INV_NUMB has only good characters |
I'd be more worried about the junk characters being a representation of a binary number as String... 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| hellobond070 |
| Posted: Wed Aug 11, 2010 11:27 am Post subject: |
|
|
Centurion
Joined: 18 Nov 2009
Posts: 118
|
| well in my case it's not. There is no binary representation of characters in the incoming XML file. |
|
| Back to top |
|
|
|
|
