Replacing single quote with two single quotes in Transformer

[bhargav_dd]

Hi all,
I'm trying to read a csv file and convert it to an output file to be read by another program. The problem I'm having is with embedded single quotes in the input file.
For example, if I have in the input csv file:

123,Michael's Place,123 Main St.
I want the output to look like:
'123' 'Michael''s Place' '123 Main St.'
It's easy enough to surround the strings with the single quotes, but how can I replace one single quote with two single quotes?
Convert function won't work because it's a one to one replacement for each character in the list and new.list.
Are there any other tricks or functions I could use?

[ArndW]

Is this a server or a parallel job and, if it is a parallel job, can it be made into a server job or use a BASIC transform stage - in which case the

Code: Select all

EREPLACE(YourString,"'","''")

function would work for you. If the number of single quotes is limited to one or two, then you can use a PX transform stage with stage variables and utilize subsequent INDEX(YourString,"'",1) to find the first occurrence then modify the string with a NewString = OldString[1,SingleQuotePos]:"'":OldString[SingleQuotePos+1,9999]

[ray.wurlod]

Why not use double quotes as the quote character? Then you don't need to change the single quotes.

[cristty]

If you only have one single quote, this should work in a transformer:

If Index(DSLink2.col,"'",1) <> 0 then Field(DSLink2.col,"'",1) : ("":'"':"") : Field(DSLink2.col,"'",2) else DSLink2.col