how can we replace one ' with two ' (chr(39))?

[stevelord]

Hi a colleague is dealing with apostrophes in some text strings and we're stumped about how to replace a single apostrophe with two apostrophes (not a double quotation mark, two apostrophes).

The character in question is chr(39), we're just stumped on how to have it replace only cases where an apostrophe appears by itself with two apostrophes.  (Please don't ask why. )  Thanks!

[MarcoWedel · Accepted solution]

Hi,

one solution to only replace single apostrophes with double apostrophes while leaving double apostrophes untouched could be:

LOAD *,
    Replace(Replace(Text,chr(39),chr(39)&chr(39)),chr(39)&chr(39)&chr(39)&chr(39),chr(39)&chr(39)) as Text2
INLINE [
    Text
    ABCDEFG
    "A'BCDEFG"
    "ABC''DEFG"
    "AB''CDE'FG"
    "ABC''DEF'G"
];

hope this helps

regards

Marco

[maxgro]

replace(

replace(

replace(Text, chr(39)&chr(39), '@@'),

  chr(39), chr(39)&chr(39)),

  '@@', chr(39)&chr(39))

[stevelord]

I'm sure these are the most genius expressions ever in the history of all qlikview script.  185 IQs for sure!  Gysbert should be in awe of this string.  Both answers are correct, I could only stamp the first one correct and the second one helpful.  Thanks, you all are great.