Function to search a space character in the string

shivan · Post by **shivan** » Wed Oct 26, 2005 11:32 am

HI All,
Is there any function in datastage that can be used to search a strange character that is ( ) in a string and then replace it with a space.

thanks
shivan

ArndW · Post by **ArndW** » Wed Oct 26, 2005 11:46 am

Shivan, the OCONV(YourString,'MCP') function replaces all unprintable characters with a '.' (period). You can then convert all periods in that string to spaces using CONVERT() and you would be finished. There is an active thread from today describing how to do this.

shivan · Post by **shivan** » Wed Oct 26, 2005 12:18 pm

thanxs for replying,
i couldnt find the thread with this information. i am using the oconv functions, it works fine. But it leaves a period. Is there a way to convert a period to a space.
thanks
shivan

ArndW · Post by **ArndW** » Wed Oct 26, 2005 12:38 pm

ArndW wrote:...You can then convert all periods in that string to spaces using CONVERT()...

shivan · Post by **shivan** » Wed Oct 26, 2005 12:42 pm

what is the correct syntax for convert because if i use
convert(onconv("string",'MCP')) it fails. I tried like this also
convert(onconv("string",'MCP'),' ')

thanks

ArndW · Post by **ArndW** » Wed Oct 26, 2005 12:57 pm

Shivan,

please, please, please RTFM! I have no problem pointing you in the correct direction but if you are not willing to look up the syntax yourself in the handbook or even do a search on this forum to see how it has been used then there is not a chance that I will do so for you.

(a) Convert() has 3 parameters according to the handbook and the online help.
(b) The function is 'oconv', not 'onconv'

shivan · Post by **shivan** » Wed Oct 26, 2005 1:44 pm

i saw the thread which was started today. The error still exists. The way the convert function was used in the thread doesnt solve the problem. I tried to look at the tutorials also, couldnt find anything that will replace period to space. The information i am finding is changing hexadecimal to numeric, or something else.
thanks

ameyvaidya · Post by **ameyvaidya** » Wed Oct 26, 2005 2:24 pm

Shivan,

Are u working on a Server or a Parallel Job?

AFAIK Oconv cannot be used in a PX Transformer.
So if you are working in PX as the forum u've posted to would suggest, the second question would be: are you using a BASIC Transformer?

And finally in PX, the

Box usually represents the (0x0) character.( the way DataStage pads CHAR fields by default).

The function we used to convert it to " " is

convert(char(000)," ", <In.CharField>)

If it is not a (0x0), then the number in the char() function will have to be replaced by the proper ascii value.

IHTH