Replace function

mtechnocrat · Post by **mtechnocrat** » Thu Jun 30, 2005 2:50 am

Hi

From source data one column is coming with carraige returns , in target table i have to replace this carriage return with spaces .is there any function like REPLACE in oracle ? Can guide me in this issue

Thanks

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Thu Jun 30, 2005 2:51 am

Search for Convert.

Sreenivasulu · Post by **Sreenivasulu** » Thu Jun 30, 2005 4:17 am

U can use the REPLACE function of Datastage to do this.

Prashantoncyber · Post by **Prashantoncyber** » Thu Jun 30, 2005 5:13 am

For convert function

Expression should be Convert("\r", " ", MyString)

For replace function

Expression should be Ereplace(MyString, "\r", " ")

thanks

chulett · Post by **chulett** » Thu Jun 30, 2005 6:12 am

You can also use Char(10) in place of the \r.

kduke · Post by **kduke** » Thu Jun 30, 2005 8:38 am

REPLACE is similar to CHANGE or EREPLACE.

ray.wurlod · Post by **ray.wurlod** » Thu Jun 30, 2005 6:58 pm

Nah!

REPLACE replaces the value of an element in a dynamic array.

You need to use CONVERT, EREPLACE or CHANGE. CHANGE is a subset of EREPLACE functionality.

For single character replacement, CONVERT is the more efficient. However if you need portability to Windows platforms you will need EREPLACE or CHANGE, since the line terminator is two characters long.

Also, I doubt very much that the "\r" notation will work properly with the CONVERT function in server jobs, since it would replace "\" and "r" separately. Similarly, I believe EREPLACE and CHANGE would seek out explicit instances of "\r". That is, DataStage BASIC does not handle shell escapes. Specify Char(10).

All these functions can be found in the DataStage BASIC manual.

kduke · Post by **kduke** » Thu Jun 30, 2005 7:56 pm

Oracle REPLACE is similar to EREPLACE and CHANGE in Universe BASIC.

Yes!

ray.wurlod · Post by **ray.wurlod** » Fri Jul 01, 2005 6:48 pm

Sreenivasulu wrote:U can use the REPLACE function of Datastage to do this.

I was really reacting to this post, of course!

kduke · Post by **kduke** » Sat Jul 02, 2005 9:59 am

Sorry.

chulett · Post by **chulett** » Sat Jul 02, 2005 2:52 pm

Don't be sorry, how were you supposed to know? Ray doesn't usually quote because you get less points when you quote.

ray.wurlod · Post by **ray.wurlod** » Sat Jul 02, 2005 7:28 pm

Fewer.

chulett · Post by **chulett** » Sat Jul 02, 2005 8:49 pm

ray.wurlod wrote:Fewer.

Yes, yes, I know, not sure what I was thinking... 'fewer' for things that can be counted individually, and 'less' for those things that can't be. Mostly. I'd edit my original post but it would ruin the whole Feng Shui of it.