datastage function

pavan_test · Post by **pavan_test** » Wed Nov 16, 2011 8:08 pm

Hi All,

I have a column that contains data such as

notes=dsadssa, sdsaddsa, dsaddfdfd, fdfdfdsfdsfds

whereever there is comma(followed by single space) I have to write a semi colon.

expected output:

notes=dsadssa;sdsaddsa;dsaddfdfd;fdfdfdsfdsfds

can someone please let me know how can I do this datastage

Thanks
pavan

SURA · Post by **SURA** » Wed Nov 16, 2011 9:08 pm

If always you will get the single space after comma?

Trim (col,' ',':')

Or

You can use convert function too.

DS User

ray.wurlod · Post by **ray.wurlod** » Wed Nov 16, 2011 10:05 pm

Search DSXchange for the pxEreplace function, which will handle your requirement even if there are spaces and commas in your data.

Otherwise do what SURA was attempting to suggest - Trim() to get rid of the spaces and Convert() to change the commas to semi-colons.

pavan_test · Post by **pavan_test** » Thu Nov 17, 2011 9:05 am

I Apologize, actualy it is other way around..

where ever there are semi-colons, I have to replace them with , (followed by single space)

notes=dsadssa;sdsaddsa;dsaddfdfd;fdfdfdsfdsfds

desired output:

notes=dsadssa, sdsaddsa, dsaddfdfd, fdfdfdsfdsfds

how can I do it using convert function

Thanks

pandeesh · Post by **pandeesh** » Thu Nov 17, 2011 10:10 am

Code: Select all

Convert(";",", ",input_column)

Try this, but i am not sure whether you will get any space after the comma.

Try this and let us know!

pandeesh · Post by **pandeesh** » Thu Nov 17, 2011 11:22 am

Convert() replaces single character by another single charcter.
I dont think it will work in your case.
Yu can search PXEReplace() or use sed or awk in before/after job subroutine for this purpose.

Thanks

BradMiller · Post by **BradMiller** » Thu Nov 17, 2011 3:30 pm

Convert() replaces single character by another single charcter.

I have seen this posted on DSXChange before, but I have not found it to be true. I ran a quick test, using the "Convert" solution and found it worked per the o/p's requirement. Is it possible this is how Convert worked in earlier versions? I am using 8.1.

Code: Select all

Convert(';',', ',DSLink2.RawData)

jwiles · Post by **jwiles** » Thu Nov 17, 2011 4:46 pm

Convert() will not work here. As Pandeesh mentioned, it replaces single characters with other single characters--";" with ",". It will not replace a single character with a string of characters--";" with ", ".

As mentioned already, pxEreplace() for parallel is available for installation and will serve the purpose...also you can write your own parallel routine if you wish, use a Basic Transformer instead or even an external filter (awk or sed for example).

Regards,

pandeesh · Post by **pandeesh** » Thu Nov 17, 2011 9:57 pm

jwiles wrote:use a Basic Transformer instead or even an external filter (awk or sed for example).

Regards,

James,

why BASIC transformer?

ray.wurlod · Post by **ray.wurlod** » Thu Nov 17, 2011 10:10 pm

BASIC Transformer has in-built Ereplace() function.

pandeesh · Post by **pandeesh** » Thu Nov 17, 2011 10:17 pm

Thanks Ray!! I forgot!!