Originally posted by: SystemAdmin

Hello,
I typically use the following rule to remove punctuation from a name.

SQUEEZE(SUBSTITUTE(LEAVEALPHANUM(SUBSTITUTE(SUBSTITUTE(UPPERCASE(Input)," ","z"),"-","z")),"z"," ")," "))

But this time, I need to keep the place holder for the punctuation...meaning that I need to replace any punctuation in my item with a space. If you have a clever rule, please share. I really don't want to manually listing out every possibility of punctuation characters to be used in a SUBSTITUTE.

I was also wondering if I could do something with the HEX values....humm....

Thanks!
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

In normal circumstances you could use the function LEAVEALPHANUM because it removes all non-alphanumeric characters from a specified text item. For your problem you may want to check out the CONVERT function which replaces each byte of a byte stream or text expression with a byte from another byte stream or text expression. Does this work?
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: repanzer

I can't look at this just this moment, but you only need on substitute here

this
SUBSTITUTE(SUBSTITUTE(Input," ","z"),"-","z")),"z"," ")

can be

SUBSTITUTE(Input," ","z","-","z")
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

Thank you for the responses.

Repanzer, nice tip to avoid nested SUBSTITUTE functions. I'll remember that one for sure.

Kashush, it doesn't look like the CONVERT will work.

So far our answer is to change our input tree to read the our field tree in 1 byte increments.

InputTextString
OneChar(s) =IF(ISALPHA(OneChar:InputTextString) | ISNUMBER(OneChar:InputTextString) , OneChar:InputTextString , " ")

I wish I could figure out a way to do this without changing our input tree. The tree was imported from an XSD.

Keep those ideas coming!
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: repanzer

The only time I've ever used CONVERT, and the rule was in place before I ever touched the map, was to convert EBCDIC to ASCII for data coming off the mainframe. There's an exact example under the install directory for design studio.

The way convert works is against a table (simple file), where you define which characters should replace which charachters in any string. You would want a string of all the puncuation marks, all with space as their convert character.
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

I have attached a map that uses the CONVERT function to replace all non-alphanumeric characters with spaces. I was only able to attach one file so I attaced the map.

The input file, NoPunctuationTable.txt, contains the following text: " 0123456789 ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz ". Note that the numbers begin on 48th byte and the uppercase alphabet begin on the 65th byte and the lower case alphabet begins on the 97th byte. Refer to this chart: http://www.asciitable.com/

The type tree for both the input and output should contain a BLOB text element with no initiators, size constraints or terminators.

The rule is: "=CONVERT("I'm replacing all the puntucation marks, like: commas, and 'exclamation' points with spaces! Is that OK with you?", NoPunctuationTable )"

The output is: "I m replacing all the puntucation marks like commas and exclamation points with spaces Is that OK with you "

This is probably not the ideal solution but it can work. The SUBSTITUTE could work as well.
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: SystemAdmin

This example is even easier. Here I have replaced the input file containing the conversion table with a hard coded text string. The map is attached.

There is no input. The rule looks like this:
=CONVERT("I'm replacing all the puntucation marks, like: commas, and 'exclamation' points with spaces! Is that OK with you?", " 0123456789 ABCDEFGHIJKLMNOPQRSTUVWXYZ abcdefghijklmnopqrstuvwxyz ")
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: SystemAdmin

Now, I get what you are saying. This is perfect! THANK YOU SO MUCH!!
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender